Using Managed Service Identity (MSI) to authenticate on Azure SQL DB

This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.

We all know that we can use SQL authentication or Azure AD authentication to log on Azure SQL DB. We can also use Azure AD Token authentication or certificate-based authentication, but we will not explore these ones here. In this article we will explore Managed Service Identity (MSI) authentication or system-assigned identity, and how to use it on Azure VM (Using Powershell) or on an Azure Function (.NET).

Understanding Managed Identity

There are two types of managed identities:

• A system-assigned managed identity is enabled directly on an Azure service instance. When the identity is enabled, Azure creates an identity for the instance in the Azure AD tenant that's trusted by the subscription of the instance. After the identity is created, the credentials are provisioned onto the instance. The lifecycle of a system-assigned identity is directly tied to the Azure service instance that it's enabled on. If the instance is deleted, Azure automatically cleans up the credentials and the identity in Azure AD.
• A user-assigned managed identity is created as a standalone Azure resource. Through a create process, Azure creates an identity in the Azure AD tenant that's trusted by the subscription in use. After the identity is created, the identity can be assigned to one or more Azure service instances. The lifecycle of a user-assigned identity is managed separately from the lifecycle of the Azure service instances to which it's assigned.

Internally, managed identities are service principals of a special type, which are locked to only be used with Azure resources. When the managed identity is deleted, the corresponding service principal is automatically removed.

Your code can use a managed identity to request access tokens for services that support Azure AD authentication. Azure takes care of rolling the credentials that are used by the service instance.

The following diagram shows how managed service identities work with Azure virtual machines (VMs):

How a system-assigned managed identity works with an Azure VM

1. Azure Resource Manager receives a request to enable the system-assigned managed identity on a VM.

2. Azure Resource Manager creates a service principal in Azure AD for the identity of the VM. The service principal is created in the Azure AD tenant that's trusted by the subscription.

3. Azure Resource Manager configures the identity on the VM by updating the Azure Instance Metadata Service identity endpoint with the service principal client ID and certificate.

4. After the VM has an identity, use the service principal information to grant the VM access to Azure resources. To call Azure Resource Manager, use role-based access control (RBAC) in Azure AD to assign the appropriate role to the VM service principal. To call Key Vault, grant your code access to the specific secret or key in Key Vault.

5. Your code that's running on the VM can request a token from the Azure Instance Metadata service endpoint, accessible only from within the VM: http://169.254.169.254/metadata/identity/oauth2/token

   • The resource parameter specifies the service to which the token is sent. To authenticate to Azure Resource Manager, use resource=https://management.azure.com/.
   • API version parameter specifies the IMDS version, use api-version=2018-02-01 or greater.

6. A call is made to Azure AD to request an access token (as specified in step 5) by using the client ID and certificate configured in step 3. Azure AD returns a JSON Web Token (JWT) access token.

7. Your code sends the access token on a call to a service that supports Azure AD authentication.

Hands On

To assign an Identity to an Azure VM you can just go to Azure Portal and enable it

You will be able to find this identity on Azure Active Directory > Enterprise applications

You can also get and Identity on an Azure Webapp or Azure Function like sample below

Giving SQL Permission

• First make sure your Azure SQL DB have an AAD Admin provisioned
• Go to database and add it like adding a regular AAD user, using name of the identity (VM or name of function)

DROP USER IF EXISTS [VMSQLCLient]
GO
CREATE USER [VMSQLCLient] FROM EXTERNAL PROVIDER;
GO
ALTER ROLE db_datareader ADD MEMBER [VMSQLCLient];
ALTER ROLE db_datawriter ADD MEMBER [VMSQLCLient];
GRANT EXECUTE TO [VMSQLCLient]
GO
---------------------------------------------------
DROP USER IF EXISTS [test123fonsecanet]
GO
CREATE USER [test123fonsecanet] FROM EXTERNAL PROVIDER;
GO
ALTER ROLE db_datareader ADD MEMBER [test123fonsecanet];
ALTER ROLE db_datawriter ADD MEMBER [test123fonsecanet];
GRANT EXECUTE TO [test123fonsecanet]
GO

Scenario 1: Using Azure VM - Powershell test

• After assigning MSI to VM, and granting the required permissions on SQL. Start the VM
• Open Powershell ISE
• Run a test script like sample below
  • https://github.com/FonsecaSergio/ScriptCollection/blob/master/Powershell/Connect%20to%20Azure%20SQL%20using%20Managed%20Identity.ps
  • $response = Invoke-WebRequest -Uri 'http://169.254.169.254/metadata/identity/oauth2/token?api-version=2018-02-01&resource=https%3A%2F%2Fdatabase.windows.net%2F' -Method GET -Headers @{Metadata="true"} $content = $response.Content | ConvertFrom-Json $AccessToken = $content.access_token $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Server=tcp:ServerName.database.windows.net,1433;Initial Catalog=sandbox;Persist Security Info=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;" $SqlConnection.AccessToken = $AccessToken try { $SqlConnection.Open() $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = "SELECT SERVERNAME = @@SERVERNAME, SUSER_SNAME = SUSER_SNAME();" $SqlCmd.Connection = $SqlConnection $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $SqlCmd $DataSet = New-Object System.Data.DataSet $SqlAdapter.Fill($DataSet) | Out-Null Write-Host "Success Connection @@SERVERNAME = $($DataSet.Tables[0].Rows[0].SERVERNAME) / SUSER_SNAME = $($DataSet.Tables[0].Rows[0].SUSER_SNAME)" -ForegroundColor Green $SqlConnection.Close() } catch { Write-Host "An error occurred:" -ForegroundColor Red Write-Host $_ -ForegroundColor Red }
• It should return the success message like below

  • Success Connection @@SERVERNAME = XXXX / SUSER_SNAME = 96c85223-6377-4f2f-8a6c-8e773fa3b1b2@72f988bf-86f1-41af-91ab-2d7cd011db

* SUSER_SNAME() first part is the Application ID we found on Azure AD

* SUSER_SNAME() second part is your Azure AD Tenant ID

Scenario 2: Using Azure Function - .NET

I've create an Azure Function sample app (Function_ConnectSQL_MSI) that you can get it from https://github.com/FonsecaSergio/AzureFunctionAppTestConnectivity

I've used a NuGet called Microsoft.Azure.Services.AppAuthentication to simplify that connection, like sample below:

And was able to connect to SQL without the need to provide any login or password

Be aware of some limitations as documented at Troubleshooting problems related to Azure AD authentication with Azure SQL DB and DW.

References:

• https://docs.microsoft.com/en-us/azure/active-directory/managed-identities-azure-resources/tutorial-windows-vm-access-sql
• https://docs.microsoft.com/en-us/azure/app-service/app-service-web-tutorial-connect-msi
• https://docs.microsoft.com/en-us/azure/active-directory/managed-identities-azure-resources/overview
• https://docs.microsoft.com/en-us/azure/active-directory/managed-identities-azure-resources/qs-configure-portal-windows-vm
• https://docs.microsoft.com/en-us/azure/active-directory/managed-identities-azure-resources/how-to-use-vm-token

Tks also to Renato Augusto from CSS (AAD team) that worked with me on a case related to this issue