How can we help you today? How can we help you today?

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

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
Stephen77
0

Comments

10 comments

Sort by
  • 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.
    Victoria W
    0
  • Stephen77
    OK thanks @Victoria W for the update
    Stephen77
    0
  • 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
    0
  • 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
    Chris_Durante
    0
  • Koll
    Same issue, have to revert back to SSMS18 to use SQL Prompt.
    Koll
    0
  • 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.
    Jon_Kirkwood
    0
  • Stephen77
    Thats's all working for me, thanks @Jon_Kirkwood!
    Stephen77
    0
  • Chris_Durante
    Hi @Jon_Kirkwood - It's working for our Azure SQL and MI instances with AAD + MFA. Thanks!
    Chris_Durante
    0
  • 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.
    ali_rizvi
    0
  • 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.  
    gigabyte77
    0

Add comment

Please sign in to leave a comment.