Options

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

  • Options
    ATurnerATurner Posts: 189 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
  • Options
    MiikaNiemiMiikaNiemi Posts: 2 New member
    Yes, there is one extra authentication window popping up when I click query. 
    · Share on Twitter
  • Options
    ATurnerATurner Posts: 189 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
  • Options
    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
  • Options
    Chris_DuranteChris_Durante Posts: 7 New member
    edited June 8, 2023 7:33PM
    sorry for duplicate
    · Share on Twitter
  • Options
    Chris_DuranteChris_Durante Posts: 7 New member
    edited June 8, 2023 7:34PM
    sorry for duplicate :cry:
    · Share on Twitter
  • Options
    ATurnerATurner Posts: 189 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
  • Options
    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
  • Options
    joetcjoetc Posts: 1 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
  • Options
    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
  • Options
    KjMKjM Posts: 1 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
  • Options
    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
Sign In or Register to comment.