c# - SQL 连接问题

Question

我使用 Visual Studio 2012 的内置 asp.net 网站模板开发了一个网站。

它在 ASP.NET 开发服务器上运行良好。

当我在 IIS 上上传网站时。我在尝试登录时遇到了下面提到的错误。

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: 50 - Local Database Runtime error occurred. Cannot create an automatic instance. See the Windows Application event log for error details.

堆栈跟踪

[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: 50 - Local Database Runtime error occurred. Cannot create an automatic instance. See the Windows Application event log for error details.
)]
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +6675286
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +688
   System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean withFailover) +6701832
   System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover) +219
   System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout) +6703968
   System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance) +6704427
   System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions) +610
   System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) +1049
   System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions) +74
   System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnectionOptions userOptions) +6706995
   System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnectionOptions userOptions) +78
   System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) +2192
   System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) +116
   System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) +1012
   System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) +6711619
   System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry) +152
   System.Data.SqlClient.SqlConnection.Open() +229
   System.Data.SqlClient.SqlProviderServices.UsingConnection(SqlConnection sqlConnection, Action`1 act) +134
   System.Data.SqlClient.SqlProviderServices.UsingMasterConnection(SqlConnection sqlConnection, Action`1 act) +3790907
   System.Data.SqlClient.SqlProviderServices.GetDbProviderManifestToken(DbConnection connection) +10941050
   System.Data.Common.DbProviderServices.GetProviderManifestToken(DbConnection connection) +91

[ProviderIncompatibleException: The provider did not return a ProviderManifestToken string.]
   System.Data.Common.DbProviderServices.GetProviderManifestToken(DbConnection connection) +10941121
   System.Web.Providers.ModelHelper.CreateMetadataWorkspace(String providerName, DbConnection connection, String csdl, String ssdl, String msl) +168
   System.Web.Providers.ModelHelper.CreateEntityConnection(ConnectionStringSettings setting, String csdl, String ssdl, String msl) +298
   System.Web.Providers.ModelHelper.CreateMembershipEntities(ConnectionStringSettings setting) +51
   System.Web.Providers.DefaultRoleProvider.IsUserInRole(String username, String roleName) +180
   System.Web.Security.Roles.IsUserInRole(String username, String roleName) +794
   LoginPage.Login1_Authenticate(Object sender, AuthenticateEventArgs e) in c:\inetpub\wwwroot\TimeCard\LoginPage.aspx.cs:19
   System.Web.UI.WebControls.Login.AttemptLogin() +160
   System.Web.UI.WebControls.Login.OnBubbleEvent(Object source, EventArgs e) +93
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +84
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3804

我的 webconfig 文件是

<?xml version="1.0"?>
<!--
  For more information on how to configure your ASP.NET application, please visit
  http://go.microsoft.com/fwlink/?LinkId=169433
  -->
<configuration>
  <configSections>
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=5.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false"/>
  </configSections>
  <connectionStrings>
    <add name="DefaultConnection" connectionString="Data Source=(LocalDb)\v11.0;Initial Catalog=aspnet-TimeCard-20131218110938;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnet-TimeCard-20131218110938.mdf"
      providerName="System.Data.SqlClient" />
    <add name="TimeCardConnectionString" connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=TimeCard;Integrated Security=SSPI"
      providerName="System.Data.SqlClient" />
    <add name="MCASConnectionString" connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=MCAS;Integrated Security=True"
      providerName="System.Data.SqlClient" />
    <add name="TimeCardConnectionString2" connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=TimeCard;Integrated Security=True"
      providerName="System.Data.SqlClient" />
  </connectionStrings>
  <system.web>
    <authorization>
      <allow roles="Admin" />
    </authorization>
    <compilation debug="true" >
      <assemblies>
        <add assembly="System.Design, Version=4.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
        <add assembly="System.Web.Extensions.Design, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
        <add assembly="System.Windows.Forms, Version=4.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
      </assemblies>
    </compilation >

    <pages>
      <namespaces>
        <add namespace="System.Web.Optimization"/>
      </namespaces>
      <controls>
        <add assembly="Microsoft.AspNet.Web.Optimization.WebForms" namespace="Microsoft.AspNet.Web.Optimization.WebForms" tagPrefix="webopt"/>
      </controls>
    </pages>
    <authentication mode="Forms">
      <forms loginUrl="~/Account/Login" timeout="2880" defaultUrl="~/"/>
    </authentication>
    <profile defaultProvider="DefaultProfileProvider">
      <providers>
        <add name="DefaultProfileProvider" type="System.Web.Providers.DefaultProfileProvider, System.Web.Providers, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" connectionStringName="DefaultConnection" applicationName="/"/>
      </providers>
    </profile>
    <membership defaultProvider="DefaultMembershipProvider">
      <providers>
        <add name="DefaultMembershipProvider" type="System.Web.Providers.DefaultMembershipProvider, System.Web.Providers, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" connectionStringName="DefaultConnection" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="false" requiresUniqueEmail="false" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="6" minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="10" applicationName="/"/>
      </providers>
    </membership>
    <roleManager enabled="true" defaultProvider="DefaultRoleProvider">
      <providers>
        <add connectionStringName="DefaultConnection" applicationName="/"
          name="DefaultRoleProvider" type="System.Web.Providers.DefaultRoleProvider, System.Web.Providers, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
      </providers>
    </roleManager>
    <!--
            If you are deploying to a cloud environment that has multiple web server instances,
            you should change session state mode from "InProc" to "Custom". In addition,
            change the connection string named "DefaultConnection" to connect to an instance
            of SQL Server (including SQL Azure and SQL  Compact) instead of to SQL Server Express.
      -->
    <sessionState mode="InProc" customProvider="DefaultSessionProvider">
      <providers>
        <add name="DefaultSessionProvider" type="System.Web.Providers.DefaultSessionStateProvider, System.Web.Providers, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" connectionStringName="DefaultConnection"/>
      </providers>
    </sessionState>
  </system.web>
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="DotNetOpenAuth.Core" publicKeyToken="2780ccd10d57b246"/>
        <bindingRedirect oldVersion="1.0.0.0-4.0.0.0" newVersion="4.1.0.0"/>
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="DotNetOpenAuth.AspNet" publicKeyToken="2780ccd10d57b246"/>
        <bindingRedirect oldVersion="1.0.0.0-4.0.0.0" newVersion="4.1.0.0"/>
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework"/>
  </entityFramework>
</configuration>

应该是什么问题？

Answer 1

A network-related or instance-specific error occurred whileestablishing a connection to SQL Server. The server was not found orwas not accessible. Verify that the instance name is correct and thatSQL Server is configured to allow remote connections. (provider: SQLNetwork Interfaces, error: 50 - Local Database Runtime error occurred.Cannot create an automatic instance. See the Windows Application eventlog for error details.

在完整的 IIS 下运行时，LocalDB 有两个属性会导致问题:

1.LocalDB needs user profile to be loaded

2.LocalDB instance is owned by a single user and private (by default)

很可能没有加载用户配置文件。如果 LocalDB 在 IIS 下执行，请确保为当前用户启用配置文件加载。

在 Google 上搜索并找到了这篇很棒的帖子:

Using LocalDB with Full IIS, Part 1: User Profile

当我将 ASP.NET MVC 4 应用程序部署到本地 IIS 中的新网站并创建新的应用程序池时，我也遇到了这个问题。正如上面的帖子所述，必须为文件中的 AppPool 启用 setProfileEnvironment=true

 C:\Windows\System32\inetsrv\config\applicationHost.config.

所以加上这个

<add name="MyAppPoolName" />
<processModel identityType="ApplicationPoolIdentity"
              loadUserProfile="true"
              setProfileEnvironment="true" />
</add>

更改配置代码后，不要忘记重新启动应用程序池。

现在您应该阅读第二篇文章来处理 LocalDb 实例所有权:

Using LocalDB with Full IIS, Part 2: Instance Ownership