在Ubuntu/Visual Studio Code上从.Net Core连接到SqlServer

Question

我有一个控制台应用程序，连接到托管在同一网络中的独立Windows机器上的SQL Server数据库，使用.Net Core（使用Visual Studio Code）编写，在Windows上工作得非常好。

完全相同的代码库在Ubuntu上的Visual Studio Code上运行时无法连接到SQL Server实例，称找不到服务器。

我可以ping SQL Server IP并从中获取响应。

此外，我可以通过1433端口的telnet连接到SQLServer实例。

另外，我已从Nuget中提取了System.Data.Common和System.Data.SqlClient并按建议设置了连接字符串中的MultipleActiveResultSets=False。

仅为完整起见，这是尝试连接的代码：

var connection = new SqlConnection(myConnectionString);
connection.Open();

以下是堆栈跟踪信息：

Unhandled Exception: 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: TCP Provider, error: 35 - An internal exception was caught) ---> System.AggregateException: One or more errors occurred. (No such device or address) ---> System.Net.Internals.SocketExceptionFactory+ExtendedSocketException: No such device or address
   at System.Net.Dns.HostResolutionEndHelper(IAsyncResult asyncResult)
   at System.Net.Dns.EndGetHostAddresses(IAsyncResult asyncResult)
   at System.Net.Dns.<>c.<GetHostAddressesAsync>b__14_1(IAsyncResult asyncResult)
   at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization)
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Data.SqlClient.SNI.SNITCPHandle.<ConnectAsync>d__22.MoveNext()
   --- End of inner exception stack trace ---
   at System.Threading.Tasks.Task.ThrowIfExceptional(Boolean includeTaskCanceledExceptions)
   at System.Threading.Tasks.Task.Wait(Int32 millisecondsTimeout, CancellationToken cancellationToken)
   at System.Threading.Tasks.Task.Wait(TimeSpan timeout)
   at System.Data.SqlClient.SNI.SNITCPHandle..ctor(String serverName, Int32 port, Int64 timerExpire, Object callbackObject, Boolean parallel)
   --- End of inner exception stack trace ---
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, 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, 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.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()

有什么想法吗？

谢谢。

- Alessandro Di Lello

---

你的连接字符串是什么样子的？你使用的是SQL身份验证吗？如果你的连接字符串使用的是Windows身份验证而不是SQL身份验证，那么你可能会遇到问题。 - Robert Paulsen

我正在使用SQL身份验证。"SERVERIP\MSSQLSERVER,1433;Database=DBName;User=UIsername;Password=password;MultipleActiveResultSets=False" - Alessandro Di Lello

Answer 1

我已经找到了问题并在coreclr存储库中提出了问题。问题在于我连接的SQLServer实例版本。它是2008r2 SP1，而.net core sql客户端无法连接。升级到SP3即可解决问题。

更多详情请参见：此处。

- Alessandro Di Lello

Answer 2

显然，由于SQL服务器存在于您的Windows机器上，而在Ubuntu机器上不存在。并且它无法安装在Ubuntu机器上。即使是为了测试目的，您也必须将其托管在数据库服务器上，并将连接字符串指向该服务器。

编辑
请确保您的数据库服务器防火墙未阻止访问。即使您在同一台计算机上有双重启动，它们对计算机来说仍然是两个不同的机器。

- It's a trap

---

抱歉，我应该明确说明。我已经连接到远程托管的DB服务器（仍在局域网中且在专用win机器上）。正如我在问题中提到的，我可以ping SQL Server机器，并且确实从它那里得到响应。 - Alessandro Di Lello

当我在Windows上运行我的应用程序时，我可以从我的计算机连接，那么为什么在Ubuntu上连接时会有变化呢？（我在我的计算机上进行了双重引导） - Alessandro Di Lello

尝试使用 Telnet / SSH 连接到端口 1433。这是 SQL Server 使用的端口，请查看是否可以成功连接。 - Krishnandu Sarkar

如果是通过telnet连接的话，那么问题可能出在代码方面。您能否请更新您的问题并附上代码？ - Krishnandu Sarkar

它是同一台机器。我已经在Win和Ubuntu上进行了双重启动。无论如何，正如我们刚才通过telnet检查的那样，我可以连接，所以这肯定与代码相关。 - Alessandro Di Lello