Managed Identity support for Azure Key Vault in SQL Server running on Linux

We are happy to announce that, you can now use Managed Identity to authenticate to Azure Key Vault from SQL Server running on Azure VM (Linux) available from SQL Server 2022 CU18 onwards. 

This blog will walk you through the process of using a user-assigned managed identity to access Azure Key Vault and configure Transparent Data Encryption(TDE) for a SQL database.

Managed Identity:

Microsoft Entra ID, formerly Azure Active Directory, provides an automatically managed identity to authenticate to any Azure service that supports Microsoft Entra authentication, such as Azure Key Vault, without exposing credentials in the code. Refer Managed identities for Azure resources - Managed identities for Azure resources | Microsoft Learn for more details.

VM Setup and Prerequisites:

Before diving into the setup, it's essential to ensure that your Azure Linux VM has SQL Server installed and that the VM has identities assigned with the necessary key vault permissions.

1. Set up SQL Server running on Azure Linux VM. Refer SQL Server on RHEL VM in Azure: RHEL: Install SQL Server on Linux - SQL Server | Microsoft Learn, SQL Server on SLES VM in Azure: SUSE: Install SQL Server on Linux - SQL Server | Microsoft Learn, SQL Server on Ubuntu VM in Azure: Ubuntu: Install SQL Server on Linux - SQL Server | Microsoft Learn for more details.
2. Create user-assigned Managed Identity. Refer https://learn.microsoft.com/en-us/azure/active-directory/managed-identities-azure-resources/how-to-manage-ua-identity-portal for more details.
3. Go to Azure Linux VM resource in the Azure portal and click on Identity tab under security blade. Go to the User assigned tab in the right side panel and click on Add. Select the user-assigned managed identity and click on Add.

4. Create a Key Vault and Keys. Refer Integrate Key Vault with SQL Server on Windows VMs in Azure (Resource Manager) - SQL Server on Azure VMs | Microsoft Learn for more details.
5. Assign Key Vault Crypto Service Encryption User role to the user-assigned managed identity to perform wrap and unwrap operations.

• • Go to the key vault resource that you created, and select the Access control (IAM)setting.

     

  • Select Add> Add role assignment.
  • Search for Key Vault Crypto Service Encryption User and select the role. Select Next.

     

  • In the Members tab, select Managed identity option and click on Select members option, and then search for the user-assigned managed identity that you created in Step 3. Select the managed identity and then click on Select button.  

Setting the primary identity on Azure Linux VM

To set the managed identity as the primary identity for Azure Linux VM, you can use the mssql-conf tool packaged with SQL Server. Here are the steps:

1. Use the mssql-conf tool to manually set the primary identity.
2. Run the following commands:

          sudo /opt/mssql/bin/mssql-conf set network.aadmsiclientid <client id of the managed identity>

          sudo /opt/mssql/bin/mssql-conf set network.aadprimarytenant <tenant id>

      3. Restart the SQL Server:

          sudo systemctl restart mssql-server

Enable TDE using EKM and managed identity:

Refer Managed Identity Support for Extensible Key Management (EKM) with Azure Key Vault (AKV) - SQL Server on Azure VMs | Microsoft Learn for configuration steps for Azure Windows VM. These steps remain same for SQL Server running on an Azure Linux VM.

1.Enable EKM in SQL Server running on the Azure VM.

2.Create credential and encrypt the database. 

When using the CREATE CREDENTIAL command in this context, you only need to provide the 'Managed Identity' in the IDENTITY argument. Unlike earlier scenarios, you do not need to include a SECRET argument. This simplifies the process and enhances security by not requiring a secret to be passed.

Conclusion:

Using managed identity to access Azure Key Vault in SQL Server running on an Azure Linux VM boosts security, streamlines key management, and supports compliance. With data protection being paramount, Azure Key Vault’s integration along with managed identity offers a robust solution. Stay tuned for more insights on SQL Server on Linux! 

Official Documentation:

1. Managed Identity Support for Extensible Key Management (EKM) with Azure Key Vault (AKV) - SQL Server on Azure VMs | Microsoft Learn
2. Extensible Key Management using Azure Key Vault - SQL Server
3. Setup Steps for Extensible Key Management Using the Azure Key Vault
4. Azure Key Vault Integration for SQL Server on Azure VMs