Connect to Azure SQL Database by Using Azure AD Authentication

One of the great features recently added to Azure SQL Database is the ability to authenticate to Azure SQL Database using Azure Active Directory. This provides an alternative to exclusively using SQL credentials. By leveraging Azure AD authentication, you can greatly simplify management of database permissions by continuing to use existing identities, as well as leveraging AD groups.

The article here does a decent job of explaining the basics of how Azure AD authentication with SQL Database works, and the steps needed to do so. One area that it doesn’t yet cover is obtaining an Azure AD authentication token and using that token to authenticate with SQL Database.  Actually  . . . it does, sort of. The article assumes a certificate will be used. That isn’t always the case. In the following sections, I will show you how to obtain an Azure AD authentication token for a user (in Azure AD directory), and use that token for authentication with SQL Database.

Prerequisites

Before we get started, be sure to follow steps 1 through 6 in the Connecting to SQL Database or SQL Data Warehouse By Using Azure Active Directory Authentication article.  I consider these the prerequisite steps. You just have to do it.

Overview

The process to use an Azure AD authentication token with SQL Database can be broken down into several distinct steps:

Let’s review each of these in a bit more detail.

Register an application in Azure AD

For the purposes of this example, let’s keep it simple and use a native (console) application. We’ll use the (new) Azure Portal here. Similar steps can be done in the classic Azure portal as well.

  1. Navigate to the Azure Active Directory section
  2. Select App registrations, and then the + Add button
  3. On the resulting Create blade, provide a friendly name, select an application type of Native, and provide a redirect URL (which is largely irrelevant in this scenario for a native console application)

register-azure-ad-natitve-app

Add Azure SQL Database to the list of APIs which will require permission from your application

This step is very important. You will need to add Azure SQL Database to the list of APIs / applications which will be granted delegated permission via your application. Failure to do this will result in an error message similar to the following:

“AADSTS65001: The user or administrator has not consented to use the application with ID ‘{your-application-id-here}’. Send an interactive authorization request for this user and resource.”

Before you can continue, you need to have followed the prerequisites steps stated at the top of this post. You especially need to be sure you have created an Azure AD contained database user. If you fail to do that, you will not see “Azure SQL Database” in the list (as specified below).

Setting the permission is fairly easy via the Azure portal.

  1. Select the newly created application (in this case, it was ContosoConsole6)
  2. On the Settings blade, select Required permissions.
  3. Add a new required permission and select Azure SQL Database as the API. You’ll want to search for “azure” to get “Azure SQL Database” to appear in the list.

select azure sql database delegated permission.png

Be sure to select the checkbox for “Access Azure SQL DB and Data Warehouse”.

select-azure-sql-database-delegated-permission-2

I should point out that even after adding the delegated permission as shown above, you will still get the previously mentioned error. We’ll solve that next.

Consent to allow your application to access Azure SQL Database

That error you received before about the administrator having not consented to use application is something we need to get past. To do so, you can force a one-time consent dialog so you can consent to your application delegating access to Azure SQL Database. You’ll need to craft a URL in the following format (wrapped for readability):

https://login.microsoftonline.com/%5Byour-tenant%5D.onmicrosoft.com/oauth2/authorize
?client_id=[your-client-application-id]
&response_type=id_token&nonce=1234&scope=openid&prompt=admin_consent

Paste that URL into your favorite browser window and go. You should be prompted to log into your Azure subscription. Do so as a Global Administrator for your Azure AD tenant.

azure-ad-consent-prompt

With the above steps complete, you should now be able to write the code for the sample app to obtain and use the Azure AD authentication token with Azure SQL Database.

Create a .NET 4.6 console application

Make sure your console project targets .NET Framework 4.6. The SqlConnection.AccessToken property used to set the Azure AD authentication token is available in .NET 4.6 only.

new-console-app

Add the Active Directory Authentication Library (ADAL) to the project via NuGet

Since you’ll be working with Azure AD, you’ll want to use ADAL to make getting the Azure AD authentication token easy.

Add ADAL Nuget.png

Add code to obtain an Azure AD authentication token

Finally! Some code! First, in my example, I set up a few constants which represent information about Azure AD and the resource for which I want to obtain an authentication token. For SQL Database, the resource is https://database.windows.net/.

private const string AadInstance = "https://login.windows.net/{0}";
private const string ResourceId = "https://database.windows.net/";

You’ll need a client ID as part of the call to AcquireTokenAsync(). The client ID is the Application ID for your app registered in Azure AD.  The Azure AD tenant value is the friendly name for your Azure AD tenant (e.g. contoso.onmicrosoft.com). Thanks to using ADAL, the code to get the authentication token is very easy – just two lines of code:

string clientId = ConfigurationManager.AppSettings["ClientId"];
string aadTenantId = ConfigurationManager.AppSettings["Tenant"];
AuthenticationContext authenticationContext =
  new AuthenticationContext(string.Format(AadInstance, AadTenantId));

AuthenticationResult authenticationResult =
  authenticationContext.AcquireTokenAsync(ResourceId,
                                          clientId,
                                          GetUserCredential()).Result;

You’ll notice the above code calls a method GetUserCredential()  to obtain a UserCredential object. This represents the Azure AD user for which to obtain the authentication token. In this example, the user details are hard coded. Yes, this is bad. I’m showing it here strictly as an example. In future posts, I’ll show a few other ways to (better) handle the user details.

private static UserCredential GetUserCredential()
{
   string pwd = ConfigurationManager.AppSettings["UserPassword"];
   string userId = ConfigurationManager.AppSettings["UserId"];

   SecureString securePassword = new SecureString();

   foreach (char c in pwd) { securePassword.AppendChar(c); }
   securePassword.MakeReadOnly();

   var userCredential = new UserPasswordCredential(userId, securePassword);

   return userCredential;
}

If all goes well, after executing the AcquireTokenAsync() method you should receive an  Azure AD (JWT) authentication token as part of the resulting AuthenticationResult object. For fun, paste it in at https://jwt.io to decode it. You should get something similar to the screenshot below.

jwt-screenshot

Add code which uses Azure AD authentication token to authenticate with SQL Database

This is the easy part. You just need some code which gets a basic database connection string, and then sets the SQL connection to use the previously obtained authentication token.

The database connection string is going to be very basic, containing nothing more than the data source (your Azure SQL Database server name), the database name, and a connection timeout.

Data Source=[your-server-name-here].database.windows.net;
Initial Catalog=[your-db-name-here];Connect Timeout=30

The following code to query the database is ultra-simple. I just want to make sure the connection is successful and I can execute a basic command. This command will return the name of the connected user . . . which should correspond to that of the specified Azure AD user.

var sqlConnectionString = ConfigurationManager.ConnectionStrings["MyDatabase"].ConnectionString;
using (SqlConnection conn = new SqlConnection(sqlConnectionString))
{
  conn.AccessToken = authenticationResult.AccessToken;
  conn.Open();

  using (SqlCommand cmd = new SqlCommand("SELECT SUSER_SNAME()", conn))
  {
    var result = cmd.ExecuteScalar();
    Console.WriteLine(result);
  }
}

That’s it. Done.

Summary

As you can hopefully see, the steps to using an Azure AD authentication token with Azure SQL Database are not especially complicated. There are a few minor hoops to jump through, but nothing too serious.

It is worth pointing out that this solution does take a dependency on Azure AD. In the unlikely event that Azure AD is unavailable, then you may be unable to access your database resources using your Azure AD credential. I think it is a wise strategy to also keep a few SQL identities in place and have a process ready to use those  – just in case. After all, designing robust applications for the cloud is about designing for failure. Always be prepared.

You can find the full source code for this project on my personal GitHub repository.

Advertisements