Azure SQL authentication with a Managed Service Identity

On a previous article I discussed how to use a certificate stored in Key Vault to provide authentication to Azure Active Directory from a Web Application deployed in AppService so that we could authenticate to an Azure SQL database.

With the introduction of Managed Service Identity, this becomes even easier, as we can just get rid of the complexity of deploying the Key Vault certificate.

Let’s see how we could use MSI to authenticate the application to a SQL Database.

Enabling Managed Service Identity

The first step is creating the necessary Azure resources for this post. As usual, I’ll use Azure Resource Manager (ARM) templates for this. I’ll create a new SQL Server, SQL Database, and a new Web Application.

The only difference here is we’ll ask Azure to create and assign a service principal to our Web Application resource:

{
    "name": "[parameters('webAppName')]",
    "type": "Microsoft.Web/sites",
    "location": "[resourceGroup().location]",
    "apiVersion": "2015-08-01",
    "dependsOn": [
        "[resourceId('Microsoft.Web/serverfarms', parameters('webAppPlanName'))]"
    ],
    "tags": {
        "[concat('hidden-related:', resourceId('Microsoft.Web/serverfarms', parameters('webAppPlanName')))]": "Resource",
        "displayName": "Web Application"
    },
    "identity": {
        "type": "SystemAssigned"
    },
    "properties": {
        "name": "[parameters('webAppName')]",
        "serverFarmId": "[resourceId('Microsoft.Web/serverfarms', parameters('webAppPlanName'))]",
        "siteConfig": {
            "connectionStrings": [
                {
                    "name": "SqlDb",
                    "connectionString": "[concat('Data Source=tcp:', parameters('sqlServerName'), '.database.windows.net,1433; Initial Catalog=', parameters('sqlDbName'))]"
                }
            ]
        }
    },
    "resources": [
        {
            "name": "appsettings",
            "type": "config",
            "apiVersion": "2015-08-01",
            "dependsOn": [
                "[resourceId('Microsoft.Web/sites', parameters('webAppName'))]"
            ],
            "properties": {
                "AAD_TENANT_ID": "[subscription().tenantId]"
            }
        }
    ]
}

The key bit in the template above is this fragment:

"identity": {
    "type": "SystemAssigned"
},

Note: You can also enable MSI from the Azure Portal for an existing Web App.

Once the web application resource has been created, we can query the identity information from the resource:

az resource show -n $webApp -g $resourceGroup --resource-type Microsoft.Web/sites --query identity

We should see something like this as output:

{
  "principalId": "f76495ad-d682-xxxx-xxxx-bc70710ebf0e",
  "tenantId": "8305b292-c023-xxxx-xxxx-a042eb5bceb5",
  "type": null
}

With the principalId, we can query AAD to get the full details of the principal, using the az ad sp show --id $principalId, which should print something like this:

{
  "appId": "09b89d60-1c0f-xxxx-xxxx-e009833f478f",
  "displayName": "msitr2app",
  "objectId": "f76495ad-d682-xxxx-xxxx-bc70710ebf0e",
  "objectType": "ServicePrincipal",
  "servicePrincipalNames": [
    "09b89d60-1c0f-xxxx-xxxx-e009833f478f",
    "https://identity.azure.net/R1arAxq7+EKpM2wyumvvaZ0n+9ICN6YkZB/sse/1VtI="
  ]
}

Note: remember that to use AAD users in SQL Azure, the SQL Server should have an AAD administrator, which the template provider does.

Creating SQL Users

Azure SQL Database does not support creating logins or users from servince principals created from Managed Service Identity. The only way to provide access to one is to add it to an AAD group, and then grant access to the group to the database.

We can use the Azure CLI to create the group and add our MSI to it:

az ad group create --display-name SQLUsers --mail-nickname 'NotSet'
az ad group member add -g SQLUsers --member-id f76495ad-d682-xxxx-xxxx-bc70710ebf0e

Notice that in the second command, we’re passing the objectId or principalId value, rather than the application id.

Now, I can grant access to the group using the same script we’ve used in the previous posts:

.\Add-SqlAadUser.ps1 -sqlServer $serverName -database $dbName -sqlAdminCredentials $cred -aadUser SQLUsers

Authenticating to the Database

To obtain a token for our Azure SQL database, I’ll use the Microsoft.Azure.Services.AppAuthentication library:

public static class ADAuthentication
{
    const String SqlResource = "https://database.windows.net/";

    public static Task<String> GetSqlTokenAsync()
    {
        var provider = new AzureServiceTokenProvider();
        return provider.GetAccessTokenAsync(SqlResource);
    }
}

Then we can use the token to authenticate to SQL and obtain the username, to ensure we are indeed connecting with our Managed Service Identity:

public async Task<ActionResult> UsingSP()
{
    String cs = ConfigurationManager.ConnectionStrings["SqlDb"].ConnectionString;

    var token = await ADAuthentication.GetSqlTokenAsync();

    var user = await GetSqlUserName(cs, token);
    ViewBag.SqlUserName = user;
    return View("UserContext");
}

private async Task<String> GetSqlUserName(String connectionString, String token)
{
    using (var conn = new SqlConnection(connectionString))
    {
        conn.AccessToken = token;
        await conn.OpenAsync();
        String text = "SELECT SUSER_SNAME()"; 
        using (var cmd = new SqlCommand(text, conn))
        {
            var result = await cmd.ExecuteScalarAsync();
            return result as String;
        }
    }
}

The value of SUSER_SNAME() should come back something like this: 09b89d60-1c0f-xxxx-xxxx-e009833f478f@8305b292-c023-xxxx-xxxx-a042eb5bceb5. Notice that what we get back as the name is based on the applicationId of the service principal.

Final Thoughts

Managed Service Identity makes it a lot simpler and more secure to access other Azure resources from your Web Applications deployed to App Service. Notice, however, than in its current form it will not support scenarios such as credential delegation, but we may see support for this added in the future.

Creating an Event Hub destination using Event Grid in ARM

In a previous post, I presented a few ways to create Azure Event Grid resources using Azure Resource Manager (ARM) templates.

Logic Apps KeyVault Connector - Part 1

Azure Logic Apps now supports writing custom connectors, which are just custom REST APIs for which you can customize the experience so that they feel like the built-in Logic Apps connectors. I wanted to give try my hand at writing one, so decided on a simple use case: Writing a connector that provides a way to retrieve a secret stored in Azure Key Vault.

Azure Managed Service Identity - Querying in ARM Template

In a previous post I was lamenting not having a way to obtained the managed service identity generated for an Azure resource, such as a Azure SQL logical server or a Web App from the Azure Resource Manager (ARM) template itself.

The issue was that the reference() function in an ARM template only returns the properties part of the resource definition, and the identity property is defined outside of that (at the same level as the resource id or the location).

It is now possible to do this thanks to a new parameter introduced in the reference() function in ARM. The new definition is:

reference(resourceName | resourceIdentifier, [apiVersion], ['Full'])

Notice the new, optional 'Full' parameter. When this is specified, the reference() function returns the complete resource definition, and not just the properties section. So we can obtain the generated identity easily using something like this:

{
    "outputs": {
        "sqlIdentity": {
            "type":"string",
            "value": "[reference(concat('Microsoft.Sql/servers/', parameters('sqlServerName')), '2015-05-01-preview', 'Full').identity.principalId]"
        }
    }
}

Unable to locate registry entry for adalsql.dll file path

A couple of days ago I was testing with a customer using Azure Active Directory integrated authentication to Azure SQL Database through the SQL Server ODBC drivers.

On one test machine, we kept getting an error similar to this:

Microsoft ODBC Driver 13 for SQL Server : SQL Server Network Interfaces: Unable to locate the registry entry for adalsql.dll file path. Verify that Active Directory Authentication Library for SQL Server is properly installed.

We checked, and adalsql.dll was present in both C:\Windows\System32 and C:\Windows\SysWOW64, as expected. We also tried downloading the standalone library installer but that would not install, since the library was already in the machine.

Looking around, I realized the problem was not that the library was missing, but that it somehow got installed without it getting registered in the Windows Registry correctly.

To fix this, we created the following registry keys:

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSADALSQL]
"TargetDir"="C:\\WINDOWS\\system32\\adalsql.dll"

[HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\MSADALSQL]
"TargetDir"="C:\\WINDOWS\\system32\\adalsql.dll"

If your system drive is anything other than C:, replace the path accordingly.

After we added these missing keys, the error went away and authentication worked correctly.

Permissions needed to create a Web App on an ASE

Documenting this here in case I run into this again. While working with a customer that is taking advantage of App Service Environment to host internal applications on Azure with connectivity to their Express Route connection, we ran into an issue when trying to setup permissions so that selected users could create new Web Apps on an existing App Service Plan on the ASE.

For App Service Environment v1, it appears the easiest way to setup the right permissions are:

  • Grant the Reader role on the App Service Environment itself.
  • Grant the Web Plan Contributor role on the App Service Plan.
  • Grant the Web Site Contributor role on the resource group you will create the Web App on.

I was a little concerned about granting the Web Plan Contributor role as it grants way too many permissions on the App Service Plan (ASP), but on v1, this is not so much of an issue as there is no direct monetary impact from the ASP.

I did try setting up a custom role instead, and the least permissions I was able to test that still worked were:

Microsoft.Authorization/*/read
Microsoft.Insights/alertRules/*
Microsoft.Insights/components/*
Microsoft.ResourceHealth/availabilityStatuses/read
Microsoft.Resources/deployments/*
Microsoft.Resources/subscriptions/resourceGroups/read
Microsoft.Support/*
Microsoft.Web/certificates/*
Microsoft.Web/listSitesAssignedToHostName/read
Microsoft.Web/deploymentLocations/read
Microsoft.Web/serverFarms/read
Microsoft.Web/serverFarms/*/read
Microsoft.Web/serverFarms/write
Microsoft.Web/sites/*

This is not much of an improvement, since it appears you still require write permissions on the App Service Plan, but it was still interesting to test.

This has changed for App Service Environment v2, so I can’t comment on that.

Azure Managed Service Identity Library

A few days ago, the preview of Managed Service Identity for Azure was released, opening up some interesting possibilies to access other Azure resources from your application in a secure manner.

App Service is one of the services adding support for managed service identity, including a nice library to make it easier to use.

You can find some documentation for the Microsoft.Azure.Services.AppAuthentication library on the sample here.

I spent some time today looking at it, and would like to share some thoughts about it.

The public surface of the library is fairly simple, and it is easy to use for the purpose it was built. When used on App Service, it is very straightforward to use: It will automatically pick up the MSI_ENDPOINT and MSI_SECRET environment variables and get the token for the resource you specify.

While testing, noticed that the MSI_ENDPOINT variable pointed to http://127.0.0.1:[port]/MSI/token/, where the [port] seems to be dynamically assigned for each app.

One interesting question that came up was how to support developing and debugging the application on your local dev workstation when using this library, and it is supported. The basis of this is that the library can be configured to use a mechanism other than MSI to generate the token.

You can modify the default behavior either by explicitly passing a connection string to the AzureServiceTokenProvider constructor, or by storing it in the AzureServicesAuthConnectionString environment variable.

There are several methods supported of authentication supported:

Service Principal Id + Secret:

To use this method, set the connection string to something like:

RunAs=App; TenantId=<tenant>; AppId=<principalId>; AppKey=<secret>

While this is probably the easier method to implement, it’s not very secure, as it will require you to have your service principal credentials in plain text somewhere. So it should not be the preferred method for development.

Service Principal Id + X509 Certificate:

To use this method, set the connection string to something like:

RunAs=App; TenantId=<tenant>; AppId=<principalId>; CertificateStoreLocation=[CurrentUser | LocalMachine]; [CertificateThumbprint=<thumbprint> | CertificateSubjectName=<cn>]

This adds a little bit of complexity to the process. You now need to:

  • Install the authentication certificate on the certificate store
  • Redeploy when the certificate expires

It is somewhat more secure than using application keys as you don’t need to have credentials in plain text. To mitigate the risk of losing control of the certificate, you could use a different Service Principal per team member, but it adds quite a bit of work to the process.

Your Azure CLI credentials:

This is an interesting alternative, based on using whatever credentials you’re using on the Azure CLI to access your Azure subscription. To use this method, set the connection string to:

RunAs=Developer; DeveloperTool=AzureCLI

Internally, this will call az account get-token to acquire the token for the specified resource, using the credentials (access token) stored by the Azure CLI for your subscription

This has the advantage of not having to store more credentials and no plain text, but it’s only as secure as your Azure CLI token.

When I first looked into this, I thought one disadvantage would be that you’d end up accessing the resources under your own account, but then I remembered that the Azure CLI does allow you to sign on using a service principal, so this is not really an issue.

There is, however, one disadvantages I can see:

The Azure CLI allows you to have multiple tokens to different accounts stored. So which one is used by this library will depend on what your active subscription is (i.e. whatever you configured with az account set). That can easily cause your application to break if you change the active account and forget to set it back to the correct one later on.

This part makes me a bit nervous, since it makes it somewhat unpredictable.

Your Azure AD Account

This is another interesting alternative that will work in cases where you are logged into your workstation using a domain account on a domain that is synchronized with Azure Active Directory. You can enable this with the following connection string:

RunAs=CurrentUser

The obvious advantage is that it doesn’t need additional credentials at all, since the library will simply take advantage of your domain credentials to obtain the token to the requested resource.

The big downside is that the applicating will be accessing resources using your own identity, rather than a service principal representing the application.

Final Thoughts

Remember that this is based on an initial preview of the library, and things could change. Maybe some of these methods will disappear or more will be added.

Overall, I was pleasantly surprised by the ease of use of the library. Having considered alternatives allowing you to keep your code simple while still supporting developing and running your application on your local workstation is a very welcome addition.

That said, every method has some advantages and disadvantages to be aware of; just make sure to choose what works for you and meets your security requirements.

I need to think a bit more to decide what I’d consider the best option. The simplest is using your Azure AD account, which will likely work well for isolated developer environments (such as your own MSDN subscription), but it might not be a good choice for other environments.

Deploying a Key Vault-based TDE protector for Azure SQL

Azure SQL now supports setting up Transparent Data Encryption while bringing your key encryption keys. This is easy to setup in the Azure Portal, but I wanted to try setting this up in an automated manner, preferably leveraging ARM templates as much as possible.

This turned out to be a bit more complex than I expected, but totally doable. Here are the necessary steps:

The ARM templates for this post can be found on GitHub.

Creating the Key

When using the BYOK (bring your own key) feature, you need to store your encryption key in an Azure Key Vault. So let’s first create one:

  • In the Azure Portal, locate your Key Vault
  • Click on the Keys icon
  • Click the + Add button
  • Select the Generate option, the name, and other required properties
  • Click the Create button to complete the process

Creating encryption key

You could do the same in a script with an Azure CLI command like the following:

az keyvault key create --vault-name $vaultName --name $keyName --size 2048 --protection software

Once you’ve created the key, make sure to grab the name of the current version of the key. You can do this in the portal, or by using the command line:

az keyvault key show --vault-name $vaultName --name $keyName --query key.kid

The URI returned will be something similar to https://<vault_name>.vault.azure.net/keys/<key_name>/<key_version>. We’re interested in the last segment.

Deploying the Server

The next step is to use an ARM template to deploy the Azure SQL Server resource. At this point, we are not actually creating the TDE protector, as we need to do something else first.

Here’s the core of the template to create the server:

{
    "name": "[parameters('sqlServerName')]",
    "type": "Microsoft.Sql/servers",
    "location": "[resourceGroup().location]",
    "apiVersion": "2015-05-01-preview",
    "dependsOn": [],
    "tags": {
        "displayName": "SQL Logical Server"
    },
    "identity": {
        "type": "SystemAssigned"
    },
    "properties": {
        "administratorLogin": "[parameters('sqlServerAdminLogin')]",
        "administratorLoginPassword": "[parameters('sqlServerAdminLoginPassword')]"
    }
}

The interesting bit here is the identity property. When we set it to SystemAssigned, SQL will go to the Azure Active Directory Tenant associated with the Azure Subscription and create a new Service Principal named RN_<serverName>.

This principal will be setup with X509 certificate credentials for authentication. I’m unsure at this point if it’s possible to create this Service Principal manually, as it would simplify things somewhat.

Once the server is created, we’ll see this identity reflected in the resource properties:

SQL Service Principal

While we’re at it, let’s create a new database on the server, and enable TDE. The latter is done by creating a nested resource of type transparentDataEncryption:

{
    "name": "[parameters('sqlDbName')]",
    "type": "databases",
    "location": "[resourceGroup().location]",
    "apiVersion": "2014-04-01-preview",
    "dependsOn": [
        "[resourceId('Microsoft.Sql/servers', parameters('sqlServerName'))]"
    ],
    "tags": {
        "displayName": "SQL DB"
    },
    "properties": {
        "collation": "[parameters('sqlDbCollation')]",
        "edition": "[parameters('sqlDbEdition')]",
        "maxSizeBytes": "1073741824",
        "requestedServiceObjectiveName": "[parameters('sqlDbRequestedServiceObjectiveName')]"
    },
    "resources": [
        {
            "comments": "Transparent Data Encryption",
            "name": "current",
            "type": "transparentDataEncryption",
            "apiVersion": "2014-04-01-preview",
            "properties": {
                "status": "Enabled"
            },
            "dependsOn": [
                "[parameters('sqlDbName')]"
            ]
        }
    ]
}

At this point, TDE will be enabled on the new Database using the default Server Managed encryption key.

Granting Key Permissions

Once the new Azure SQL Server has an identity in Azure Active Directory, we need to grant permissions to that entity to fetch our encryption key from Key Vault. We can use a command like the following to grant it permissions to get, wrap, and unwrap our encryption key:

objectId=`az sql server show --resource-group $rg --name $sqlserver --query identity.principalId | sed -e 's/^"//' -e 's/"$//'`
az keyvault set-policy --name $vaultName --object-id $objectId --key-permissions get wrapKey unwrapKey

Creating the Protector

Now we can go back to ARM, and create the TDE protector. For this, we need to create 2 separate resources:

Server Key

First, let’s create a new server key from the Key Vault. All we need for this is the name of the server, the key vault, the key name and version. From the last 3, we need to compute a couple of things:

{
    "variables": {
        "serverKeyName": "[concat(parameters('keyVaultName'), '_', parameters('keyName'), '_', parameters('keyVersion'))]",
        "keyUri": "[concat('https://', parameters('keyVaultName'), '.vault.azure.net/keys/', parameters('keyName'), '/', parameters('keyVersion'))]"
    }
}

The serverKeyName variable contains the name of the server key we’ll create; this needs to be in the specific format vaultName_keyName_keyVersion. The keyUri variable is just to avoid having to add an extra parameter to the template with duplicated information.

Now we can create a keys resource, like this:

{
    "name": "[concat(parameters('sqlServerName'), '/', variables('serverKeyName'))]",
    "type": "Microsoft.Sql/servers/keys",
    "apiVersion": "2015-05-01-preview",
    "dependsOn": [
        "[parameters('sqlServerName')]"
    ],
    "properties": {
        "serverKeyType": "AzureKeyVault",
        "uri": "[variables('keyUri')]"
    }
}

All we really need to specify here is the key type (AzureKeyVault) and the URI of the key in Key Vault.

Rotating the Protector

Now we can rotate the protector by setting the “current” encryptionProtector resource on the server to the new server key.

{
    "name": "[concat(parameters('sqlServerName'), '/current')]",
    "type": "Microsoft.Sql/servers/encryptionProtector",
    "kind": "azurekeyvault",
    "apiVersion": "2015-05-01-preview",
    "dependsOn": [
        "[resourceId('Microsoft.SQL/servers/keys', parameters('sqlServerName'), variables('serverKeyName'))]"

    ],
    "properties": {
        "serverKeyName": "[variables('serverKeyName')]",
        "serverKeyType": "AzureKeyVault"
    }
}

If we go into the portal and check the TDE settings for the SQL Server, we should see it is indeed configured to use Key Vault at this point:

TDE with Key Vault

Authenticating to SQL Azure with delegated tokens

In a previous post, I discussed how to authenticate to an Azure SQL database from a Web Application (running in Azure App Service) using an Azure Active Directory Service Principal. For this I used a certificate stored in Key Vault to authenticate the principal and obtain a token I could present to SQL.

You can find the updated code for this post on GitHub.

In this post, let’s expand this a bit further: I will add authentication support to the Web Application by federating through OpenId Connect to Azure Active Directory, and then delegate the user credentials all the way to the database.

Configure new users in SQL

Since I want to allow other Azure AD users to connect to the database, I need to grant them permissions to it.

To do this, I just follow the steps we used in my previous post, and use the T-SQL commands:

  • Create the user: CREATE USER [user@tenant] FROM EXTERNAL PROVIDER
  • Grant permissions: ALTER ROLE

Setting up the AD Application

So far, I’ve been using an Azure Active Directory Service Principal to authenticate to SQL. In order to be able to setup OpenId Connect on our Web App, I also need an Azure AD application to go with it.

So what I want is to configure my existing Service Principal so that I can use it for this purpose as well.

Note: using a separate AD application registration won’t work because doing the On_Behalf_Of delegation will fail.

To do this, I’m going to open the Azure AD portal and find the Service Principal in the App Registrations section of the portal. Here, I open the Reply URLs page and add the application host (i.e. https://<app>.azurewebsites.net/):

Set Reply URL

Granting Sign-In permission

For users to be able to sign-in, I need to give the application the right permissions. I do this by opening the Required permissions page, and clicking on the + Add button.

Here, I select Windows Azure Active Directory (Microsoft.Azure.ActiveDirectory) as the API, and check the “Sign in and read user profile” option under “Delegated Permissions”:

Grant Sign in permissions

Then click save and confirm all changes.

Granting Access to SQL

There is one other permission that I need to grant to the application, so that we can delegate credentials to SQL Server. Again, click on the + Add button, and select Azure SQL Database as the API. Then, check the “Access Azure SQL DB and Data Warehouse” option under the “Delegated Permissions” section:

Grant SQL Permissions

Then save all the changes.

As a final step, I locate the Application Id property in the application properties; we’ll need this value in a moment.

Enabling Authentication

Now I can enable federated authentication. I could do this by leveraging the Easy Authentication feature in App Service, but since I’ll need to change the application code later on, I’ll do this in code just as easy.

First, let’s add references to the following NuGet packages into the project:

  • Microsoft.Owin.Host.SystemWeb
  • Microsoft.Owin.Security.OpenIdConnect
  • Microsoft.Owin.Security.Cookies

Now I can write our startup code to enable authentication:

using Microsoft.Owin.Security;
using Microsoft.Owin.Security.Cookies;
using Microsoft.Owin.Security.OpenIdConnect;
using Owin;
using System;
using System.Configuration;

namespace UsingTokenAuthApp
{
    public class AuthConfig
    {
        public static void RegisterAuth(IAppBuilder app)
        {
            var clientId = ConfigurationManager.AppSettings["APP_CLIENT_ID"];
            var tenant = ConfigurationManager.AppSettings["AAD_TENANT_ID"];

            var authority = $"https://login.microsoftonline.com/{tenant}";

            app.SetDefaultSignInAsAuthenticationType(CookieAuthenticationDefaults.AuthenticationType);
            app.UseCookieAuthentication(new CookieAuthenticationOptions());

            var options = new OpenIdConnectAuthenticationOptions
            {
                Authority = authority,
                ClientId = clientId,
            };
            app.UseOpenIdConnectAuthentication(options);
        }
    }
}

This is obviously a very minimal implementation, but enough for my needs.

Note: Configure the APP_CLIENT_ID value in appSettings to the Application Id value I copied from the Service Principal properties in the previous step.

I also want to force every request to be authenticated, so I’ll add a global AuthorizeAttribute filter:

public class FilterConfig
{
    public static void RegisterGlobalFilters(GlobalFilterCollection filters)
    {
        filters.Add(new AuthorizeAttribute());
    }
}

Obtaining delegated Credentials

Now that the user can be authenticate by the application, I want to change how the access token is obtained.

I’m going to create a separate method for this:

public static async Task<String> GetDelegatedTokenAsync(ClaimsPrincipal user)
{
    var context = new AuthenticationContext(Authority);
    var certificate = FindCertificate(CertSubjectName);
    if (certificate == null)
        throw new InvalidOperationException("Could not load certificate");

    var id = (ClaimsIdentity)user.Identity;
    var bootstrap = id.BootstrapContext as BootstrapContext;
    var credential = new ClientAssertionCertificate($"http://{ServicePrincipalName}", certificate);

    var userAssertion = new UserAssertion(bootstrap.Token);
    var result = await context.AcquireTokenAsync(SqlResource, credential, userAssertion);
    return result.AccessToken;
}

The code is very similar to the original, except I obtain the JWT token of the current user and use that to create a UserAssertion object that I provide to AcquireTokenAsync.

Note: This is not production-ready code. Besides needing additional error handling, we also need to cache the access tokens by providing a proper TokenCache implemenation. Some discussion on this can be found here.

By default, ClaimsIdentity.BootstrapContext will be null. To fix this, I need to modify the Web.config file to tell the identify framework to save the JWT token of the user during login:

<system.identityModel>
    <identityConfiguration saveBootstrapContext="true" />
</system.identityModel>

Now let’s put this code to use:

public async Task<ActionResult> UsingDelegation()
{
    var user = this.User as ClaimsPrincipal;
    var token = await ADAuthentication.GetDelegatedTokenAsync(user);

    String cs = ConfigurationManager.ConnectionStrings["SqlDb"].ConnectionString;
    var sqlUser = await GetSqlUserName(cs, token);

    ViewBag.SqlUserName = sqlUser;
    return View("DelegatedContext");
}

Testing the application

After deploying all the changes, I am ready to test this. I navigate to the application’s home page (https://<app>.azurewebsites.net/) and I should be redirected to the Azure AD login page. Here I provide credentials for a valid user.

Once logged in, I navigate to the Credential Delegation page (/home/UsingDelegation).

If you’re trying this yourself, you might get an error similar to:

{
    "error":"invalid_grant",
    "error_description":"AADSTS65001: The user or administrator has not consented to use the application with ID 'http://TestSpWithCert'. Send an interactive authorization request for this user and resource.\r\nTrace ID: 48285e18-5e24-48a3-98b3-93027d392700\r\nCorrelation ID: 08cd347f-117e-412d-9a63-b2fc533fef22\r\nTimestamp: 2017-08-31 18:32:48Z",
    "error_codes":[65001],
    "timestamp":"2017-08-31 18:32:48Z",
    "trace_id":"48285e18-5e24-48a3-98b3-93027d392700","correlation_id":"08cd347f-117e-412d-9a63-b2fc533fef22"
}

This means you forgot to grant permissions to your application to access SQL Server in Azure AD.

If everything was setup correctly, however, I should see my user credentials delegated to SQL server without problems:

User delegated to SQL

Conclusion

Doing credential delegation to Azure SQL DB through Azure Active Directory opens up interesting options, such as taking advantage of row-based security or better use of Data Masking in SQL.

Hope you found this series of articles useful!

Token authentication to SQL Azure with a Key Vault Certificate

In a previous post, I presented a PowerShell script to create a new Service Principal in Azure Active Directory, using a self-signed certificate generated directly in Azure Key Vault for authentication.

Now, let’s try using it for somethig useful. All the code and samples for this article can be found on GitHub.

We can use the Key Vault certificate in a Web Application deployed to Azure App Service to authenticate to Azure Active Directory using our Service Principal, and then obtain a token to connect to SQL Azure. This saves us from having to store passwords anywhere in our configuration, since Key Vault and App Service will provide us with easy, secure access to our authentication certificate.

In order to do this, we need a few things:

  • Our Service Principal identity in AAD, with the Key Vault certificate
  • A SQL Azure Database
  • A SQL Server (Azure) login based on our AAD Service Principal, with permissions on the database in question.
  • A Web App deployed with our Key Vault certificate
  • An ASP.NET App that will use the certificate to authenticate to AAD, then use the token to connect to SQL.

Let’s get started!

Creating the Database

For this sample, I’m going to create a new Azure SQL Server logical server, then deploy a new, blank database on it. We’ll also set up the server firewall to allow connections from other Azure resources.

Since we want to use Azure Active Directory authentication, we also need to setup our new server to have an AzureAD admin user. For this we need both the username (user@domain) and the object id of the account in the domain.

As usual, let’s use Azure Resource Manager (ARM) Templates for this, by creating a resource of type Micosoft.Sql/servers/administrators:

{
    "name": "[parameters('sqlServerName')]",
    "type": "Microsoft.Sql/servers",
    "location": "[resourceGroup().location]",
    "apiVersion": "2014-04-01-preview",
    "dependsOn": [],
    "tags": {
        "displayName": "SQL Logical Server"
    },
    "properties": {
        "administratorLogin": "[parameters('sqlServerAdminLogin')]",
        "administratorLoginPassword": "[parameters('sqlServerAdminLoginPassword')]"
    },
    "resources": [
        {
            "name": "activedirectory",
            "type": "administrators",
            "location": "[resourceGroup().location]",
            "apiVersion": "2014-04-01-preview",
            "dependsOn": [
                "[resourceId('Microsoft.Sql/servers', parameters('sqlServerName'))]"
            ],
            "properties": {
                "administratorType": "ActiveDirectory",
                "login": "[parameters('sqlServerAdAdmin')]",
                "sid": "[parameters('sqlServerAdAdminObjectId')]",
                "tenantId": "[subscription().tenantId]"
            }
        },
        {
            "name": "AllowAllWindowsAzureIps",
            "type": "firewallrules",
            "location": "[resourceGroup().location]",
            "apiVersion": "2014-04-01-preview",
            "dependsOn": [
                "[resourceId('Microsoft.Sql/servers', parameters('sqlServerName'))]"
            ],
            "properties": {
                "startIpAddress": "0.0.0.0",
                "endIpAddress": "0.0.0.0"
            }
        },
        {
            "name": "[parameters('sqlDbName')]",
            "type": "databases",
            "location": "[resourceGroup().location]",
            "apiVersion": "2014-04-01-preview",
            "dependsOn": [
                "[resourceId('Microsoft.Sql/servers', parameters('sqlServerName'))]"
            ],
            "tags": {
                "displayName": "SQL DB"
            },
            "properties": {
                "collation": "[parameters('sqlDbCollation')]",
                "edition": "[parameters('sqlDbEdition')]",
                "maxSizeBytes": "1073741824",
                "requestedServiceObjectiveName": "[parameters('sqlDbRequestedServiceObjectiveName')]"
            }
        }
    ]
}

Creating the SQL Login

To define our new SQL login for our service principal, we must first connect to our new database using the Azure AD admin account we created in the previous step. Then, we want to grant the necessary permissions (we’ll make it db_owner for now). You can do this from SQL Server Management Studio, or use a version of SqlCmd.exe that supports Azure AD Authentication:

[CmdletBinding()]
param(
    [Parameter(Mandatory=$true)]
    [String]$sqlServer,
    [Parameter(Mandatory=$true)]
    [String]$database,
    [Parameter(Mandatory=$true)]
    [PSCredential]$sqlAdminCredentials,
    [Parameter(Mandatory=$true)]
    [String]$servicePrincipalName
)

# For this script to work we need SqlCmd v14.1 or later
# which can be downloaded from
# https://www.microsoft.com/en-us/download/details.aspx?id=53591
# and the latest ODBC driver from
# https://www.microsoft.com/en-us/download/details.aspx?id=53339

$query = @"
CREATE USER [$servicePrincipalName] FROM EXTERNAL PROVIDER
GO
ALTER ROLE db_owner ADD MEMBER [$servicePrincipalName]
"@

sqlcmd.exe -S "tcp:$sqlServer,1433" `
           -N -C -d $database -G -U $sqlAdminCredentials.UserName `
           -P $sqlAdminCredentials.GetNetworkCredential().Password `
           -Q $query

Creating the Web Application

Now we can create a new Web App resource that references our Key Vault certificate. We will add this to our ARM template, and most of it is normal stuff, so I won’t repeat it here. The interesting bit is importing the certificate from Key Vault using a resource of type Microsoft.Web/certificates.

To do this, we will need the Id of our Key Vault (which we can get from the resource group and key vault names), the certificate secret name, and the Id of our App Service Plan:

{
    "name": "[concat(parameters('servicePrincipalName'))]",
    "type": "Microsoft.Web/certificates",
    "location": "[resourceGroup().location]",
    "apiVersion": "2015-08-01",
    "properties": {
        "keyVaultId": "[resourceId(parameters('keyVaultRGName'), 'Microsoft.KeyVault/vaults', parameters('keyVaultName'))]",
        "keyVaultSecretName": "[concat('SPCert-', parameters('servicePrincipalName'))]",
        "serverFarmId": "[resourceId('Microsoft.Web/serverfarms', parameters('webAppPlanName'))]"
    }

}

Note: In order to use certificates, our Web Application must be deployed on an App Service Plan in the Basic/Standard/Premium tiers.

The first time you try this, you will likely get an error similar to this:

The service does not have access to '<keyvault_id_here>' Key Vault.
Please make sure that you have granted necessary permissions to the service to perform the request operation

This happens because internally, App Service will use a “Well-Known Service Principal” to connect to your Key Vault and read the certificate, but it won’t have permissions by default.

You can find such principals by enumerating Service Principals in your Azure Active Directory Tenant, using a command such as Get-AzureRmAdServicePrincipal in PowerShell, or az ad sp list in the Azure CLI:

ServicePrincipalNames : {abfa0a7c-a6b6-4736-8310-5855508787cd, Microsoft.Azure.WebSites}
ApplicationId         : abfa0a7c-a6b6-4736-8310-5855508787cd
DisplayName           : Microsoft.Azure.WebSites
Id                    : a365affc-333f-4deb-9f8b-5f802fbb2615
Type                  : ServicePrincipal

We can then grant this principal the necessary permissions by using PowerShell:

Set-AzureRmKeyVaultAccessPolicy -VaultName trkv -ServicePrincipalName abfa0a7c-a6b6-4736-8310-5855508787cd -PermissionsTo get

We can do the same using the Azure CLI command:

az keyvault set-policy --name trkv --spn abfa0a7c-a6b6-4736-8310-5855508787cd --secret-permissions get

Now, our ARM template should be able to deploy the certificate without problems.

Using Token Authentication to SQL

We can now write a simple ASP.NET application that will connect to our SQL Database and show us the authenticated user, which should match our Service Principal.

Let’s first write the code to load the certificate, and then authenticate to Azure AD using the Active Directory Authentication Library (ADAL):

public static class ADAuthentication
{
    static String TenantId = ConfigurationManager.AppSettings["AAD_TENANT_ID"];
    static String Authority = "https://login.windows.net/" + TenantId;
    static String ServicePrincipalName = ConfigurationManager.AppSettings["SERVICE_PRINCIPAL_NAME"];
    static String CertSubjectName = ConfigurationManager.AppSettings["CERT_SUBJECT_NAME"];
    const String SqlResource = "https://database.windows.net/";

    public static X509Certificate2 FindCertificate(String subjectName)
    {
        using (var store = new X509Store(StoreName.My, StoreLocation.CurrentUser))
        {
            store.Open(OpenFlags.ReadOnly);
            var results = store.Certificates.Find(X509FindType.FindBySubjectName, CertSubjectName, false);
            if ( results.Count > 0 )
            {
                return results[0];
            }
        }
        return null;
    }

    public static async Task<String> GetSqlTokenAsync()
    {
        var context = new AuthenticationContext(Authority);
        var certificate = FindCertificate(CertSubjectName);
        if (certificate == null)
            throw new InvalidOperationException("Could not load certificate");

        var credential = new ClientAssertionCertificate($"http://{ServicePrincipalName}", certificate);

        var result = await context.AcquireTokenAsync(SqlResource, credential);
        return result.AccessToken;
    }
}

A couple of notes about the code:

  • To use ClientAssertionCertificate, we need to specify the URL identifier for our Service Principal. The code above assumes that’s http:// and its name.
  • To acquire a token, we need to specify the target resource. In this case, we will always use the https://database.windows.net/ resource identifier to tell AzureAD we want a token to an Azure SQL Server database.

Once we have a token, we can go ahead and connect to SQL Azure, by assigning the token to the SqlConnection.AccessToken property:

public async Task<ActionResult> UsingSP()
{
    String cs = ConfigurationManager.ConnectionStrings["SqlDb"].ConnectionString;

    var token = await ADAuthentication.GetSqlTokenAsync();

    var user = await GetSqlUserName(cs, token);
    ViewBag.SqlUserName = user;
    return View("UserContext");
}

private async Task<String> GetSqlUserName(String connectionString, String token)
{
    using (var conn = new SqlConnection(connectionString))
    {
        conn.AccessToken = token;
        await conn.OpenAsync();
        String text = "SELECT SUSER_SNAME()"; 
        using (var cmd = new SqlCommand(text, conn))
        {
            var result = await cmd.ExecuteScalarAsync();
            return result as String;
        }

    }
}

If the connection succeeds, we should see something like this in our application:

Authenticated user message

Here the username will be composed of two parts separated by @:

  • The first one is the ApplicationId of our service principal in Azure AD.
  • The second is the TenantId for the directory

Conclusion

In this post I introduced how to take advantage of Azure Active Directory Service Principals, together with Key Vault-based certificates to authenticate to Azure SQL using an access token. Along the way, we figured out how to automate some deployment processes and configure things using ARM templates.

Hope you found it useful. In a follow up post, I’ll discuss how to extend this further.