SQL Prompt 10 / SSMS 19.1 / AAD Universal with MFA Azure Managed Instance - No suggestions

Stephen77

SQL Prompt 10.13.9.869
SSMS: 19.1.56.0

When connecting to an Azure managed instance authenticating via "Azure Active Directory - Universal with MFA" while I get snippets I don't get any database related suggestions. In the logs I can see error

[Warning] Failed to load server <server-name-redacted>.database.windows.net

System.Data.SqlClient.SqlException (0x80131904): Login failed for user '<token-identified principal>'. The server is not currently configured to accept this token.

   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)

   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.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)

   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)

   at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)

   at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)

   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)

   at System.Data.SqlClient.SqlConnection.Open()

   at RedGate.SqlPrompt.Cache.Candidates.ServerCandidate.IsContainedOrRestrictedDatabase(ConnectionProperties cp)

   at RedGate.SqlPrompt.Cache.Candidates.ServerCandidate.LoadDatabaseContainment()

   at RedGate.SqlPrompt.Cache.Candidates.ServerCandidate.LoadServer()

Worked on SSMS 18

Jon_Kirkwood

Hi @Stephen77, @Koll, @Chris_Durante

 

Thank you for your patience whilst our development team worked on this issue.

Version 10.13.13 has been released, which includes a fix for this issue.

Are you able to patch at your earliest convenience and verify this has been resolved for you

 

Download of the latest version available here: https://download.red-gate.com/checkforupdates/SQLPrompt/SQLPrompt_10.13.13.2147.exe

 

Patch notes available here: https://documentation.red-gate.com/sp/release-notes-and-other-versions/sql-prompt-10-13-release-notes

SQL Prompt 10.13 release notes

10.13.13.2147 - Released on 22 August 2023

Features

• Added a feature for saving SQL History advanced search settings.
• Added support for multiple syntaxes on Azure SQL database.
   

Fixes

• Fixed an issue with connecting via Azure Active Directory with MFA for SSMS19.1.
• Fixed AT TIME ZONE expressions recognition.
• Fixed formatting issue for TRIM in SQL Server 2022.
• Fixed "Could not load file or assembly System.Threading.Tasks.Extensions" error occurring when using Azure Active Directory authentication methods.
• Fixed an issue with columns not having data type when created by CAST function.

Victoria W

Hi @Stephen77

I'm afraid you've run into a bug. The development team are currently working on it so I'll let you know once we have a fix.

Sorry for the inconvenience.

Stephen77

OK thanks @Victoria W for the update

Chris_Durante

My team continues to have the same issue. It's very similar to an issue we had with the last version and Azure AD authentication with AzureSQL and SQL MI:
Suggestions not working with AzureSQL and Azure SQL Managed Instance with AAD and MFA — Redgate forums (red-gate.com)

Chris_Durante

Victoria W said:

Hi @Stephen77

I'm afraid you've run into a bug. The development team are currently working on it so I'll let you know once we have a fix.

Sorry for the inconvenience.

@Vic@"Victoria W" Any update on this bug? The product is unusable with our company's cloud governance policies and software configuration solution requiring AAD MFA for Azure SQL and SSMS19

Koll

Same issue, have to revert back to SSMS18 to use SQL Prompt.

Stephen77

Thats's all working for me, thanks @Jon_Kirkwood!

Chris_Durante

Hi @Jon_Kirkwood - It's working for our Azure SQL and MI instances with AAD + MFA. Thanks!

ali_rizvi

Hello @Jon_Kirkwood I am continuing to have this issue with latest version of SQL prompt and SSMS 19.1 the suggestion are not loading and the refresh suggestion does not work. Kindly update on the fix for this issue.

gigabyte77

Hi!  I am also having this issue (connecting to Azure SQL Managed Instance with MFA) with SQL Prompt 10.14.18.8999 and SSMS 19.3/20.1.