Azuredatastudio: Unable to connect to remote server on Linux

[h0m3]

• SQL Operations Studio Version: sqlops 0.24.1 (af2414efcb485ab5e435d1db693d3f7569c48453, 2017-12-19T02:25:40.652Z)
  

  - OS Version: Linux x64 4.14.10-1-ARCH

Remote connection is not possible using IP address or netbios name.

Connection type: Microsoft SQL Server
Auth Type: SQL Login
OS: Arch Linux x64
Error: Timeout
No firewall or blocking app.

A Windows Server 2016 NAT VM running on the same machine can connect to the server.

System.Data.SqlClient.SqlException (0x80131904): Connection Timeout Expired.  The timeout period elapsed during the post-login phase.  The connection could have timed out while waiting for server to complete the login process and respond; Or it could have timed out while attempting to create multiple active connections.  The duration spent while attempting to connect to this server was - [Pre-Login] initialization=52; handshake=158; [Login] initialization=3; authentication=7; [Post-Login] complete=14020;  ---> System.ComponentModel.Win32Exception (0x80004005): Unknown error 258
   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.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.<>c__DisplayClass22_0.<TryGetConnection>b__0(Task`1 _)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot)
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.SqlTools.ServiceLayer.Connection.ReliableConnection.ReliableSqlConnection.<>c__DisplayClass28_0.<<OpenAsync>b__0>d.MoveNext() in D:\a\1\s\src\Microsoft.SqlTools.ServiceLayer\Connection\ReliableConnection\ReliableSqlConnection.cs:line 298
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.SqlTools.ServiceLayer.Connection.ConnectionService.<TryOpenConnection>d__46.MoveNext() in D:\a\1\s\src\Microsoft.SqlTools.ServiceLayer\Connection\ConnectionService.cs:line 542
ClientConnectionId:23da237c-014a-4309-a6ec-7805ef0edc7f
Error Number:-2,State:0,Class:11

[genio]

423

Ubuntu

[MattIrv]

What version of SQL Server are you running? You can find out by executing a SELECT @@VERSION query if needed.

This is probably related to https://github.com/dotnet/corefx/issues/9719

[genio]

```locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1> select @@VERSION;
2> GO

Microsoft SQL Server 2008 (SP3) - 10.0.5538.0 (X64)
Apr 3 2015 14:50:02
Copyright (c) 1988-2008 Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (VM)

(1 row affected)
1>
```

[genio]

There is no fix to that error apparently. Updating SQL Server shouldn't be the fix as other clients are clearly able to connect just fine.

[MattIrv]

Thanks for the quick response. There's a discussion of this on the VS Code MSSQL extension GitHub page (https://github.com/Microsoft/vscode-mssql/issues/666) which suggests updating to SQL Server 2008 SP4 also works, which could be a more acceptable workaround for some than updating to SQL Server 2008 R2 SP3.

[h0m3]

@MattIrv Thanks for the quick response and sorry for the time to answer.

Unfortunally i dont have access to the SQL Server right now but when connecting from a Windows VM using sqlops and SSMS i was able to sucessfully connect. Also, other Windows users on the same network can use both sqlops and SSMS on Windows.

I dont think that its a server side issue.

Unfortunally i cant update the server, its a production one and im not the only sysadmin. I dont have the authority.

I will update you with the SQL Server information about my server.

[h0m3]

@MattIrv, follows my SQL Server version.

Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64)
Jul 9 2008 14:17:44
Copyright (c) 1988-2008 Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

[christian-roggia]

This bug is persisting in SQL Operations Studio 0.30.4. We have the same problem stated above by @h0m3: we have a production legacy database running Microsoft SQL Server 2005 and we are not able to update the database due to the ownership and administration of the server. We will get rid of this legacy database soon enough but I still need to access data for migration purposes.

Other SQL clients are working fine but I would like to use SQL Operations Studio for this database as well.

[kevcunnane]

@christian-roggia - this is a .Net Core SqlClient issue, so unfortunately it's not something we can work around in SQL Operations Studio. Additionally, SQL Server 2005 is out of support which makes it less likely any driver fix would be done to help here.

I would recommend using SSMS or other existing client tools to access your SQL Server 2005 server. Once this has been migrated over to a newer version you'll be able to use SQL Operations Studio to connect.

[Zhujinyong]

I have updated to sp3,but I still can not open database.Is x86 not supported??? Waiting for answer online ,thanks a lot!
my database version is :
Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (Intel X86)
Aug 19 2014 12:21:07
Copyright (c) Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build : )
Error message is:
System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
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.TryOpen(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.Open()