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

MiikaNiemiMiikaNiemi Posts: 2 New member
edited March 29, 2023 11:21AM in Root
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?
Tagged:
· Share on Twitter

Answers

  • ATurnerATurner Posts: 204 Silver 2
    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. 
    · Share on Twitter
  • MiikaNiemiMiikaNiemi Posts: 2 New member
    Yes, there is one extra authentication window popping up when I click query. 
    · Share on Twitter
  • ATurnerATurner Posts: 204 Silver 2
    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. 
    · Share on Twitter
  • Chris_DuranteChris_Durante Posts: 7 New member
    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


    · Share on Twitter
  • Chris_DuranteChris_Durante Posts: 7 New member
    edited June 8, 2023 7:33PM
    sorry for duplicate
    · Share on Twitter
  • Chris_DuranteChris_Durante Posts: 7 New member
    edited June 8, 2023 7:34PM
    sorry for duplicate :cry:
    · Share on Twitter
  • ATurnerATurner Posts: 204 Silver 2
    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
    · Share on Twitter
  • Chris_DuranteChris_Durante Posts: 7 New member
    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
    · Share on Twitter
  • joetcjoetc Posts: 3 New member
    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
    · Share on Twitter
  • TomThorpTomThorp Posts: 1 New member
    @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 :-)
    · Share on Twitter
  • KjMKjM Posts: 2 Bronze 1
    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.
    · Share on Twitter
  • KnightRiderKnightRider Posts: 1 New member
    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. 
    · Share on Twitter
  • rsimmonsrsimmons Posts: 1 New member
    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.
    · Share on Twitter
  • KjMKjM Posts: 2 Bronze 1
    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.
    · Share on Twitter
  • skyfoster82skyfoster82 Posts: 1 New member
    SSMS - 18.12.1   works with SQL Prompt 10.14.21.9884 - using (AAD - Universal)  MFA
    · Share on Twitter
  • joetcjoetc Posts: 3 New member
    edited September 22, 2024 12:19PM
    The setting is "Microsoft Entra with MFA", and it is still broken with this selection. SSMS 19.3
    · Share on Twitter
  • joetcjoetc Posts: 3 New member
    bump
    · Share on Twitter
Sign In or Register to comment.