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

SQL Prompt not working when using AAD - Universal with MFA authentication

I updated to SSMS 19.0.2. and SQL Prompt didn't load table and column names etc. From the logs I discovered that there was a "110003;Invalid user or password"-error and server failed to load. I've used AAD - Universal with MFA authentication earlier and it seems it was causing the problem with SSMS 19.0.2. and SQL Prompt. I changed authentication to AAD - Password and SQL Prompt works now. Would like to use MFA though, could this be fixed?
MiikaNiemi
0

Comments

17 comments

Sort by
  • ATurner
    Hi MiikaNiemi

    If you look on the taskbar you should see an extra authentication window pop up once you click into the query in SSMS?
    This is an issue the dev team are aware of and are currently looking into.
    ATurner
    0
  • MiikaNiemi
    Yes, there is one extra authentication window popping up when I click query.
    MiikaNiemi
    0
  • ATurner
    Hi MiikaNiemi

    For the time being you will need to open this extra authentication window and select it. The team are aware of it and are looking into a resolution.
    ATurner
    0
  • Chris_Durante
    Hi,

    A related issue we're having seems to be a regression where suggestions aren't loaded when using AAD auth in a secondary tenant - e.g. the Azure SQL server is not in the user's primary tenant.

    My team members can't use SQL Prompt at all as MFA is required and our identities are in one tenant, while all our Azure SQL servers are in a differnet tenant.

    See post:
    Suggestions not working with AzureSQL and Azure SQL Managed Instance with AAD and MFA — Redgate forums (red-gate.com)

    Here's a log when trying to "refresh suggestions":

    2023-05-10 12:19:51.203 -07:00 [Debug] Sending command to server: StoreFullRevision
    2023-05-10 12:19:51.204 -07:00 [Verbose] Named pipe created - connecting...
    2023-05-10 12:19:51.204 -07:00 [Verbose] Named pipe connected
    2023-05-10 12:19:51.207 -07:00 [Verbose] Sending operation json: {"operation":{"type":"StoreFullRevision","message":{"clientIdentifier":"f3ec425c-f11e-491d-a53e-6e43f8d9511b","queryIdentity":"1b3b434e-8646-42f4-a1b6-55d781f6f252","name":"SQLQuery1.sql","path":"C:\\Users\\****\\AppData\\Local\\Temp\\~vsD833.sql","contents":"","connection":null,"saveReason":"QueryEdited"}},"operationType":"command"}
    2023-05-10 12:19:51.379 -07:00 [Information] Created new ServerCandidate for server: m*****g.database.windows.net
    2023-05-10 12:19:51.388 -07:00 [Debug] Checking is contained or restricted
    2023-05-10 12:19:52.292 -07:00 [Warning] Failed to load server m*****g.database.windows.net
    System.Data.SqlClient.SqlException (0x80131904): Login failed for user ''.
       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()
    ClientConnectionId:9e7e332f-3935-4d1c-b172-f2659cda173b
    Error Number:18456,State:1,Class:14
    2023-05-10 12:19:55.319 -07:00 [Debug] AutoRefreshing database m*****d
    2023-05-10 12:19:56.062 -07:00 [Debug] An error occurred while auto-refreshing
    System.Data.SqlClient.SqlException (0x80131904): Login failed for user ''.
       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.CommonUI.Plugins.AutoRefreshPlugin.AutoRefresh.<Start>b__11_0()
    ClientConnectionId:daa40988-e963-43d5-9e10-fa76d6663c1e
    Error Number:18456,State:1,Class:14
    2023-05-10 12:19:59.568 -07:00 [Debug] Checking is contained or restricted
    2023-05-10 12:20:00.624 -07:00 [Warning] Failed to load server m*****g.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()
    ClientConnectionId:b6ee778b-a9d3-42a0-8bab-02e41a85eac8
    Error Number:18456,State:235,Class:14


    Chris_Durante
    0
  • Chris_Durante
    sorry for duplicate
    Chris_Durante
    0
  • Chris_Durante
    sorry for duplicate :cry:
    Chris_Durante
    0
  • ATurner
    This should be fixed in 10.13.13 if you could test and let me know? This should be fixed in 10.13.13
    https://download.red-gate.com/checkforupdates/SQLPrompt/SQLPrompt_10.13.13.2147.exe
    ATurner
    0
  • Chris_Durante
    ATurner said:
    This should be fixed in 10.13.13 if you could test and let me know? This should be fixed in 10.13.13
    https://download.red-gate.com/checkforupdates/SQLPrompt/SQLPrompt_10.13.13.2147.exe
    Confirmed Suggestions now work with AAD auth + MFA and SSMS 19.1, even when Azure SQL or MI is in a different tenant than the user's home tenant.

    Thanks @ATurner
    Chris_Durante
    0
  • joetc
    This is broken now with SQL Prompt 10.14 .. Had to downgrade to 10.13 to get my prompt ability back when using AAD for auth
    joetc
    0
  • TomThorp
    @joetc I have the same issue after upgrading to 10.14 using SSMS 19.3 and MFA. It continues to work for SSMS 18.12 using AAD auth that I have running alongside for testing purposes.

    To get my prompt ability back in 19.3 I have had to downgrade to the above mentioned SQL Prompt v10.13.13.2147

    It would be great to get a fix for this. You've made us reliant on this tool with it being great :-)
    TomThorp
    0
  • KjM
    2024-04-18 13:52:14.864 -05:00 [Warning] Failed to load server xxxxxxxx
    Microsoft.Data.SqlClient.SqlException (0x80131904): Login failed for user '<token-identified principal>'.
       at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
       at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

    SSMS 19.3.4
    SQLPrompt 10.14.14.8187

    Authentication is using Microsoft Entra MFA to managed instances in Azure. I've been without a functioning SQL Prompt for a while now. Recently re-upped the license, but am beginning to ask "If I can 'manage' without it, why pay?" A question that's becoming harder to answer, as you might imagine, as the days without a fix go by.
    KjM
    0
  • KnightRider
    I'm experiencing the same issues as KjM. Works well with a SQL login, but not with MFA. Really hope this gets fixed before other on my team start working in a managed instance.
    KnightRider
    0
  • rsimmons
    Just finally updated to SSMS20, updated to the latest SQL prompt, and it's also not working for us. We use MFA as well. It does still work in SSMS18.
    rsimmons
    0
  • KjM
    It's now July. I'm using SSMS 20.1. I can no long downgrade my SQL Prompt to regain the main reason I pay for the add-in and the subject of much of its marketing – so I'm left wondering if I should just save my money as I'm "coping" with having to type table & column names. I mean, the ability to format the SQL to my liking is all very fine, but that's a nice addition to core functionality, core functionality that's now been missing for some time.
    KjM
    0
  • skyfoster82
    SSMS - 18.12.1   works with SQL Prompt 10.14.21.9884 - using (AAD - Universal)  MFA
    skyfoster82
    0
  • joetc
    The setting is "Microsoft Entra with MFA", and it is still broken with this selection. SSMS 19.3
    joetc
    0
  • joetc
    bump
    joetc
    0

Add comment

Please sign in to leave a comment.