Azuredatastudio: Connection Error

[pchinnala]

We have written the needed data into your clipboard because it was too large
Issue Type: Bug

Receiving this error trying to connect to sql server database after upgrading to latest version:

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>b__274_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.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.<b__0>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 549
ClientConnectionId:cdaa0780-e018-4d65-b0c2-fc247c697d81

Azure Data Studio version: azuredatastudio 1.20.0 (cfbadd1eaef2dfa1fd643009c572137c704f1009, 2020-07-14T23:40:35.393Z)
OS version: Windows_NT x64 10.0.18363

System Info

|Item|Value|
|---|---|
|CPUs|Intel(R) Core(TM) i7-3770 CPU @ 3.40GHz (8 x 3392)|
|GPU Status|2d_canvas: enabled
flash_3d: enabled
flash_stage3d: enabled
flash_stage3d_baseline: enabled
gpu_compositing: enabled
multiple_raster_threads: enabled_on
oop_rasterization: unavailable_off
protected_video_decode: unavailable_off
rasterization: unavailable_off
skia_renderer: disabled_off_ok
video_decode: enabled
viz_display_compositor: enabled_on
viz_hit_test_surface_layer: disabled_off_ok
webgl: enabled
webgl2: enabled|
|Load (avg)|undefined|
|Memory (System)|15.95GB (7.62GB free)|
|Process Argv||
|Screen Reader|no|
|VM|0%|

Extensions (6)

Extension|Author (truncated)|Version
---|---|---
query-editor-boost|dre|0.4.0
agent|Mic|0.47.0
import|Mic|0.17.0
profiler|Mic|0.11.0
azdata-sanddance|msr|3.0.0
sql-search|Red|0.4.0

to send. Please paste.

[adsbot[bot]]

Thanks for submitting this issue. Please also check if it is already covered by an existing one, like:

• Only Azure Data Studio for Mac cannot connect to SQL Server in Docker (#9116)
• "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'." when trying to connect to an azure SQL instance. (#8815)
• （How can't I support a database with a lower version）怎么不支持低版本的数据库啊 (#7852)
• Can't connect to Database using Windows Authentication on Mac (#447)
• Error When Trying To Connect To The Dedicated Admin Connection On SQL Server (#3161)
  

[psarin]

I'm having the same issue with latest update. Downgrading to v1.19 seems to fix the issue (i.e., seems like something got broken in 1.2)

[dbenz04]

I'm having the same problem with v1.2. I can connect to SQL Server 2019, but not to any SQL 2017 instances.

[adsbot[bot]]

We need more info to debug your particular issue. If you could attach your logs to the issue (ensure no private data is in them), it would help us fix the issue much faster.

To find your logs:

• Open command palette (Click View -> Command Palette)
• Run the command: Developer: Open Logs Folder

This will open the log file locally. Please include renderer.log

[Charles-Gagnon]

The following information will also be useful in trying to determine what the issue is :

1. The type (Azure/Standard On-Prem/DW/etc) and version of database connecting to (SELECT @@VERSION)
2. The type of authentication used to connect
3. The OS that ADS is running on

[pchinnala]

Here's the details you need:

1. Standard On-Prem
2. Sql Login
3. Windows 10 Enterprise

[Charles-Gagnon]

Could you get the full output from running SELECT @@VERSION on the server @pchinnala ? And is there anything in the logs (instructions posted by the bot above)?

I've tried repro'ing this myself with a similar setup and haven't had any issues so we're going to need some more information.

[pchinnala]

Sql Server version: Microsoft SQL Server 2016 (SP2-CU10) (KB4524334) - 13.0.5492.2 (X64) Oct 4 2019 19:14:08 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: )

renderer1.log

[cheenamalhotra]

Hi @pchinnala @psarin @dbenz04

Would you be able to test your connectivity with Microsoft.Data.SqlClient driver by running a basic .NET Core Console app and let us know if you're able to connect? You can use connection string as below:

Data Source=<server host/IP>;UID=<user>;PWD=<password>;Initial Catalog=<database>

[dbenz04]

@pchinnala Is your SQL Server in a failover cluster? I've determined that I can connect to SQL 2016 and 2019 if they aren't clustered. When I try to connect to any clustered instance, I get the error.

Perhaps some sort of name resolution/SPN issue?

[cheenamalhotra]

@dbenz04

You could try to connect with combinations of server host + port (use 1433 for default instance), IP + port to validate if this is due to name/SPN resolution.

[dbenz04]

No luck using IP, IP+port, server name+port. Same error.

[pchinnala]

@dbenz04 - same here, no luck with either combination in the connection string, I get the same error. Not sure if the sql server is in a fail over cluster, its a vendor database and I've no clue.

I was able to connect to the same database server with prior version 1.19.

[cheenamalhotra]

Ok, please try setting"TrustServerCertificate" connection property to "true" on your connection.
The reason you're seeing issue is probably because SQL Server might be enforcing "Encryption" but your clients don't have certificates installed.

This behavior is from the recent changes added in Microsoft.Data.SqlClient v2.0.0 now used by Azure Data Studio.

[dbenz04]

@cheenamalhotra That worked!

[pchinnala]

Now, it works for me too. Thanks @cheenalmalhotra!!!

[SimonYeatsEM]

Thanks Cheena, that worked for me also, for the SQL servers that were having the connection failure in ADS
(they have enforce TLS set.)

Ok, please try setting"TrustServerCertificate" connection property to "true" on your connection.
The reason you're seeing issue is probably because SQL Server might be enforcing "Encryption" but your clients don't have certificates installed.

This behavior is from the recent changes added in Microsoft.Data.SqlClient v2.0.0 now used by Azure Data Studio.

[minoseah629]

thanks for addressing my issue 15 days ago in insider version 1.20
https://github.com/microsoft/azuredatastudio/issues/11182

[Charles-Gagnon]

Closing this out now with the solution posted above.

For further information, this issue was caused by a client driver update which fixed an issue where TLS encryption wasn't enforced correctly. See https://github.com/dotnet/SqlClient/blob/master/release-notes/2.0/2.0.0.md#breaking-changes-1 and https://docs.microsoft.com/en-us/sql/relational-databases/native-client/features/using-encryption-without-validation for more information.