SQL Tools Service - Database Connection Error

[KDunn5517]

Issue Type: Bug

When I connect to my Azure SQL databases using Data Studio, I get the following error the first time I connect. Happens on three separate databases across two VMs. First instance of this error was immediately after last Data Studio update, and has been constant since then. The output window shows this happening to the SQL Tools Service.

[Error - 7:10:43 AM] Request connection/listdatabases failed. Message: Microsoft.Data.SqlClient.SqlException (0x80131904): Login failed for user ''. 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.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at Microsoft.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) 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.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry, SqlConnectionOverrides overrides) at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides) at Microsoft.Data.SqlClient.SqlConnection.Open() at Microsoft.SqlTools.ServiceLayer.Connection.ReliableConnection.ReliableSqlConnection.<Open>b__29_0() in /_/src/Microsoft.SqlTools.ManagedBatchParser/ReliableConnection/ReliableSqlConnection.cs:line 286 at Microsoft.SqlTools.ServiceLayer.Connection.ReliableConnection.RetryPolicy.<>c__DisplayClass25_0.<ExecuteAction>b__0(RetryState _) in /_/src/Microsoft.SqlTools.ManagedBatchParser/ReliableConnection/RetryPolicy.cs:line 128 at Microsoft.SqlTools.ServiceLayer.Connection.ReliableConnection.RetryPolicy.<>c__DisplayClass26_0.<ExecuteAction>b__0(RetryState retryState) in /_/src/Microsoft.SqlTools.ManagedBatchParser/ReliableConnection/RetryPolicy.cs:line 141 at Microsoft.SqlTools.ServiceLayer.Connection.ReliableConnection.RetryPolicy.ExecuteAction[R](Func2 func, Nullable1 token) in /_/src/Microsoft.SqlTools.ManagedBatchParser/ReliableConnection/RetryPolicy.cs:line 179 at Microsoft.SqlTools.ServiceLayer.Connection.ReliableConnection.RetryPolicy.ExecuteAction(Action1 action, Nullable1 token) in /_/src/Microsoft.SqlTools.ManagedBatchParser/ReliableConnection/RetryPolicy.cs:line 138 at Microsoft.SqlTools.ServiceLayer.Connection.ReliableConnection.RetryPolicy.ExecuteAction(Action action, Nullable1 token) in //src/Microsoft.SqlTools.ManagedBatchParser/ReliableConnection/RetryPolicy.cs:line 127 at Microsoft.SqlTools.ServiceLayer.Connection.ReliableConnection.ReliableSqlConnection.Open() in //src/Microsoft.SqlTools.ManagedBatchParser/ReliableConnection/ReliableSqlConnection.cs:line 282 at Microsoft.SqlTools.ServiceLayer.Connection.ListDatabaseRequestHandler`1.HandleRequest(ISqlConnectionFactory connectionFactory, ConnectionInfo connectionInfo) in D:\a_work\1\s\src\Microsoft.SqlTools.ServiceLayer\Connection\ListDatabaseRequestHandler.cs:line 71 at Microsoft.SqlTools.ServiceLayer.Connection.ConnectionService.ListDatabases(ListDatabasesParams listDatabasesParams) in D:\a_work\1\s\src\Microsoft.SqlTools.ServiceLayer\Connection\ConnectionService.cs:line 1058 at Microsoft.SqlTools.ServiceLayer.Connection.ConnectionService.<>c__DisplayClass78_0.<b__0>d.MoveNext() in D:\a_work\1\s\src\Microsoft.SqlTools.ServiceLayer\Connection\ConnectionService.cs:line 1250 ClientConnectionId:52e96711-6d80-4ae5-bf87-34d6180767e1 Error Number:18456,State:1,Class:14 Code: 0

Azure Data Studio version: azuredatastudio 1.41.1 (33dce95b152227c4f10f6251ab30690fe9895c0c, 2023-01-28T00:46:39.274Z) OS version: Windows_NT x64 10.0.22000 Restricted Mode: No Preview Features: Enabled

System Info

|Item|Value| |---|---| |CPUs|11th Gen Intel(R) Core(TM) i7-11800H @ 2.30GHz (16 x 2304)| |GPU Status|2d_canvas: enabled
canvas_oop_rasterization: disabled_off
direct_rendering_display_compositor: disabled_off_ok
gpu_compositing: enabled
multiple_raster_threads: enabled_on
opengl: enabled_on
rasterization: enabled
raw_draw: disabled_off_ok
skia_renderer: enabled_on
video_decode: enabled
video_encode: enabled
vulkan: disabled_off
webgl: enabled
webgl2: enabled
webgpu: disabled_off| |Load (avg)|undefined| |Memory (System)|63.73GB (48.76GB free)| |Process Argv|| |Screen Reader|no| |VM|0%|

Extensions (14)

Extension|Author (truncated)|Version ---|---|--- admin-pack|Mic|0.0.2 admin-tool-ext-win|Mic|0.1.2 agent|Mic|0.49.0 dacpac|Mic|1.12.0 import|Mic|1.5.4 machine-learning|Mic|0.12.0 powershell|ms-|2022.7.2 profiler|Mic|0.12.2 query-history|Mic|0.5.3 schema-compare|Mic|1.18.0 server-report|Mic|0.2.3 sql-assessment|Mic|0.6.4 sql-database-projects|Mic|0.21.0 whoisactive|Mic|0.1.4

[kburtram]

@cheenamalhotra any ideas what could be happening here? It looks like the connection is SqlLogin so probably not related to MSAL updates I guess.

[cheenamalhotra]

I think this might be also related to Query Editor, as when opening a new query, a connection/listdatabases request is sent..

@KDunn5517 Can you confirm this happens when you start a 'New Query' on a non-master database?

[KDunn5517]

I have two users, and admin user and a developer user. This issue is only occurring with my developer user.

Yes, this is happening every time I open a new query window on a non-master database, as my developer user does not have access to the master db.

[cheenamalhotra]

Thanks for confirming.

First instance of this error was immediately after last Data Studio update, and has been constant since then.

Can you also specify the last ADS version that worked fine for you? Was it 1.40.2?

[KDunn5517]

Sorry, I don't remember the version number. I keep it up to date as soon as a version is available and after I took the last one this started occurring.

[VytenisJ]

Greetings, same issue here. Opening a new query window or even switching between tabs gets the Request connection/listdatabases failed error.

I'm on the latest 1.41.1 version and the last working one for me was 1.40.2.

[KDunn5517]

I've also noticed there are times when the connection will just hang inside azure data studio, and require a restart of the application. This is also new since last version. This is intermittent, and happens most often when I open a new query and immediately try to execute a stored procedure.

Existing query windows with the non-admin user will continue to work. New windows with the non-admin user will all hang until application is restarted.

New connections with my admin user will work as well. There are no blocking processes on the Azure SQL server when this happens.

The non-admin user in this case is part of my developer role, which has alter and a few other permissions per schema.

[kburtram]

@alanrenmsft it looks like there have been several reports of similar issues since the 1.41 release. Could you please investigate what's happening here?

[kburtram]

These are potential duplicates of this issue.

https://github.com/microsoft/azuredatastudio/issues/21752 https://github.com/microsoft/azuredatastudio/issues/21804 https://github.com/microsoft/azuredatastudio/issues/21803

[alanrenmsft]

@KDunn5517 @VytenisJ we are going to release a hotfix to fix the issue of error popping up in an annoying way. for the issue with database list for contained users, I've created an issue to track it here: https://github.com/microsoft/azuredatastudio/issues/21884, it will be fixed in March 2023 release.

for the query hang issue, there is already an issue for it: https://github.com/microsoft/azuredatastudio/issues/7440, we are actively investigating it.

[KDunn5517]

Cool, thanks!