c# - 创建后无法连接到 Microsoft SQL Server 数据库（“系统找不到指定的文件”）

Question

我有一个脚本，它为需要数据库连接的测试运行准备环境（创建一些表并写入一些值等）

我有一个通过创建 DB_NAME 的 PowerShell 脚本

& sqllocaldb create DB_NAME
& sqllocaldb start DB_NAME
& sqllocaldb info DB_NAME

输出是：

LocalDB instance "DB_NAME" created with version 13.1.4001.0.
LocalDB instance "DB_NAME" started.

Name:               DB_NAME
Version:            13.1.4001.0

Shared name:        

Owner:              fv-az604\VssAdministrator
Auto-create:        No
State:              Running

Last start time:    11/7/2019 11:47:43 PM

Instance pipe name: np:\\.\pipe\LOCALDB#24C8D765\tsql\query

通过以下代码执行连接

using (SqlCommand cmd = new SqlCommand(sql))
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        cmd.Connection = connection;
        cmd.Connection.Open();
        cmd.ExecuteNonQuery();
    }
}

连接字符串如下所示：

Application Name=MyApp;Connect Timeout=60;Data Source=localhost;Initial Catalog=master;Integrated Security=sspi;Connection Reset=false;Min Pool Size=1;Max Pool Size=200;Pooling=true;MultipleActiveResultSets=true;Enlist=false

数据库名称：DB_NAME

完整的堆栈跟踪是：

[Error]: 
Type: System.ComponentModel.Win32Exception
Message: The system cannot find the file specified
Source: 
Stack:

Type: System.Data.SqlClient.SqlException
Message: 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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
Source: .Net SqlClient Data ProviderHelpLink.ProdName: Microsoft SQL Server
HelpLink.EvtSrc: MSSQLServer
HelpLink.EvtID: 2
HelpLink.BaseHelpUrl: http://go.microsoft.com/fwlink
HelpLink.LinkId: 20476

Stack:
   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, SqlAuthenticationProviderManager sqlAuthProviderManager)
   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, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   at MyClass.MyFuncABC(String connectionString, String databaseName) in D:\a\1\s\App\MyClass.cs:line 333
   at MyClass.MyFuncAB() in D:\a\1\s\App\MyClass.cs:line 444
   at MyClass.MyFuncA() in D:\a\1\s\App\MyClass.cs:line 555
   at App.Main(String[] args) in D:\a\1\s\App\Main.cs:line 1337

标签： c#sql-serverpowershell

Answer 1

关于丢失文件的第一个错误消息只是水中的泥浆。真正的一个是第二个，几乎每个 SQL Server DBA 都知道：

Message: 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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

As the message says, connection cannot be made into SQL Server. The client library tries to connect via TCP/IP, Shared Memory and Named Pipes. Since pipes is the last option, and it fails too, the error contains message from pipes.

LocalDB has it's own, quite peculiar syntax, for connection strings. The one you've used is to connect into a real SQL Server.

Maybe something like

Server=np:\\.\pipe\LOCALDB#24C8D765\tsql\query

would work better. Also, find out your localdb's instance name and try something like

Server=(localdb)\MyInstance;Integrated Security=true;