Edit

Managed identities for transparent data encryption with customer-managed key

  • Article

Applies to: Azure SQL Database Azure SQL Managed Instance

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. For more information, see Managed identity types in Azure.

Managed Identities can be of two types:

  • System-assigned
  • User-assigned

For more information, see Managed identities in Microsoft Entra ID for Azure SQL.

For TDE with customer-managed key (CMK) in Azure SQL, a managed identity on the server is used for providing access rights to the server on the key vault. For instance, the system-assigned managed identity of the server should be provided with key vault permissions prior to enabling TDE with CMK on the server.

In addition to the system-assigned managed identity that is already supported for TDE with CMK, a user-assigned managed identity (UMI) that is assigned to the server can be used to allow the server to access the key vault. A prerequisite to enable key vault access is to ensure the user-assigned managed identity has been provided the Get, wrapKey and unwrapKey permissions on the key vault. Since the user-assigned managed identity is a standalone resource that can be created and granted access to the key vault, TDE with a customer-managed key can now be enabled at creation time for the server or database.

Note

For assigning a user-assigned managed identity to the logical server or managed instance, a user must have the SQL Server Contributor or SQL Managed Instance Contributor Azure RBAC role along with any other Azure RBAC role containing the Microsoft.ManagedIdentity/userAssignedIdentities/*/assign/action action.

Benefits of using UMI for customer-managed TDE

  • Enables the ability to pre-authorize key vault access for Azure SQL logical servers or managed instances by creating a user-assigned managed identity, and granting it access to key vault, even before the server or database has been created

  • Allows creation of an Azure SQL logical server with TDE and CMK enabled

  • Enables the same user-assigned managed identity to be assigned to multiple servers, eliminating the need to individually turn on system-assigned managed identity for each Azure SQL logical server or managed instance, and providing it access to key vault

  • Provides the capability to enforce CMK at server creation time with an available built-in Azure policy

Considerations while using UMI for customer-managed TDE

  • By default, TDE in Azure SQL uses the primary user-assigned managed identity set on the server for key vault access. If no user-assigned identities have been assigned to the server, then the system-assigned managed identity of the server is used for key vault access.
  • When using a user-assigned managed identity for TDE with CMK, assign the identity to the server and set it as the primary identity for the server
  • The primary user-assigned managed identity requires continuous key vault access (get, wrapKey, unwrapKey permissions). If the identity's access to key vault is revoked or sufficient permissions aren't provided, the database will move to Inaccessible state
  • If the primary user-assigned managed identity is being updated to a different user-assigned managed identity, the new identity must be given required permissions to the key vault prior to updating the primary
  • To switch the server from user-assigned to system-assigned managed identity for key vault access, provide the system-assigned managed identity with the required key vault permissions, then remove all user-assigned managed identities from the server

Important

The primary user-assigned managed identity being used for TDE with CMK should not be deleted from Azure. Deleting this identity will lead to the server losing access to key vault and databases becoming inaccessible.

Limitations and known issues

  • If the key vault is behind a VNet that uses a firewall, the option to Allow Trusted Microsoft Services to bypass this firewall must be enabled in the key vault's Networking menu if you want to use a user-assigned managed identity or system-assigned managed identity. Once this option is enabled, available keys can't be listed in the SQL server TDE menu in the Azure portal. To set an individual CMK, a key identifier must be used. When the option to Allow Trusted Microsoft Services to bypass this firewall isn't enabled, the following error is returned:
    • Failed to save Transparent Data Encryption settings for SQL resource: <ServerName>. Error message: The managed identity with ID '/subscriptions/subsriptionID/resourcegroups/resource_name/providers/Microsoft.ManagedIdentity/userAssignedIdentities/umi_name' requires the following Azure Key Vault permissions: 'Get, WrapKey, UnwrapKey' to the key 'https://keyvault_name/keys/key_name'. Please grant the missing permissions to the identity. Additionally ensure the key is not expired and is not disabled. For expired key, please extend the key expiry time so that SQL can use it to perform wrap and unwrap operations. If your key vault is behind a virtual network or firewall, ensure you select the 'Allow trusted Microsoft services to bypass this firewall' option. (https://aka.ms/sqltdebyokcreateserver).
    • If you get the above error, check if the key vault is behind a virtual network or firewall, and make sure the option Allow Trusted Microsoft Services to bypass this firewall is enabled.
  • User Assigned Managed Identity for SQL Managed Instances is currently only supported on key vaults that have public access from all networks enabled. It is not supported when the AKV firewall is filtering specific virtual networks and IP addresses or using private endpoint connections.
  • When multiple user-assigned managed identities are assigned to the server or managed instance, if a single identity is removed from the server using the Identity pane of the Azure portal, the operation succeeds but the identity doesn't get removed from the server. Removing all user-assigned managed identities together from the Azure portal works successfully.
  • When the server or managed instance is configured with customer-managed TDE and both system-assigned and user-assigned managed identities are enabled on the server, removing the user-assigned managed identities from the server without first giving the system-assigned managed identity access to the key vault results in an Unexpected error occurred message. Ensure the system-assigned managed identity has been provided key vault access prior to removing the primary user-assigned managed identity (and any other user-assigned managed identities) from the server.

Next steps

Create Azure SQL database configured with user-assigned managed identity and customer-managed TDE

See also