SQL Azure Integrated Authentication with a cloud-only Azure Active Directory fails

Question

I have created an Azure tenancy and configured the following:

Azure AD with:

• A simple custom domain name (less than 15 characters). DNS verified etc. All good.
• Users and Admins groups
• Users in both groups
• A VNET and DNS and IP Addresses
• Enabled Device Management
• Enabled Domain Services and connected to the VNET

Note that there is nothing on premise, this is all in the cloud. My physical laptop is effectively being used just as a jump box.

A SQL Azure database and server with:

• Firewall rules open for all necessary incoming connections
• An Active Directory admin set as the Admins group I created in Azure AD
• The AD users all created in SQL Azure using CREATE USER FROM EXTERNAL PROVIDER;

I can connect fine to the SQL Azure database from SSMS on my laptop using either Active Directory Universal Authentication or Active Directory Password Authentication. For both of these I get challenged for the username and password as would be expected.

Objective: I want to be able to use integrated authentication so that can seamlessly flow identity from a) A machine, b) A ASP.NET MVC site. I have not tried Scenario b yes, so let's park that. For scenario a, I have done the following.

Configured an Azure VM:

• Standard D2 - Windows 10 fully patched
• Connected to the same VNET as the domain
• SQL Server Management Server 2016 (SSMS) installed (latest and patched - 13.0.15700.28)
• ODBC 13.1 installed (though I think this is not relevant)
• ADAL
• Microsoft Online Services Sign-In Assistant for IT Professionals RTW

In short, my full "environment" consists of an Azure AD, A SQL Azure DB and a client VM.

Problem: I join the VM to my Azure Active Directory using Directory Services, sign out and log in as a valid domain user (valid in AD and SQL Azure with appropriate logins and permissions). When I open SSMS I can connect fine with Active Directory Universal Authentication or Active Directory Password Authentication but when I try connect with Active Directory Authenticated Security, I get the error below. This also happens if I join the VM directly to Azure AD. My deployment is 100% cloud, so there is no federation in place.

So I have two questions:

• Am I missing something in my configuration or approach or is there a work around? It may be an existing issue - see here
• Would this connectivity (pass through) work if coded in .net 4.6.2 with C# and deployed in the cloud? Possibly with the ODBC 13.1 driver?

Thanks

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

Cannot connect to .database.windows.net.

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

Failed to authenticate the user NT Authority\Anonymous Logon in Active Directory (Authentication=ActiveDirectoryIntegrated). Error code 0xCAA9001F; state 10 Integrated Windows authentication supported only in federation flow. (.Net SqlClient Data Provider)

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

------------------------------ Server Name: .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, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 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()

Answer 1

Got an update from Azure Support:

It's not working because:

1. To use Active Directory Integrated Authentication the Azure Active Directory must be federated. This is because Kerberos is necessary to do it (Kerberos TG ticket that was generated when logging in to the machine), but Azure AD doesn't know Kerberos, hence the need for ADFS.
2. Azure AD Domain Services Preview: This makes legacy features (NTLM, Kerberos, etc) available. But Azure SQL Database doesn’t support the old fashioned Windows Authentication.

So, you could add 2-3 low power VM's to achieve AD + ADFS + AAD + AAD-DS but that's definitely not the ideal way.

If I get information on future plans, I'll share them here.

**

Have the same issue and some open tickets. Will update this answer once I get additional information.

As of today, only a federated setup is supported as documented here. You have to establish an ADFS setup using AD Connect.

That's something AAD-DS should provide for a cloud-only solution. AAD-DS is still preview...

Answer 2

I had the federation flow message until I set the domain account to use for the "Active Directory admin" setting in the Azure Sql Server features screen. Then I was able to connect using SSMS running under this account.

Note: To simplify running SSMS as this other user I used runas: C:\Windows\System32\runas.exe /savecred /user:YourAdAdminUser@YourDomain.com "C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\Ssms.exe"

Running as this user, I connected using the SSMS authentication option, "Active Directory - Integrated". From here I ran the following in the master db:

CREATE USER [YourAdAdminUser@YourDomain.com] FROM EXTERNAL PROVIDER WITH DEFAULT_SCHEMA = dbo

Then I connected to same server in SSMS using local SQL Authentication, logging in with the account set as the "Server admin" for the Azure Sql Server instance. From here I ran alter role commands in master db:

ALTER ROLE dbmanager ADD MEMBER [YourAdAdminUser@YourDomain.com]

ALTER ROLE loginmanager ADD MEMBER [YourAdAdminUser@YourDomain.com]

Now I could go back to the to SSMS running as the AD Admin user and from there I could run CREATE USER commands as above but for other domain accounts:

CREATE USER [OtherAccount@YourDomain.com] FROM EXTERNAL PROVIDER WITH DEFAULT_SCHEMA = dbo

You can decide which database to run the above (e.g., master and your non-system databases).

The domain users can now log in using "Active Directory - Integrated". Note if you add a domain user that is configured for MFA, then for that user to log on using SSMS they should select the SSMS authentication option, "Azure Active Directory - Universal with MFA", and their username should be with an "@" not backslash.