09/03/2006  |   

you may get the following error when you have developed an application, and then you move your site to another server (like your web host's server):

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connection. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified)

Below you will find 2 causes and their resolutions.

Cause & Fix #1:

Cause: Server not configured for remote connections.

The error is telling us that your server has not been configured to allow remote connections. If your SQL Server has not been configured to allow remote connections, then configure your SQL Server. Fix: SQL Server 2000: Open the SQL Server Client Network Utility. On the General tab enable the protocols you need to use. Typically this will be 'TCP/IP' and possibly 'Named Pipes'. SQL Server 2005 Open SQL Server Configuration Manager. Select "SQL Server 2005 Network Configuration | Protocols for MSSQLSERVER" then enable the protocols you need.

Cause & Fix #2:

Cause: ASP.Net 2.0 Providers are trying to pull from the server's (nonexistent) Providers database.

By default the machine.config file is trying to pull the Provider information from a SQLExpress database using an invalid connection string named "LocalSQLServer". Many web servers will not have SQLExpress enabled, and will not have this value set to a valid SQL Server database that is of use to you. In a shared hosting environment, this is especially true, as it would be expected that you would want your Provider information stored in your database and not some single database shared by the other users of that web server. Fix: Since you probably cannot access the machine.config file, you need to override the Provider settings in your web.config file, and set the connection string name to your connection string name. The following code comes from the machine.config file and has been modified to first remove each provider before adding the provider. Add the following code to your web.config file just under the "<system.web>" tag. Make sure to replace the 3 occurrences of connectionStringName="LocalSQLServer" with your connection string name.

This goes in the system.web section of web.config.

<membership>

<providers>

<remove name="AspNetSqlMembershipProvider" />

<add name="AspNetSqlMembershipProvider"

type="System.Web.Security.SqlMembershipProvider,

System.Web, Version=2.0.0.0, Culture=neutral,

PublicKeyToken=b03f5f7f11d50a3a"

connectionStringName="LocalSQLServer"

enablePasswordRetrieval="false"

enablePasswordReset="true"

requiresQuestionAndAnswer="true"

applicationName="/"

requiresUniqueEmail="false"

passwordFormat="Hashed"

maxInvalidPasswordAttempts="5"

minRequiredPasswordLength="7"

minRequiredNonalphanumericCharacters="1"

passwordAttemptWindow="10"

passwordStrengthRegularExpression="" />

</providers>

</membership>

<profile>

<providers>

<remove name="AspNetSqlProfileProvider" />

<add name="AspNetSqlProfileProvider"

connectionStringName="LocalSQLServer"

applicationName="/"

type="System.Web.Profile.SqlProfileProvider,

System.Web, Version=2.0.0.0, Culture=neutral,

PublicKeyToken=b03f5f7f11d50a3a" />

</providers>

</profile>

<roleManager>

<providers>

<remove name="AspNetSqlRoleProvider" />

<add name="AspNetSqlRoleProvider"

connectionStringName="LocalSQLServer"

applicationName="/"

type="System.Web.Security.SqlRoleProvider,

System.Web, Version=2.0.0.0, Culture=neutral,

PublicKeyToken=b03f5f7f11d50a3a" />

</providers>

</roleManager>

Your database must be configured for the ASP.Net 2.0 Providers. This article assumes that you have already configured your database to use ASP.Net 2.0 Providers. If you haven't, there is an article at http://www.aquesthosting.com/HowTo/Sql2005/Providers.aspx.