AAD Auth Error - Login failed for user ''

Published May 26 2020 03:50 AM 89.6K Views
Microsoft

An error may appear when trying to login to Azure SQL DB using AAD authentication

 

===================================
Cannot connect to SERVENAME.database.windows.net.
===================================
Login failed for user '<token-identified principal>'. (.Net SqlClient Data Provider)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&Evtsrc=MSSQLServer&EvtID=18456&LinkId=20476
------------------------------
Server Name: SERVENAME.database.windows.net
Error Number: 18456
Severity: 14
State: 1
Line Number: 65536
------------------------------
Program Location:
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 Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()
 
This error mean the user is invalid, usually related to a AAD user that does not have user created on SQL DB that you are trying to connect (User DB or Master DB) or that the user is not the AAD Server Admin
 
 
  • CREATE USER <Azure_AD_principal_name> FROM EXTERNAL PROVIDER;
  • CREATE USER [bob@contoso.com] FROM EXTERNAL PROVIDER;
  • CREATE USER [alice@fabrikam.onmicrosoft.com] FROM EXTERNAL PROVIDER;
  • CREATE USER [ICU Nurses] FROM EXTERNAL PROVIDER;
  • CREATE USER [appName] FROM EXTERNAL PROVIDER;
 
*If you are connecting from SSMS you may also need to change the default database option (Image below). By default it will try to connect to master DB where this user may not exists there as AAD users are contained inside each user database.
 
2020-05-26 11_44_05-Clipboard.png

 

15 Comments
%3CLINGO-SUB%20id%3D%22lingo-sub-1417535%22%20slang%3D%22en-US%22%3EAAD%20Auth%20Error%20-%20Login%20failed%20for%20user%20'%3CTOKEN-IDENTIFIED%20principal%3D%22%22%3E'%3C%2FTOKEN-IDENTIFIED%3E%3CLINGO-BODY%20id%3D%22lingo-body-1417535%22%20slang%3D%22en-US%22%3E%3CP%3EAn%20error%20may%20appear%20when%20trying%20to%20login%20to%20Azure%20SQL%20DB%20using%20AAD%20authentication%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3E%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3CBR%20%2F%3ECannot%20connect%20to%20SERVENAME.database.windows.net.%3CBR%20%2F%3E%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3CBR%20%2F%3E%3CFONT%20color%3D%22%23FF0000%22%3E%3CSTRONG%3ELogin%20failed%20for%20user%20'%3CTOKEN-IDENTIFIED%20principal%3D%22%22%3E'.%20(.Net%20SqlClient%20Data%20Provider)%3C%2FTOKEN-IDENTIFIED%3E%3C%2FSTRONG%3E%3C%2FFONT%3E%3CBR%20%2F%3E------------------------------%3CBR%20%2F%3EFor%20help%2C%20click%3A%20http%3A%2F%2Fgo.microsoft.com%2Ffwlink%3FProdName%3DMicrosoft%2520SQL%2520Server%26amp%3BEvtsrc%3DMSSQLServer%26amp%3BEvtID%3D18456%26amp%3BLinkId%3D20476%3CBR%20%2F%3E------------------------------%3CBR%20%2F%3EServer%20Name%3A%20SERVENAME.database.windows.net%3CBR%20%2F%3EError%20Number%3A%2018456%3CBR%20%2F%3ESeverity%3A%2014%3CBR%20%2F%3EState%3A%201%3CBR%20%2F%3ELine%20Number%3A%2065536%3CBR%20%2F%3E------------------------------%3CBR%20%2F%3EProgram%20Location%3A%3CBR%20%2F%3Eat%20System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity%20identity%2C%20SqlConnectionString%20connectionOptions%2C%20SqlCredential%20credential%2C%20Object%20providerInfo%2C%20String%20newPassword%2C%20SecureString%20newSecurePassword%2C%20Boolean%20redirectedUserInstance%2C%20SqlConnectionString%20userConnectionOptions%2C%20SessionData%20reconnectSessionData%2C%20DbConnectionPool%20pool%2C%20String%20accessToken%2C%20Boolean%20applyTransientFaultHandling%2C%20SqlAuthenticationProviderManager%20sqlAuthProviderManager)%3CBR%20%2F%3Eat%20System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions%20options%2C%20DbConnectionPoolKey%20poolKey%2C%20Object%20poolGroupProviderInfo%2C%20DbConnectionPool%20pool%2C%20DbConnection%20owningConnection%2C%20DbConnectionOptions%20userOptions)%3CBR%20%2F%3Eat%20System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection%20owningConnection%2C%20DbConnectionPoolGroup%20poolGroup%2C%20DbConnectionOptions%20userOptions)%3CBR%20%2F%3Eat%20System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection%20owningConnection%2C%20TaskCompletionSource%601%20retry%2C%20DbConnectionOptions%20userOptions%2C%20DbConnectionInternal%20oldConnection%2C%20DbConnectionInternal%26amp%3B%20connection)%3CBR%20%2F%3Eat%20System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection%20outerConnection%2C%20DbConnectionFactory%20connectionFactory%2C%20TaskCompletionSource%601%20retry%2C%20DbConnectionOptions%20userOptions)%3CBR%20%2F%3Eat%20System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection%20outerConnection%2C%20DbConnectionFactory%20connectionFactory%2C%20TaskCompletionSource%601%20retry%2C%20DbConnectionOptions%20userOptions)%3CBR%20%2F%3Eat%20System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource%601%20retry)%3CBR%20%2F%3Eat%20System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource%601%20retry)%3CBR%20%2F%3Eat%20System.Data.SqlClient.SqlConnection.Open()%3CBR%20%2F%3Eat%20Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo%20ci%2C%20IServerType%20server)%3CBR%20%2F%3Eat%20Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()%3C%2FPRE%3E%0A%3CDIV%20class%3D%22repos-diff-contents-row%20monospaced-text%22%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%20class%3D%22repos-diff-contents-row%20monospaced-text%22%3E%3CSPAN%20class%3D%22padding-right-8%20repos-line-content%20added%22%20data-mline%3D%2242%22%3EThis%20error%20mean%20the%20user%20is%20invalid%2C%20usually%20related%20to%20a%20%3CFONT%20color%3D%22%230000FF%22%3E%3CSTRONG%3EAAD%20user%20that%20does%20not%20have%20user%20created%20on%20SQL%20DB%3C%2FSTRONG%3E%3C%2FFONT%3E%3CFONT%20color%3D%22%23333333%22%3E%20that%20you%20are%20trying%20to%20connect%20(%3CFONT%20color%3D%22%230000FF%22%3E%3CSTRONG%3EUser%20DB%20or%20Master%20DB%3C%2FSTRONG%3E%3C%2FFONT%3E)%20%3C%2FFONT%3Eor%20that%20the%20user%20is%20%3CSTRONG%3E%3CFONT%20color%3D%22%230000FF%22%3Enot%20the%20AAD%20Server%20Admin%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%20class%3D%22repos-diff-contents-row%20monospaced-text%22%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%20class%3D%22repos-diff-contents-row%20monospaced-text%22%3E%0A%3CDIV%20class%3D%22repos-diff-contents-row%20monospaced-text%22%3E%3CSPAN%20class%3D%22padding-right-8%20repos-line-content%20added%22%20data-mline%3D%2269%22%3EJust%20create%20the%20user%20in%20the%20DB%20-%26nbsp%3B%3C%2FSPAN%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsql-database%2Fsql-database-aad-authentication-configure%3Ftabs%3Dazure-powershell%23create-contained-database-users-in-your-database-mapped-to-azure-ad-identities%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%3CSPAN%20class%3D%22padding-right-8%20repos-line-content%20added%22%20data-mline%3D%2270%22%3ECreate%20contained%20database%20users%20in%20your%20database%20mapped%20to%20Azure%20AD%20identities%3C%2FSPAN%3E%3C%2FA%3E%3C%2FDIV%3E%0A%3CDIV%20class%3D%22repos-diff-contents-row%20monospaced-text%22%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CUL%3E%0A%3CLI%20class%3D%22repos-diff-contents-row%20monospaced-text%22%3E%3CSPAN%20class%3D%22padding-right-8%20repos-line-content%20added%22%20data-mline%3D%2272%22%3ECREATE%20USER%20%3CAZURE_AD_PRINCIPAL_NAME%3E%20FROM%20EXTERNAL%20PROVIDER%3B%3C%2FAZURE_AD_PRINCIPAL_NAME%3E%3C%2FSPAN%3E%3C%2FLI%3E%0A%3CLI%20class%3D%22repos-diff-contents-row%20monospaced-text%22%3E%3CSPAN%20class%3D%22padding-right-8%20repos-line-content%20added%22%20data-mline%3D%2273%22%3ECREATE%20USER%20%5Bbob%40contoso.com%5D%20FROM%20EXTERNAL%20PROVIDER%3B%3C%2FSPAN%3E%3C%2FLI%3E%0A%3CLI%20class%3D%22repos-diff-contents-row%20monospaced-text%22%3E%3CSPAN%20class%3D%22padding-right-8%20repos-line-content%20added%22%20data-mline%3D%2274%22%3ECREATE%20USER%20%5Balice%40fabrikam.onmicrosoft.com%5D%20FROM%20EXTERNAL%20PROVIDER%3B%3C%2FSPAN%3E%3C%2FLI%3E%0A%3CLI%20class%3D%22repos-diff-contents-row%20monospaced-text%22%3E%3CSPAN%20class%3D%22padding-right-8%20repos-line-content%20added%22%20data-mline%3D%2275%22%3ECREATE%20USER%20%5BICU%20Nurses%5D%20FROM%20EXTERNAL%20PROVIDER%3B%3C%2FSPAN%3E%3C%2FLI%3E%0A%3CLI%20class%3D%22repos-diff-contents-row%20monospaced-text%22%3E%3CSPAN%20class%3D%22padding-right-8%20repos-line-content%20added%22%20data-mline%3D%2276%22%3ECREATE%20USER%20%5BappName%5D%20FROM%20EXTERNAL%20PROVIDER%3B%3C%2FSPAN%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CDIV%20class%3D%22repos-diff-contents-row%20monospaced-text%22%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%20class%3D%22repos-diff-contents-row%20monospaced-text%22%3E*If%20you%20are%20connecting%20from%20SSMS%20you%20may%20also%26nbsp%3Bneed%20to%20%3CSTRONG%3Echange%20the%20default%20database%20option%20(Image%20below)%3C%2FSTRONG%3E.%20By%20default%20it%20will%20try%20to%20connect%20to%20%3CSTRONG%3Emaster%3C%2FSTRONG%3E%20DB%20where%20this%20user%20may%20not%20exists%20there%20as%20AAD%20users%20are%20contained%20inside%20each%20user%20database.%3C%2FDIV%3E%0A%3CDIV%20class%3D%22repos-diff-contents-row%20monospaced-text%22%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%20class%3D%22repos-diff-contents-row%20monospaced-text%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%222020-05-26%2011_44_05-Clipboard.png%22%20style%3D%22width%3A%20478px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F194386i98745BE836117376%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%222020-05-26%2011_44_05-Clipboard.png%22%20alt%3D%222020-05-26%2011_44_05-Clipboard.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1417535%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzureSQLDB%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Econnectivity%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3C%2FLINGO-SUB%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1470775%22%20slang%3D%22en-US%22%3ERe%3A%20AAD%20Auth%20Error%20-%20Login%20failed%20for%20user%20'%3CTOKEN-IDENTIFIED%20principal%3D%22%22%3E'%3C%2FTOKEN-IDENTIFIED%3E%3CLINGO-BODY%20id%3D%22lingo-body-1470775%22%20slang%3D%22en-US%22%3E%3CP%3EWhat%20about%20when%20you're%20the%20server's%20%22Active%20Directory%20Admin%22.%20i%20don't%20have%20a%20user%20created%20on%20each%20database%2C%20but%20i%20should%20be%20able%20to%20log%20in%20regardless%20in%20order%20to%20be%20able%20to%20grant%20access%20to%20other%20AAD%20Principals...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1474154%22%20slang%3D%22en-US%22%3ERe%3A%20AAD%20Auth%20Error%20-%20Login%20failed%20for%20user%20'%3CTOKEN-IDENTIFIED%20principal%3D%22%22%3E'%3C%2FTOKEN-IDENTIFIED%3E%3CLINGO-BODY%20id%3D%22lingo-body-1474154%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F702293%22%20target%3D%22_blank%22%3E%40JuanParodi%3C%2FA%3E%26nbsp%3Bin%20this%20case%20(AAD%20Server%20Admin)%20yes%2C%20you%20should%20be%20able%20to%20login%20to%20ANY%20database.%20If%20getting%20error%20being%20AAD%20Server%20Admin%20we%20would%20need%20to%20investigate%20further%20and%20you%20can%20open%20a%20case%20on%20that%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1525898%22%20slang%3D%22en-US%22%3ERe%3A%20AAD%20Auth%20Error%20-%20Login%20failed%20for%20user%20'%3CTOKEN-IDENTIFIED%20principal%3D%22%22%3E'%3C%2FTOKEN-IDENTIFIED%3E%3CLINGO-BODY%20id%3D%22lingo-body-1525898%22%20slang%3D%22en-US%22%3E%3CP%3EAny%20progress%20on%20this%20issue%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1541063%22%20slang%3D%22en-US%22%3ERe%3A%20AAD%20Auth%20Error%20-%20Login%20failed%20for%20user%20'%3CTOKEN-IDENTIFIED%20principal%3D%22%22%3E'%3C%2FTOKEN-IDENTIFIED%3E%3CLINGO-BODY%20id%3D%22lingo-body-1541063%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F146055%22%20target%3D%22_blank%22%3E%40Edvard%20Gundersen%3C%2FA%3E%26nbsp%3Bthis%20is%20not%20an%20issue.%20This%20is%20an%20expected%20behavior...%20When%20you%20create%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Frelational-databases%2Fsecurity%2Fcontained-database-users-making-your-database-portable%3Fview%3Dsql-server-ver15%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%3CSTRONG%3Econtained%20users%3C%2FSTRONG%3E%3C%2FA%3E%2C%20in%20this%20case%20was%20AAD%20user%2C%20but%20could%20be%20SQL%20login%20(contained%20user).%20You%20are%20only%20able%20to%20connect%20to%20the%20specified%20DB%2C%20if%20you%20want%20to%20connect%20to%20master%20db%20or%20to%20list%20the%20available%20dbs%20(master%20metadata)%20you%20need%20to%20have%20this%20user%20created%20also%20on%20master.%3C%2FP%3E%0A%3CP%3E-%20If%20user%20is%20application%20user%2C%20its%20not%20needed%20any%20permission%20on%20master.%20And%20application%20connection%20should%20specify%20database%20name%3C%2FP%3E%0A%3CP%3E-%20If%20user%20is%20a%20SSMS%20user%20and%20if%20you%20do%20not%20want%20to%20always%20specify%20the%20database%20name%20you%20can%20create%20user%20on%20master%20DB%20to%20have%20a%20more%20simple%20navigation%20on%20SSMS.%20You%20do%20not%20need%20any%20special%20permission%20on%20master%2C%20just%20create%20user%20on%20master%20should%20be%20enough%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CTABLE%20class%3D%22table%22%3E%0A%3CTHEAD%3E%0A%3CTR%3E%0A%3CTH%3ETraditional%20model%3C%2FTH%3E%0A%3CTH%3EContained%20database%20user%20model%3C%2FTH%3E%0A%3C%2FTR%3E%0A%3C%2FTHEAD%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTD%3EWhen%20connected%20to%20the%20master%20database%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3CCODE%3ECREATE%20LOGIN%20login_name%20WITH%20PASSWORD%20%3D%20'strong_password'%3B%3C%2FCODE%3E%3CBR%20%2F%3E%3CBR%20%2F%3EThen%20when%20connected%20to%20a%20user%20database%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3CCODE%3ECREATE%20USER%20'user_name'%20FOR%20LOGIN%20'login_name'%3B%3C%2FCODE%3E%3C%2FTD%3E%0A%3CTD%3EWhen%20connected%20to%20a%20user%20database%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3CCODE%3ECREATE%20USER%20user_name%20WITH%20PASSWORD%20%3D%20'strong_password'%3B%3C%2FCODE%3E%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1542291%22%20slang%3D%22en-US%22%3ERe%3A%20AAD%20Auth%20Error%20-%20Login%20failed%20for%20user%20'%3CTOKEN-IDENTIFIED%20principal%3D%22%22%3E'%3C%2FTOKEN-IDENTIFIED%3E%3CLINGO-BODY%20id%3D%22lingo-body-1542291%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20running%20into%20a%20similar%20issue%20with%20being%20able%20to%20deploy%20a%20NEW%20database%20onto%20an%20Azure%20SQL%20Server%20using%20token-based%20authentication%20from%20an%20Azure%20VM.%26nbsp%3B%20I've%20added%20the%20Azure%20VM%20to%20the%20database%20server's%20firewall%2C%20I've%20ensured%20that%20the%20Azure%20VM%20has%20a%20system-assigned%20identity.%26nbsp%3B%20When%20I%20publish%20the%20DACPAC%20to%20an%20existing%20Azure%20SQL%20database%2C%20there%20is%20no%20problem.%26nbsp%3B%20When%20I%20try%20to%20use%20the%20same%20DACPAC%20to%20create%20a%20NEW%20database%20by%20simply%20changing%20the%20database%20name%2C%20I%20get%20the%20error%20message%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELogin%20failed%20foruser%20'%3CTOKEN-IDENTIFIED%20principal%3D%22%22%3E'%3C%2FTOKEN-IDENTIFIED%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can't%20create%20the%20contained%20user%20before%20the%20database%20is%20created%2C%20obviously.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1582467%22%20slang%3D%22en-US%22%3ERe%3A%20AAD%20Auth%20Error%20-%20Login%20failed%20for%20user%20'%3CTOKEN-IDENTIFIED%20principal%3D%22%22%3E'%3C%2FTOKEN-IDENTIFIED%3E%3CLINGO-BODY%20id%3D%22lingo-body-1582467%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20are%20probably%20using%20a%20guest%20invite%20for%20someone%20who%20has%20access%20to%20multiple%20Azure%20tenants.%20If%20that's%20the%20case%2C%20they%20need%20to%20specify%20the%20tenant%20ID%20as%20well%20in%20the%20SSMS%20connection%20options%20at%20the%20bottom.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20tenant%20ID%20is%20xxxx.onmicrosoft.com%20(the%20tenant%20containing%20the%20SQL%20DB)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1619925%22%20slang%3D%22en-US%22%3ERe%3A%20AAD%20Auth%20Error%20-%20Login%20failed%20for%20user%20'%3CTOKEN-IDENTIFIED%20principal%3D%22%22%3E'%3C%2FTOKEN-IDENTIFIED%3E%3CLINGO-BODY%20id%3D%22lingo-body-1619925%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%20I'm%20having%20an%20issue%20when%20trying%20to%20connect%20an%20Active%20Directory%20user%20that%20is%20the%20Active%20Directory%20Admin%20over%20this%20sql%20server.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20been%20connecting%20to%20this%20database%20in%20the%20past%20w%2Fout%20any%20issues%20through%20Management%20Studio.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20all%20of%20the%20sudden%20I%20am%20getting%20this%20error%3A%26nbsp%3Blogin%20failed%20for%20user%20'%3CTOKEN-IDENTIFIED%20principal%3D%22%22%3E'%3C%2FTOKEN-IDENTIFIED%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAgain%2C%20the%20user%20I'm%20using%20is%20the%20Active%20Directory%20Admin%20over%20this%20instance%20along%20w%2F%20the%20databases%20within%20this%20instance%20in%20our%20Azure%20environment.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%2C%20I%20have%20tried%20just%20connecting%20to%20any%20other%20database%20other%20than%20the%20default%20(Master)%20db%20with%20no%20success.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20anyone%20know%20why%20it's%20all%20the%20sudden%20doing%20this%20and%20what%20the%20fix%20is%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1622554%22%20slang%3D%22en-US%22%3ERe%3A%20AAD%20Auth%20Error%20-%20Login%20failed%20for%20user%20'%3CTOKEN-IDENTIFIED%20principal%3D%22%22%3E'%3C%2FTOKEN-IDENTIFIED%3E%3CLINGO-BODY%20id%3D%22lingo-body-1622554%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F774998%22%20target%3D%22_blank%22%3E%40MrGalvan%3C%2FA%3E%26nbsp%3Bin%20this%20case%20as%20you%20said%20this%20is%20Server%20Admin%20this%20issue%20should%20not%20happen.%20Could%20be%20something%20else.%20I%20suggest%20you%20to%20open%20a%20case%20to%20further%20investigated%20it%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1623665%22%20slang%3D%22en-US%22%3ERe%3A%20AAD%20Auth%20Error%20-%20Login%20failed%20for%20user%20'%3CTOKEN-IDENTIFIED%20principal%3D%22%22%3E'%3C%2FTOKEN-IDENTIFIED%3E%3CLINGO-BODY%20id%3D%22lingo-body-1623665%22%20slang%3D%22en-US%22%3E%3CP%3EFound%20the%20issue%20and%20have%20resolved%20it.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20happened%20is%20the%20AD%20group%20that%20was%20assigned%20as%20the%20Active%20Directory%20Admin%20was%20dropped%20and%20created%20w%2F%20the%20same%20name.%20However%2C%20the%20old%20group's%20Object%20Id%20wasn't%20the%20same%20as%20the%20newly%20created%20AD%20group's%20Object%20Id.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOnly%20found%20this%20out%20by%20looking%20at%20the%20Creation%20Date%20of%20the%20Active%20Directory%20Group.%20Which%20shows%20it%20was%20a%20recent%20creation%20date.%20A%20time%20frame%20within%20where%20the%20issue%20started%20happening.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20it%20appears%20that%20the%20token%20assigned%20to%20the%20old%20group%20was%20still%20saved%2C%20but%20that%20Object%20Id%20no%20logger%20existed%20since%20the%20group%20was%20re-created%20w%2F%20a%20new%20Object%20Id.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ERemedy%3A%3C%2FSTRONG%3E%20Just%20dropped%20AD%20admin%20and%20re-added%20it%20and%20now%20we're%20back%20in%20business.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1624960%22%20slang%3D%22en-US%22%3ERe%3A%20AAD%20Auth%20Error%20-%20Login%20failed%20for%20user%20'%3CTOKEN-IDENTIFIED%20principal%3D%22%22%3E'%3C%2FTOKEN-IDENTIFIED%3E%3CLINGO-BODY%20id%3D%22lingo-body-1624960%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F774998%22%20target%3D%22_blank%22%3E%40MrGalvan%3C%2FA%3E%26nbsp%3Btks%20for%20the%20feedback%20and%20for%20adding%20the%20solution%20to%20this%20article.%20This%20may%20help%20others%20too%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2118017%22%20slang%3D%22en-US%22%3ERe%3A%20AAD%20Auth%20Error%20-%20Login%20failed%20for%20user%20'%3CTOKEN-IDENTIFIED%20principal%3D%22%22%3E'%3C%2FTOKEN-IDENTIFIED%3E%3CLINGO-BODY%20id%3D%22lingo-body-2118017%22%20slang%3D%22en-US%22%3E%3CBLOCKQUOTE%3E%3CHR%20%2F%3E%3CP%3EYou%20are%20probably%20using%20a%20guest%20invite%20for%20someone%20who%20has%20access%20to%20multiple%20Azure%20tenants.%20If%20that's%20the%20case%2C%20they%20need%20to%20specify%20the%20tenant%20ID%20as%20well%20in%20the%20SSMS%20connection%20options%20at%20the%20bottom.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20tenant%20ID%20is%20xxxx.onmicrosoft.com%20(the%20tenant%20containing%20the%20SQL%20DB)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FBLOCKQUOTE%3E%3CP%3EThis%20is%20interestingly%20not%20true%20in%20the%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-sql%2Fdatabase%2Fauthentication-mfa-ssms-overview%23azure-ad-domain-name-or-tenant-id-parameter%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3E18.x%20versions%20of%20SSMS%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CBLOCKQUOTE%3E%3CP%3E%3CSPAN%3EIf%20you%20are%20running%20SSMS%2018.x%20or%20later%2C%20the%20AD%20domain%20name%20or%20tenant%20ID%20is%20no%20longer%20needed%20for%20guest%20users%20because%2018.x%20or%20later%20automatically%20recognizes%20it.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FBLOCKQUOTE%3E%3CP%3EFunny%20enough%20-%20i%20login%20to%20SQL%20Managed%20Instacnes%20via%20Guest%20Accounts%20via%20Group%20Access%20and%20I%20keep%20getting%3A%26nbsp%3B%3C%2FP%3E%3CP%3ELogin%20failed%20for%20user%20'%3CTOKEN-IDENTIFIED%20principal%3D%22%22%3E'.%20(Microsoft%20SQL%20Server%2C%20Error%3A%2018456)%3C%2FTOKEN-IDENTIFIED%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOnly%20fix%20i%20have%20to%20switch%20to%20Azure%20Data%20Studio%20-%20works%20some%20times%20OR%20switch%20to%20a%20VDI%20-%20works%20normally.%26nbsp%3B%20My%20main%20machine%20fails%20often%20-%20would%26nbsp%3B%20be%20better%20to%20know%20WHICH%20creds%20SSMS%20attempts%20to%20use%20...%20CLI%20%2F%20Powershell%20%2F%20Browser%3F%20Need%20to%20know%20where%20i%20can%20go%20to%20clear%20out%20these%20creds!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2182391%22%20slang%3D%22en-US%22%3ERe%3A%20AAD%20Auth%20Error%20-%20Login%20failed%20for%20user%20'%3CTOKEN-IDENTIFIED%20principal%3D%22%22%3E'%3C%2FTOKEN-IDENTIFIED%3E%3CLINGO-BODY%20id%3D%22lingo-body-2182391%22%20slang%3D%22en-US%22%3E%3CP%3EI%20get%20the%20same%20error%3A%26nbsp%3B%3CSPAN%3ELogin%20failed%20for%20user%20'%3CTOKEN-IDENTIFIED%20principal%3D%22%22%3E'.%20I%20am%20able%20to%20connect%20to%20the%20database%20through%20PowerBI%20since%20PowerBI%20using%20this%20database%20refreshes%20works%20fine%20with%20my%20credential%20from%20the%20same%20machine.%26nbsp%3B%20I%20have%20tried%20to%20download%20the%20latest%20SQL%20Management%20Studio%20and%20it%20does%20not%20help.%20I%20have%20also%20downloaded%20Azure%20Data%20Studio%2C%20and%20I%20run%20into%20the%20same%20error.%20Help%20is%20highly%20appreciated.%20Thank%20you!%3C%2FTOKEN-IDENTIFIED%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2182428%22%20slang%3D%22en-US%22%3ERe%3A%20AAD%20Auth%20Error%20-%20Login%20failed%20for%20user%20'%3CTOKEN-IDENTIFIED%20principal%3D%22%22%3E'%3C%2FTOKEN-IDENTIFIED%3E%3CLINGO-BODY%20id%3D%22lingo-body-2182428%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F985467%22%20target%3D%22_blank%22%3E%40Xiao_Wu%3C%2FA%3E%26nbsp%3B-%26nbsp%3B%3C%2FP%3E%3CP%3Ewhat%20caused%20my%20issue%20was%20the%20user%20was%20dropped%20and%20recreated%2C%20so%20the%20Object%20ID%20in%20Azure%20was%20incorrect.%20This%20is%20what%20caused%20the%26nbsp%3B%3CSPAN%3ELogin%20failed%20for%20user%20'%3CTOKEN-IDENTIFIED%20principal%3D%22%22%3E'%20error%20message%20I%20was%20receiving.%26nbsp%3B%3C%2FTOKEN-IDENTIFIED%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThis%20same%20user%20was%20also%20an%20admin%20on%20the%20sql%20instance%20in%20Azure.%20So%2C%20I%20had%20to%20remove%20that%20user%20as%20an%20admin%20and%20re-add%20that%20same%20user%20as%20the%20admin.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EOnce%20I%20did%20that%2C%20my%20problem%20was%20resolved.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2197796%22%20slang%3D%22en-US%22%3ERe%3A%20AAD%20Auth%20Error%20-%20Login%20failed%20for%20user%20'%3CTOKEN-IDENTIFIED%20principal%3D%22%22%3E'%3C%2FTOKEN-IDENTIFIED%3E%3CLINGO-BODY%20id%3D%22lingo-body-2197796%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F774998%22%20target%3D%22_blank%22%3E%40MrGalvan%3C%2FA%3E%26nbsp%3Bfor%20your%20kind%20response!%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAt%20the%20end%20my%20issue%20was%20due%20to%20the%20default%20DB%20I%20specified%20has%20a%20typo.%20I%20will%20give%20a%20suggestion%20that%20the%20error%20message%20should%20be%20more%20meaningful%20to%20this%20situation%20(such%20as%20the%20database%20you%20specified%20cannot%20be%20found.%20).%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThanks%3C%2FP%3E%0A%3CP%3EXiao%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2197807%22%20slang%3D%22en-US%22%3ERe%3A%20AAD%20Auth%20Error%20-%20Login%20failed%20for%20user%20'%3CTOKEN-IDENTIFIED%20principal%3D%22%22%3E'%3C%2FTOKEN-IDENTIFIED%3E%3CLINGO-BODY%20id%3D%22lingo-body-2197807%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F985467%22%20target%3D%22_blank%22%3E%40Xiao_Wu%3C%2FA%3E%26nbsp%3B%20-%20i%20had%20the%20same%20epiphany%26nbsp%3B%20-%20when%20i%20left%20SSMS%20and%20went%20to%20ADE%20my%20default%20database%20was%20the%20one%20variable%20-%20sure%20enough%20the%20default%20database%20i%20had%20was%20moved%20off%20the%20server%20so%20that%20was%20my%20same%20exact%20issue.%20%2B1%20for%20updating%20that%20error%20message!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E
Version history
Last update:
‎May 26 2020 03:50 AM
Updated by: