Using Managed Service Identity in Azure Functions to Access Azure SQL Database

Managed Service Identity (MSI) in Azure is a fairly new kid on the block. What it allows you to do is keeping your code and configuration clear of keys and passwords, or any kind of secrets in general. Let’s say you have an Azure Function accessing a database hosted in Azure SQL Database. Often, developers put credentials for SQL Server authentication into the Function’s application settings in terms of a connection string. That takes sensitive information out of the code, but still quite often, configuration is checked into source control. Wouldn’t it be great to manage credentials completely outside of the application realm and push that responsibility to the platform? That’s what MSI allows you to do and this post describes how to go about it.

Under the Hood

MSI is relying on Azure Active Directory to do it’s magic. Once you enable MSI for an Azure Service (e.g. Azure Functions), the fabric will create a dedicated Service Principal (think of it as a technical user or identity) in the Azure AD tenant that’s associated with the Azure subscription. Your service instance ‘knows’ how to leverage this specific identity to retrieve tokens for accessing other Azure services that also support Azure AD-based authentication (like an Azure SQL Database). That experience is fully managed in terms of principal creation, deletion and key rotation, no more need for you to provision certificates, etc. The only thing you need to do is granting access to the service principal for the desired target service, as we will see later on.

At the time of writing this post, MSI is supported for virtual machines running Windows or Linux and for Azure App Service incl. Azure Functions. Authentication works for target services that allow authentication via Azure Active Directory (e.g. ARM, Key Vault, Data Lake, Azure SQL DB).

Enable MSI on your Function App

First thing you need to do is switch on MSI support in your Function’s application settings, as shown below:

Function1

After a couple of seconds, you should see the following message in the notification section of your Azure Portal:

Notification1

You now have a service principal in your Azure AD tenant that is associated with your Azure Function App. Also, the Function App has been enriched by some logic to use this principal internally for retrieving access tokens from Azure AD to be used with other Azure services, without having to mess with the principal’s credentials.

Show me the Principal

So, where and how do I see my principal? In order to do so, let’s check the ARM template of the resource group our Function App resides in. Go to the resource group and navigate to ‘Automation Script’, as shown below. The JSON template contains a new ‘Identity’-section within the website resource, showing the attributes of the principal.

ResourceGroup1

principalId reflects the ObjectId of the service principal in the Azure AD tenant. We can verify that by opening a PowerShell session and execute the following statements:

Install-Module AzureAD (if never done before)
Connect-AzureAD (authenticate to your Azure AD tenant)
Get-AzureADObjectByObjectId –ObjectIds <principalId>

This will show the specific service principal object created for your Function App, carrying the same DisplayName as the Function App. The object will also show up in the list of service principals in your tenant when calling Get-AzureADServicePrincipal.

Obtain a Token for Azure SQL DB

You will find two environment variables MSI_ENDPOINT and MSI_SECRET in your Function App environment (which you can check from the Kudu console). MSI_ENDPOINT is a local service (listens on a service-local address like http://127.0.0.1:41056/MSI/token/) that provides bearer tokens for the principal to be used for accessing an Azure resource like Azure SQL DB. In order to request a token, your code just needs to call this endpoint and specify the resource URI of the target service (e.g. https://database.windows.net/ for Azure SQL), together with the secret key stored in MSI_SECRET.

One way to call the endpoint is via plain REST. If you work with .NET, you can leverage the Microsoft.Azure.Services.AppAuthentication NuGet library instead. Let’s look at a simple HttpTrigger-based C# Azure Function. First, you will need to include the required libraries via your project.json file. Go do that for the helper library above, and also for the System.Data.SqlClient package that’s required for access to the SQL database:

{
  "frameworks": {
    "net46":{
      "dependencies": {
        "Microsoft.Azure.Services.AppAuthentication":"1.0.0-preview",
        "System.Data.SqlClient":"4.4.0"

      }
    }
  }

}

Saving the project.json file will trigger a NuGet restore and pull the libraries into the Function App. Now, you need to include the code for retrieving the access token in the Function as follows:

using System.Net;
using Microsoft.Azure.Services.AppAuthentication;

public static async Task<HttpResponseMessage> Run(HttpRequestMessage req, TraceWriter log)
{
  var tokenProvider = new AzureServiceTokenProvider();
  string accessToken = await tokenProvider.GetAccessTokenAsync(”
https://database.windows.net/”);
  log.Info($"accessToken: {accessToken}");

  return req.CreateResponse(HttpStatusCode.OK);
}

Note, that you need to specify the resource URI for the target service you want to access, in this case that’s https://database.windows.net/ for SQL Database. Running the function should plot the accessToken in the Function’s log output window.

accessToken

Violà, we now have a bearer token in our hands, representing the Azure Function instance! Behind the scenes, the MSI extension we activated for our Azure Function has automagically organized this token from Azure AD on our behalf, using the MSI_ENDPOINT and MSI_SECRET in it’s environment. In the next step, we can now use the token to authenticate against a database.

Configure Access in Azure SQL Database

First of all, you need to enable Azure AD authentication in the SQL Server instance hosting your database by configuring an administrator account:

SQL1

Go ahead and specify a proper user account from your Azure AD tenant. After that’s done, access to the database itself needs to be configured in terms of a contained user. At the time of writing this post, it is not possible to create a contained user for the MSI (i.e. the service principal) itself, so we need to take a detour in terms of doing that for an Azure AD group.

Using your PowerShell session from above, create a group in the Azure AD tenant, e.g. called MsiAccessToSql, and add the service principal as a member:

$group = New-AzureADGroup -DisplayName “MsiAccessToSql” -MailEnabled $false -SecurityEnabled $true -MailNickName “NotSet”

Add-AzureAdGroupMember -ObjectId $group.ObjectId -RefObjectId <principalId>

Make sure to use the proper ObjectId of the MSI service principal. Now that we have the group and added the MSI as a member, we can finally configure access for the group in our target database. In order to do so, open SQL Server Management Studio (SSMS) and connect to the database using the Azure AD admin user we configured on the server previously. Make sure you enable access from your client in the server firewall first.

SQL2

Open a query window for your database and execute the following statements:

CREATE USER MsiAccessToSql FROM EXTERNAL PROVIDER
ALTER ROLE db_datareader ADD MEMBER MsiAccessToSql

This will create a contained user in the database and give it read access (if you need write access, just change the role assignment appropriately). The contained user object is mapped to the Azure AD group MsiAccessToSql containing the MSI service principal.

Access the Database

Now, let’s write the code to access the database in our Azure Function and see if that’s working. In my case, I created a SQL Database based on the AdventureWorksLT sample, so I could read some records from the Customer table.

using System.Net;
using Microsoft.Azure.Services.AppAuthentication;
using System.Configuration;
using System.Data.SqlClient;

public static async Task<HttpResponseMessage> Run(HttpRequestMessage req, TraceWriter log)
{
  var tokenProvider = new AzureServiceTokenProvider();
  string accessToken = await tokenProvider.GetAccessTokenAsync(
https://database.windows.net/);
  log.Info($"accessToken: {accessToken}");

  var str = ConfigurationManager.ConnectionStrings["sqlConnection"].ConnectionString;
  using (SqlConnection conn = new SqlConnection(str))
  {
    conn.AccessToken = accessToken;
    conn.Open();
    var statement = $"select top 5 LastName from SalesLT.Customer";
    log.Info($"{statement}");
    using (SqlCommand cmd = new SqlCommand(statement, conn))
    {
      using (SqlDataReader reader = cmd.ExecuteReader())
      {
        while (reader.Read())
        {
          log.Info($"{reader.GetString(0)}");
        }
      }
    }
  }

  return req.CreateResponse(HttpStatusCode.OK);
}

The connection string for the database is taken from the Function’s application settings and looks like this:

Data Source=<server>.database.windows.net;Initial Catalog=<database>;

Function2

Note that the connection string does not contain any secret, just the server and database we want to connect to. You can see that the token we obtained from the local MSI_ENDPOINT is passed into the SQL connection object like this:

conn.AccessToken = accessToken;

This makes sure we hand the bearer token over to the database, which happily accepts our request, as it will authenticate the MSI via the Azure AD group and the contained user configured in the DB! Executing the Function should show some customer records from the database in the log output window:

Function3

Summary

This post demonstrates how to use Managed Service Identity to keep secrets really secret and let the Azure fabric support you in taking care of the ‘plumbing’. Today, you can use MSI not only with App Service & Azure Functions, but also from Azure VMs. As target services, today it’s Azure Resource Manager (ARM), Azure Key Vault, Azure Data Lake, Storage and Azure SQL DB as shown in the example above.

Over time, the list will grow and make Azure an even more powerful & secure platform as it already is today. Keep an eye on Azure documentation about MSI to stay up-to-date.