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, TaskCompletionSource
1 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()
