Why does only one of these connection strings work

I have 2 identical connection strings. One is for ASP.NET membership etc and the other is for everything else.

I can log in to my application, so the following connection string obviously works.

<add name="ApplicationServices" connectionString="Data Source=SBS;Initial Catalog=CustomerIntranet;Integrated Security=True;"
      providerName="System.Data.SqlClient" />

As soon as I try to access a secure page (role based access) I get the dreaded:

System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Here is the other connections string:

<add name="CustomerIntranetConnectionString" connectionString="Data Source=SBS;Initial Catalog=CustomerIntranet;Integrated Security=True;"
  providerName="System.Data.SqlClient" />

I am connected to the server via remote desktop connection.
I am running the app through a browser on that server
The SQL server (SQLSERVER 2005) is configured for remote access anyway
I can log in
ASP.NET Membership and the other database stuff is all located in the same DB
The application is deployed on IIS 6.0
The application runs fine on my machine (IIS 7.0)

If anyone can shed some light on this issue that would be awesome. I've played with the connection strings but with zero success.

Just to reiterate, I can log in so I obviously establish a connection to the DB. As soon as I hit a secure page I get the error.

Thanks.

PS Here is the stack

[SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)]
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +5009598
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() +234
   System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity) +341
   System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, SqlConnection owningObject) +129
   System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, TimeoutTimer timeout) +270
   System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, TimeoutTimer timeout, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +195
   System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +232
   System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +185
   System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +33
   System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +524
   System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
   System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +479
   System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +108
   System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +126
   System.Data.SqlClient.SqlConnection.Open() +125
   System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +123
   System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior) +166
   System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) +115
   DataAccess.IntranetTableAdapters.employeeTableAdapter.GetEmployeeByUserID(Nullable`1 id) +377
   BusinessClasses.GlobalOperations.getEmployeeByUserID(Guid id) +96
   Customer.EmployeeTimesheet.prepareComponents() +327
   Customer.EmployeeTimesheet.Page_Load(Object sender, EventArgs e) +179
   System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +14
   System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +35
   System.Web.UI.Control.OnLoad(EventArgs e) +91
   System.Web.UI.Control.LoadRecursive() +74
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2207

Here is the full config file:

<?xml version="1.0"?>

<configuration>
  <connectionStrings>
    <add name="ApplicationServices" connectionString="Data Source=SBS;Initial Catalog=CustomerIntranet;Integrated Security=True;"
      providerName="System.Data.SqlClient" />
    <add name="IntranetConnectionString" connectionString="Data Source=SBS;Initial Catalog=CustomerIntranet;Integrated Security=True;"
      providerName="System.Data.SqlClient" />
  </connectionStrings>

  <system.web>
    <compilation debug="true" targetFramework="4.0" />
    <httpRuntime maxRequestLength="8192" executionTimeout="360"/>
    <authentication mode="Forms">
      <forms loginUrl="~/Account/Login.aspx" timeout="2880" />
    </authentication>

    <membership>
      <providers>
        <clear/>
        <add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider" connectionStringName="ApplicationServices"
             enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="false" requiresUniqueEmail="false"
             maxInvalidPasswordAttempts="5" minRequiredPasswordLength="6" minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="10"
             applicationName="/" />
      </providers>
    </membership>

    <profile>
      <providers>
        <clear/>
        <add name="AspNetSqlProfileProvider" type="System.Web.Profile.SqlProfileProvider" connectionStringName="ApplicationServices" applicationName="/"/>
      </providers>
    </profile>

    <roleManager enabled="true" defaultProvider="MyRoleProvider">
      <providers>
        <clear/>
        <add name="MyRoleProvider" type="BusinessClasses.MyRoleProvider, BusinessClasses"  />
      </providers>
    </roleManager>

  </system.web>

  <system.webServer>
     <modules runAllManagedModulesForAllRequests="true"/>
  </system.webServer>
</configuration>

回答1:

This connection string is actually pass the control to the system

Data Source=SBS;Initial Catalog=CustomerIntranet;Integrated Security=True;

By saying control, I mean that the system, together with the database check if he allow or not to connect to the database. So that is the reason that works on one case and not on the other.

What I suggest, first use localhost on Data Source=localhost, except if you connect to a different computer, then make sure that the name of the computer is properly configured on host file of your server, or just use direct the IP.

Second open the database management and make sure that the catalog exist, and your pool that try to connect have permission to read that catalog. Also the file of the database must have permissions for the pool.

The pool is running under a user account, or a system account. That account must have permissions to read/write that database, both on the files of the database, and on the preference of the database. Also that user must be on the grand list on the database.

回答2:

I found out what the issue was to this problem. Let me first add some more information relevant to the issue.

I am using an n-tier architecture with a typical DataAccess, DataClasses (Typed datasets, xsd file), Business and Presentation layers.

A solution wide search for connectionString reveals multiple hard coded connection strings (Courtesy of VS) that were not being overridden by the web.config. These are obviously created during the set up of the DataSet designer.

When deploying the application, the web.config connectionStrings reflect the production server but the hard coded strings still refer to my development server.

My limited understanding is that the web.config is supposed to override any other connection strings defined elsewhere in the application if they have the same name. It seems as this is not the case. Somebody wiser than me might be able to elaborate on that.

In short, if you are having any bizare issue like I did, do a solution wide search for any other connection strings. They might exist without your knowledge and they may be causing a conflict.