SQL Azure DB now supports Azure Active Directory based authentication (preview) and this needs some detailing as the official documentation is very high level. Below I am presenting the flow of this authentication mechanism so that you can create all sorts of permutations and combinations and deduce the behavior yourself for any scenario. I am taking example of a typical ASP.NET based website (say developed using C#) that access SQL Azure DB using Azure AD authentication.
The candidates involved in this scenario are described here:
- Client: Any one who tries to access SQL Azure DB. This could be SQL Management Studio or an ASP.NET web app running on IIS or a REST based Web API.
- SQL DB: The resource that we are trying to access.
- Azure AD: The Security Token Service whose tokens are needed by client to access SQL Azure DB
I will not go into the details of other well documented requirements like setting up contained DB, groups, admins,.NET 4.6 and above based client etc. You can read them here: https://azure.microsoft.com/en-in/documentation/articles/sql-database-aad-authentication/
I am taking a little bit bigger scenario involving both Windows AD and Azure AD and looking at different options at different stages of flow. This will help you comprehend simple scenarios easily.
In above scenario, we will see how a typical web app access Azure SQL DB using different ways.
(1) User is authenticated with web application. User could have authenticated using claims authentication, Kerberos or any other mechanism… it doesn’t matter. One thing in this step must be clear is that user is actually a local AD user. IIS website may or may not be using ADFS for authentication (this too doesn’t matter for our scenario).
Now, before application could use SQL DB, it first need to obtain a valid token from Azure AD. Further steps explain that process.
(2) Before client/app can obtain token from Azure AD, it will obtain token from ADFS which is federated with Azure AD. This is performed using ws-trust protocol. This can be done using one of the below options depending on what the scenario is.
- Option 1: IIS web app can prompt user for his/her credentials, collect credentials and send them to ADFS to obtain a Token (T1). This does not require IIS web server to be domain joined.
- Option 2: If the user was authenticated with IIS web app using claims authentication then web app can use c2wts (impersonation) to authenticate with AD FS and obtain a Token (T1). This requires IIS server to be domain joined.
- Option 3: If the IIS web app is configured to use integrated Windows authentication with impersonation, then it can use Kerberos (constrained delegation) to authenticate with ADFS and obtain a Token (T1). This requires IIS web server to be domain joined (unless we used NTLM and made solution ugly).
- Option 4: IIS web app can use a fixed windows identity to access ADFS (irrespective of who the user is) and obtain a Token (T1) from ADFS. If you hardcode username/pwd then IIS web server doesn’t need to be domain joined, otherwise it would need to be domain joined and app pool running under that fixed AD user’s identity.
(3) No matter which option is used by IIS web app, ultimately IIS web app will get a Token (T1) from ADFS. This token T1 is meant for Azure AD and contains user’s attributes defined by claim rules in ADFS. The user could be either the end user or fixed identity (in case of option 4) depending on which option we choose as described above.
(4) Now, the IIS web app will send T1 to Azure AD. Azure AD will accept this token as there already is a federation between ADFS and Azure AD.
(5) Azure AD will verify the token T1 and look at user’s attributes and then create a new token (T2) for the same user to get another token (T2). This token (T2) will be returned to application.
(6) Then the IIS web app will use token T2 to access Azure SQL DB. Azure SQL DB trusts Azure AD issued tokens and allows access using that user’s identity (user of step 1).
Cloud & Security Consultant