无法使用连接字符串连接到本地SQL Server DB，但是VS可以 [英] Can't connect to local SQL Server DB with connection string, but VS can

问题描述

我正在尝试使用连接字符串通过Visual Studio 15连接到本地Sql Server 2012数据库实例.虽然我已经能够通过Visual Studio SQL Server对象资源管理器连接到数据库并成功运行测试过程，但是在我的项目中却无法使用连接字符串来完成此操作.

I'm trying to connect to a local Sql Server 2012 database instance trough Visual Studio 15 using connection string. While I've been able to connect to the DB through the Visual Studio SQL Server Object Explorer and run a test procedure successfully, I'm unable to do so in my project using connection string.

这是SQL Server对象资源管理器提供的连接字符串

Here's my connection string that was given by the SQL Server Object Explorer

<connectionStrings>    
  <add name="objConnLocal" 
connectionString="Data Source=DESKTOPIVO;Integrated Security=True;Initial Catalog=tnk;Connect Timeout=15;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False;"providerName="System.Data.SqlClient"/>
</connectionStrings>

这是我的代码，使用给定的连接字符串连接到Azure数据库时，该代码可以很好地工作(该代码有效)

Here's my code that works perfectly fine connecting to an Azure DB with Azure given connection string (the code works)

public class DbConn
{

public static string DatabaseConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["objConnLocal"].ConnectionString;
    public static SqlConnection objConn = new sqlConnection(DatabaseConnectionString);



public void spCMSSelectTest(out DataSet ds)
{
    ds = new DataSet("buttons");
    SqlDataAdapter objDataAdapter = new SqlDataAdapter();
    SqlCommand cmd = new SqlCommand("spCMSSelectTest", DbConn.objConn);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Connection.Open();
    objDataAdapter.SelectCommand = cmd;
    objDataAdapter.Fill(ds);
    cmd.Connection.Close();
}
}

这是我要连接的数据库:

And this is the DB I'm trying to connect to:

这是我要访问的测试页:

Here's the test Page I'm trying to access:

 protected void Page_Load(object sender, EventArgs e)
{
    DataSet ds;
    try
    { 
    dbc.spCMSSelectTest(out ds);

        lblTest.Text = ds.Tables[0].Rows.Count.ToString();
    }
    catch (Exception easd)
    {
        lblTest.Text = easd.ToString();
    }

}

我一直在这里和Google上寻找答案，但似乎对我没有任何帮助.我想念什么?

I've been going trough answers here and on google but nothing seemed to work for me. What Am I missing?

有错误:

System.Data.SqlClient.SqlException(0x80131904):用户登录失败 'IIS APPPOOL \ DefaultAppPool'.在 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity 身份，SqlConnectionString connectionOptions，SqlCredential 凭证，对象providerInfo，字符串newPassword，SecureString newSecurePassword，布尔型redirectedUserInstance，SqlConnectionString userConnectionOptions，SessionData reconnectSessionData， DbConnectionPool池，字符串accessToken，布尔值 applyTransientFaultHandling) System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions 选项，DbConnectionPoolKey poolKey，对象poolGroupProviderInfo， DbConnectionPool池，DbConnection owningConnection， DbConnectionOptions userOptions)，网址为 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool 池，DbConnection owningObject，DbConnectionOptions选项， DbConnectionPoolKey poolKey，DbConnectionOptions userOptions)位于 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject，DbConnectionOptions，userOptions，DbConnectionInternal oldConnection)在 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject，DbConnectionOptions，userOptions，DbConnectionInternal oldConnection)在 System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject，UInt32 waitForMultipleObjectsTimeout，布尔值 allowCreate，仅布尔值OneCheckConnection，DbConnectionOptions userOptions，DbConnectionInternal&连接) System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject，TaskCompletionSource 1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource 1重试，DbConnectionOptions userOptions，DbConnectionInternal oldConnection，DbConnectionInternal& 连接) System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection externalConnection，DbConnectionFactory connectionFactory， TaskCompletionSource 1 retry, DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource 1 重试) System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 重试)在System.Data.SqlClient.SqlConnection.Open()在 DbConn.spCMSSelectTest(DataSet&ds)在 c:\ inetpub \ wwwroot \ CMS \ App_Code \ DbConn.cs:第22行位于 index.Page_Load(Object sender，EventArgs e)在 c:\ inetpub \ wwwroot \ CMS \ index2.aspx.cs:第18行 ClientConnectionId:d576fae4-db3d-451d-8436-2abe003c01f0错误 编号:18456，状态:1，类别:14

System.Data.SqlClient.SqlException (0x80131904): Login failed for user 'IIS APPPOOL\DefaultAppPool'. at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 retry) at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry) at System.Data.SqlClient.SqlConnection.Open() at DbConn.spCMSSelectTest(DataSet& ds) in c:\inetpub\wwwroot\CMS\App_Code\DbConn.cs:line 22 at index.Page_Load(Object sender, EventArgs e) in c:\inetpub\wwwroot\CMS\index2.aspx.cs:line 18 ClientConnectionId:d576fae4-db3d-451d-8436-2abe003c01f0 Error Number:18456,State:1,Class:14

推荐答案

您以受信任的用户身份登录(默认)-该用户是池用户IIS APPPOOL \ DefaultAppPool，您需要在登录时登录，或者SQL登录.由于它在池中运行时使用的凭据与您在桌面上使用的凭据不同.

You're logging in as a trusted user (default) - and that user is the pool user IIS APPPOOL\DefaultAppPool you need to either login as you, or an SQL login. As its not using the same credentials when running under a pool as it is from your desktop.

这篇关于无法使用连接字符串连接到本地SQL Server DB，但是VS可以的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！