Authenticating in Azure SQL Database using Azure Active Directory B2C

Question

I'm having problems with authenticating to Azure SQL Database through Azure Active Directory.

I am pretty much following the instructions I found here: [ https://azure.microsoft.com/en-us/documentation/articles/sql-database-aad-authentication/ ][Connecting to SQL Database By Using Azure Active Directory Authentication]

I am able to authenticate with Azure Active Directory using localhost and OpenID. I am able to sign up, sign in, and log out. I used "[email protected]" (actual email changed) as the user, and I can get an authorization_code and id_token by signing in. I have also added "[email protected]" as the Active Directory admin of my SQL Database, and added my computer's IP address to the firewall settings.

I have tried to authenticate with "[email protected]" using Microsoft SQL Server Management Studio, but I received this error message:

===================================

Cannot connect to abc.database.windows.net.

===================================

Failed to authenticate the user [email protected] in Active Directory (Authentication=ActiveDirectoryPassword).
Error code 0xCAA90018; state 10
Could not discover a user realm. (.Net SqlClient Data Provider)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=0&LinkId=20476

------------------------------
Server Name: abc.database.windows.net
Error Number: 0
Severity: 11
State: 0
Procedure: ADALGetAccessToken


------------------------------
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)
   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()

Here is my fake Azure setup:

Azure Active Directory B2C
Directory domain: xyz.onmicrosoft.com

Azure SQL Server
Name: abc.database.windows.net
Server version: V12
Number of databases: 1
Database name: def
Dababase pricing tier: S0 Standard

I have also set up the subscription that contains the SQL Database and server to be within the same Active Directory stated above.

I have read some stuff about "contained databases" and "contained database users", and I might need 2 databases: a "master database" and a "user database", but I don't understand all this, especially in the context of Azure SQL Database. Here is one of the links that I read, but don't fully understand:

[ https://msdn.microsoft.com/library/ff929188.aspx ][Contained Database Users - Making Your Database Portable]

I'll post the other links below, since SO won't let me post more than 2 links.

Here are the other links: [ technet.microsoft.com/library/ff929071.aspx ][Contained Databases] [ azure.microsoft.com/en-us/documentation/articles/… ][Azure SQL Database security guidelines and limitations] — Ed Solis
Update I have pictures: [Login attempt 1][ i.stack.imgur.com/qiqUg.png ] [Login attempt 2][ i.stack.imgur.com/8u9oZ.png ] [Error message][ i.stack.imgur.com/MqwJS.png ] The error message using my fake, non-existent information is the same as when I use my actual "Server name" and "Username", so maybe that means something? — Ed Solis

MirekS SQL PM MirekS SQL PM · Accepted Answer · 2016-03-21T17:28:44

As we documented in [ https://azure.microsoft.com/en-us/documentation/articles/sql-database-aad-authentication/ ][Connecting to SQL Database By Using Azure Active Directory Authentication], the MSA accounts and guest accounts are not supported in the current version ( see below). Only native and integrated domain Azure AD accounts are currently supported for Azure SQL DB.

From the doc... (see Azure AD features and limitations)

Microsoft accounts (for example outlook.com, hotmail.com, live.com) or other guest accounts (for example gmail.com, yahoo.com) are not supported. If you can login to https://login.live.com using the account and password, then you are using a Microsoft account which is not supported for Azure AD authentication for Azure SQL Database.

Thanks Mirek

Mirek Sztajno, Senior PM SQL Server security team

Authenticating in Azure SQL Database using Azure Active Directory B2C

2 Answers