Scripting service Access Token Is Expired

Dvnoten commented 1 year ago

Azure Data Studio Version: 1.41.0
OS Version: Windows_NT x64 10.0.19045

Steps to Reproduce:

Connecting to Azure SQL Managed Instance database
Keep Data Studio open for a couple of days (laptop in sleep mode no full shutdowns or restarts of the applications)
After using Data Studio for a while try to open up SQL view or procedure to see the syntax using the Object Explorer.
You will get an error 'Login failed for user ''. Token is expired'.

Refreshing the connection with the database: does NOT solve it.
Disconnecting and reconnect with the database: does NOT solve it.
Restart the Azure Data Studio application: does SOLVE the issue.

Error message:

Microsoft.SqlServer.Management.Common.ConnectionFailureException: Failed to connect to server *****. ---> Microsoft.Data.SqlClient.SqlException (0x80131904): Login failed for user ''. Token is expired. 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.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides) at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides) at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnectImpl() at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect() at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect() ClientConnectionId:4d101833-6cb0-4eb3-9ce4-f0dfbb9e46a4 Error Number:18456,State:233,Class:14 --- End of inner exception stack trace --- at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect() at Microsoft.SqlTools.ServiceLayer.Scripting.ScriptAsScriptingOperation.Execute() in D:\a_work\1\s\src\Microsoft.SqlTools.ServiceLayer\Scripting\ScriptAsScriptingOperation.cs:line 84

Does this issue occur when all extensions are disabled?: Yes

cheenamalhotra commented 1 year ago

Hi @Dvnoten

Can you clarify how many days? Were you running old version of Azure Data Studio when you experienced the error?

This issue has been recently fixed in 1.41.0, which I believe got installed for you when you restarted ADS (as this version was released on January 25).

Dvnoten commented 1 year ago

Hi @Dvnoten

Can you clarify how many days? Were you running old version of Azure Data Studio when you experienced the error?

This issue has been recently fixed in 1.41.0, which I believe got installed for you when you restarted ADS (as this version was released on January 25).

Hi thank you for your swift reaction.

I thought it was fixed in the latest release as well or that was what I hoped for. It was a long awaited feature/bugfix since, let us be honest here, an application that doesn't support Azure Active Directory authorization/authentication in a stable way for all the different components is... not great. It's also the only thing that is holding us back at the moment to switch to Azure Data Studio completely. The whole Azure Active Directory authorization and authentication issues and token expirations bugs are here for too long already. So please if you need any additional logs or anything let me know so I can help with this?

To answer on your question. I installed the latest version immediately when the release was out in the hope it fixed the issue so on 25 of January the new version was installed and around 2-3 days later this issue was there again.

cheenamalhotra commented 1 year ago

Thanks,

I would recommend capturing verbose Azure Account and MSSQL logs if you do experience this issue again with MSAL (the new auth library now supported by default) and the latest release.

Please update ADS to the latest Hotfix v1.41.1 released today, and enable verbose loggings, keeping an eye on any related issues if they reoccur.

Will keep this issue under observation closely to see if it reoccurs.

github-actions[bot] commented 1 year ago

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. First open the Settings page, find the Mssql: Tracing Level setting and change that to All then restart ADS and repro your issue. Next there are two types of logs to collect:

Console Logs

Open Developer Tools (Help -> Toggle Developer Tools)
Click the Console tab
Click in the log area and select all text (CTRL+A)
Save this text into a file named console.log and attach it to this issue.
Developer Tools can be closed via Help -> Toggle Developer Tools Application Logs
Open command palette (Click View -> Command Palette)
Run the command: Developer: Open Logs Folder
This will open the log folder locally. Please zip up this folder and attach it to the issue.

github-actions[bot] commented 1 year ago

We need more info to debug your Azure Active Directory 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.

In the settings menu, find the setting titled Azure: Logging Level and select the Verbose option
Run the process that produces your error
Open command palette (Click View -> Command Palette)
Run the command: Developer: Open Logs Folder
Follow this path to find the Azure Accounts log file: [default log folder]/exthost1/output_logging_[earliest timestamp]/#-Azure Acounts.log
Please attach the Azure-Accounts.log file to the issue.

Dvnoten commented 1 year ago

Hi updated to version Hotfix v1.41.1. Issue just arised again like 2 mins ago (31/01/2023 10:43 UTC+01). Logs here in attachments. Please have a look at this.

20230131T083851.zip console.log

cheenamalhotra commented 1 year ago

Thanks for the logs @Dvnoten

I see this error which explains the issue: [error] Scripting failed. error: An error occurred while scripting the objects. Failed to connect to server ****.****.database.windows.net.

On the Azure side, there're no error, so I believe there're no authentication issue, and updated token is available, but it's not passed to the backend service (ScriptingService) requests.

Likewise, there are multiple paths/services where the backend connection does not get updated with new token due to current design issues (which we're working on changing to bring in a more robust long term design solution). The fix we made was specifically targeted for Object Explorer's Refresh and node expansion requests - so that should work. I will try to reproduce to see for any issues around Object explorer failing to refresh connection tree nodes after ScriptingService fails.

cheenamalhotra commented 1 year ago

Confirming, this is indeed reproducible, and is also related with #21766, where if you refresh dashboard after access token is expired, the error in screenshot occurs.

Refreshing OE nodes, does not refresh backend connection for Dashboard & Scripting service, possibly because they're identified by different URIs.

Dvnoten commented 1 year ago

@cheenamalhotra : thank you for the feedback! I hope the necessary priority has been given to this tickets and the related ones.

cheenamalhotra commented 1 year ago

@Dvnoten

We've introduced support that improves access token refresh behavior. The feature is now available in ADS insiders (download link) and can be enabled with below setting:

    "mssql.enableSqlAuthenticationProvider": true

It will be available in the ADS March 2023 release and is currently NOT enabled by default. We're evaluating any remaining improvements in this area to be able to enable this feature by default in future releases. Please give it a try and let us know your experience!

cheenamalhotra commented 1 year ago

Closing this issue as resolved with setting mssql.enableSqlAuthenticationProvider enabled. Verified locally in latest insiders.

microsoft / azuredatastudio

Scripting service Access Token Is Expired #21774