%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
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

 

11 Comments
Occasional Visitor

What about when you're the server's "Active Directory Admin". i don't have a user created on each database, but i should be able to log in regardless in order to be able to grant access to other AAD Principals...

Microsoft

@JuanParodi in this case (AAD Server Admin) yes, you should be able to login to ANY database. If getting error being AAD Server Admin we would need to investigate further and you can open a case on that

 

New Contributor

Any progress on this issue?

Microsoft

@Edvard Gundersen this is not an issue. This is an expected behavior... When you create contained users, in this case was AAD user, but could be SQL login (contained user). You are only able to connect to the specified DB, if you want to connect to master db or to list the available dbs (master metadata) you need to have this user created also on master.

- If user is application user, its not needed any permission on master. And application connection should specify database name

- If user is a SSMS user and if you do not want to always specify the database name you can create user on master DB to have a more simple navigation on SSMS. You do not need any special permission on master, just create user on master should be enough

 

Traditional model Contained database user model
When connected to the master database:

CREATE LOGIN login_name WITH PASSWORD = 'strong_password';

Then when connected to a user database:

CREATE USER 'user_name' FOR LOGIN 'login_name';
When connected to a user database:

CREATE USER user_name WITH PASSWORD = 'strong_password';
Visitor

I'm running into a similar issue with being able to deploy a NEW database onto an Azure SQL Server using token-based authentication from an Azure VM.  I've added the Azure VM to the database server's firewall, I've ensured that the Azure VM has a system-assigned identity.  When I publish the DACPAC to an existing Azure SQL database, there is no problem.  When I try to use the same DACPAC to create a NEW database by simply changing the database name, I get the error message 

 

Login failed foruser '<token-identified principal>'

 

I can't create the contained user before the database is created, obviously.

New Contributor

You are probably using a guest invite for someone who has access to multiple Azure tenants. If that's the case, they need to specify the tenant ID as well in the SSMS connection options at the bottom.

 

The tenant ID is xxxx.onmicrosoft.com (the tenant containing the SQL DB)

New Contributor

Hi all,

  I'm having an issue when trying to connect an Active Directory user that is the Active Directory Admin over this sql server. 

 

I have been connecting to this database in the past w/out any issues through Management Studio. 

 

However, all of the sudden I am getting this error: login failed for user '<token-identified principal>'

 

Again, the user I'm using is the Active Directory Admin over this instance along w/ the databases within this instance in our Azure environment. 

 

Also, I have tried just connecting to any other database other than the default (Master) db with no success.

 

Does anyone know why it's all the sudden doing this and what the fix is?

Microsoft

@MrGalvan in this case as you said this is Server Admin this issue should not happen. Could be something else. I suggest you to open a case to further investigated it

New Contributor

Found the issue and have resolved it. 

 

What happened is the AD group that was assigned as the Active Directory Admin was dropped and created w/ the same name. However, the old group's Object Id wasn't the same as the newly created AD group's Object Id. 

 

Only found this out by looking at the Creation Date of the Active Directory Group. Which shows it was a recent creation date. A time frame within where the issue started happening.

 

So, it appears that the token assigned to the old group was still saved, but that Object Id no logger existed since the group was re-created w/ a new Object Id. 

 

Remedy: Just dropped AD admin and re-added it and now we're back in business. 

Microsoft

@MrGalvan tks for the feedback and for adding the solution to this article. This may help others too

Regular Visitor

You are probably using a guest invite for someone who has access to multiple Azure tenants. If that's the case, they need to specify the tenant ID as well in the SSMS connection options at the bottom.

 

The tenant ID is xxxx.onmicrosoft.com (the tenant containing the SQL DB)

 

This is interestingly not true in the 18.x versions of SSMS 

If you are running SSMS 18.x or later, the AD domain name or tenant ID is no longer needed for guest users because 18.x or later automatically recognizes it.

Funny enough - i login to SQL Managed Instacnes via Guest Accounts via Group Access and I keep getting: 

Login failed for user '<token-identified principal>'. (Microsoft SQL Server, Error: 18456)

 

Only fix i have to switch to Azure Data Studio - works some times OR switch to a VDI - works normally.  My main machine fails often - would  be better to know WHICH creds SSMS attempts to use ... CLI / Powershell / Browser? Need to know where i can go to clear out these creds!