Can't Connect to a SQL Server

[greenjaed]

• MSSQL Extension Version: 1.10.0
• VSCode Version: 1.52.0
• OS Version: Windows 10.0.18363

Steps to Reproduce:

1. Open SQL Server panel
2. Click the arrow next to a connection

I get the following error:

mssql: Failed to connect: Microsoft.Data.SqlClient.SqlException (0x80131904): A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: HTTP Provider, error: 0 - ) at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at Microsoft.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean& marsCapable, Boolean& fedAuthRequired) at Microsoft.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean withFailover, SqlAuthenticationMethod authType) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance) at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken, DbConnectionPool pool) at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) at Microsoft.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions) at Microsoft.Data.ProviderBase.DbConnectionFactory.<>c__DisplayClass45_0.<TryGetConnection>b__1(Task1 _) at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke() at System.Threading.Tasks.Task.<>c.<.cctor>b274_0(Object obj) at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state) --- End of stack trace from previous location where exception was thrown --- at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread) --- End of stack trace from previous location where exception was thrown --- at Microsoft.SqlTools.ServiceLayer.Connection.ReliableConnection.ReliableSqlConnection.<>c__DisplayClass30_0.<b0>d.MoveNext() in D:\a\1\s\src\Microsoft.SqlTools.ManagedBatchParser\ReliableConnection\ReliableSqlConnection.cs:line 314 --- End of stack trace from previous location where exception was thrown --- at Microsoft.SqlTools.ServiceLayer.Connection.ConnectionService.TryOpenConnection(ConnectionInfo connectionInfo, ConnectParams connectionParams) in D:\a\1\s\src\Microsoft.SqlTools.ServiceLayer\Connection\ConnectionService.cs:line 550 ClientConnectionId:6ef9a8f4-aeed-4b4d-826f-a54dcf78331e

This error does not occur when using the previous version, 1.9.0 I can successfully connect to the same servers using other means, like Microsoft SQL Server Management Studio and System.Data.SqlClient

The Server is a MSSQL14 server on Windows Server 2019 and uses Windows Authentication / Integrated Authentication.

[dataacumen]

I am seeing the same issue but with SQL Server Authentication even after a clean install of both VS Code and the Extension. If I roll back to 1.9.0 the issue goes away.

[cssuh]

Please try setting "trustServerCertificate": true in options

[kburtram]

@greenjaed to set the trustServerCertificate connection option, please open the "User Settings (JSON)" from the command palette. The connection metadata is in the JSON and each connection should have an options array. For example,

    "options": {
                ...
                "trustServerCertificate": true,
                ...
        },

The was a recent SqlClient driver update that now more strictly enforces validation of self-signed certificates for encryped TDS connections. Alternatively, you can add the CA used to sign the SQL Server cert to you keystore.

[greenjaed]

That worked for me. I didn't see "User Settings (JSON)" when I entered it into the command palette, however I did see "Open Settings (JSON)" which opened the settings file. I saw my connections but didn't see an options object. It was laid out like this:

"mssql.connections": [
        {
            "server": "",
            "database": "",
            "authenticationType": "Integrated",
            "profileName": "",
            "password": "",
            "trustServerCertificate": true
        }
]

I added the trustServerCertificate option to the connection settings, as you see hear, restarted VS Code, and I got it working again.

[dataacumen]

Just confirming this worked for me as well. Thanks.

[golaat]

This works, but its not a real good solution. Why is the extension not using the system certificate store? Wouldn't this leave you open to a man in the middle attack?

[kburtram]

@golaat the driver should be using the system certificate store. The underlying issue we are typically seeing is the the SQL Server is using a self-signed certificate and the private CA isn't registered in the certificate store. In this case the issue can be resolved by either registering the CA on the system or setting "Trust Server" option. This issue was is related to a bug fix in the SQL Client driver to more correctly validate certificates.

Please let us know if you're seeing something else so we can log a bug with the client driver team.

[golaat]

@kburtram I can see that being the case with certificates for on-prem sql servers, but I also get that message for Azure SQL databases. Are Azure SQL DBs also using a self signed certificate? The above fix worked for me too, but the discoverability of the connection defaults is not there. It would be nice if you could set connection defaults via the settings pane for the extension. If I hadn't found this issue, I would have never known I can specify connection defaults in mssql.connections via an unnamed entry in the connections array.

[kburtram]

@golaat fair point regarding setting connection options. We're somewhat limited what we can do with the UX in the Command Palette in vscode. But we'll discuss how we could improve this experience.

It's interesting you are seeing this connecting to Azure. This isn't expected as Azure is not using self-signed certificates and the CA should be registered on most platforms. Which platform are you using?

[ObiRonzo]

Have this issue, and have been unable to successfully implement supposed fix. I have "rolled back" to release 1.9. I would strongly suggest that a new release/patch be issued that includes, as part of release notes, detailed instructions/example of how to address this issue.

[golaat]

@kburtram we use a Palo Alto Firewall and do SSL inspection. The certificate used for SSL inspection is trusted and in the Windows Certificate store. Does the VS Code Extension have a private cert store it uses? If I set "http.proxyStrictSSL": false in the configuration settings the problem also goes away, although I'd rather not set that approach globally.

[yezz123]

I got this error when i try to connect to SQL Server on linux

Version: 1.52.1
Electron: 9.3.5
Chrome: 83.0.4103.122
Node.js: 12.14.1
V8: 8.3.110.13-electron.0
OS: Linux x64 5.8.0-41-generic

mssql: Failed to connect: Cannot authenticate using Kerberos. Ensure Kerberos has been initialized on the client with 'kinit' and a Service Principal Name has been registered for the SQL Server to allow Kerberos authentication.
ErrorCode=InternalError, Exception=Interop+NetSecurityNative+GssApiException: GSSAPI operation failed with error - Unspecified GSS failure.  Minor code may provide more information (SPNEGO cannot find mechanisms to negotiate).
   at System.Net.Security.NegotiateStreamPal.GssInitSecurityContext(SafeGssContextHandle& context, SafeGssCredHandle credential, Boolean isNtlm, SafeGssNameHandle targetName, GssFlags inFlags, Byte[] buffer, Byte[]& outputBuffer, UInt32& outFlags, Int32& isNtlmUsed)
   at System.Net.Security.NegotiateStreamPal.EstablishSecurityContext(SafeFreeNegoCredentials credential, SafeDeleteContext& context, String targetName, ContextFlagsPal inFlags, SecurityBuffer inputBuffer, SecurityBuffer outputBuffer, ContextFlagsPal& outFlags)
   at Microsoft.Data.SqlClient.SNI.SNIProxy.GenSspiClientContext(SspiClientContextStatus sspiClientContextStatus, Byte[] receivedBuff, Byte[]& sendBuff, Byte[] serverName)
   at Microsoft.Data.SqlClient.SNI.TdsParserStateObjectManaged.GenerateSspiClientContext(Byte[] receivedBuff, UInt32 receivedLength, Byte[]& sendBuff, UInt32& sendLength, Byte[] _sniSpnBuffer)
   at Microsoft.Data.SqlClient.TdsParser.SNISSPIData(Byte[] receivedBuff, UInt32 receivedLength, Byte[]& sendBuff, UInt32& sendLength)

[kburtram]

@yezz123 this looks like a different connectivity issue than the one being highlighted with this issue (due to the specific error message).

There is a guide on how to setup Active Director auth on Linux in https://github.com/Microsoft/vscode-mssql/wiki/How-to-enable-Integrated-Authentication-on-macOS-and-Linux-using-Kerberos. Also, if you search issues for "kinit" you can find several related issues with additional troubleshooting steps for this authentication configuration.

Please let us know if those don't help resolve the issue you're hitting. Thanks!

[garyng2000]

I have the same issue and the setting(under mssql.connection) solved it. However, the UI technically makes this not solvable for first time user. The 'wizard'(add connection) fail to connect to the server thus do not create the connection object so nothing to search for and change. so I have to manually copy/paste the above setting. given that this is a known issue, can the wizard be changed to include this option or have the setting by default ?

[kburtram]

@garyng2000 agreed that this is not a discoverable mitigation. I've opened this issue to track improving this experience in the UX https://github.com/microsoft/vscode-mssql/issues/16959.

[Nefcanto]

None of the solutions worked for me. It's a shame that such an old technology can not be connected easily. SQL Server, VSCode, and this extension are all to blame. Developers spend their time connecting to the DB engine, instead of adding value to their companies and the world.

[TalMat]

"trustServerCertificate": true fixed this for me with v1.16.0

Hesitant to move to 1.17.0 though.