Bewerken

Delen via

Common errors for transparent data encryption with customer-managed keys in Azure Key Vault

  • Artikel

Applies to: Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics

This article describes how to identify and resolve Azure Key Vault key access issues that caused a database configured to use transparent data encryption (TDE) with customer-managed keys in Azure Key Vault to become inaccessible.

Note

Microsoft Entra ID was previously known as Azure Active Directory (Azure AD).

Introduction

When TDE is configured to use a customer-managed key in Azure Key Vault, continuous access to this TDE Protector is required for the database to stay online. If the logical SQL server or managed instance loses access to the customer-managed TDE protector in Azure Key Vault, a database will start denying all connections with the appropriate error message and change its state to Inaccessible in the Azure portal.

For the first 30 minutes, if the underlying Azure key vault key access issue is resolved, the database will autoheal and come online automatically. This means that for all intermittent and temporary network outage scenarios, no user action is required, and the database will come online automatically. In most cases, user action is required to resolve the underlying key vault key access issue.

If an inaccessible database is no longer needed, it can be deleted immediately to stop incurring costs. All other actions on the database aren't permitted until access to the Azure key vault key has been restored and the database is back online. Changing the TDE option from customer-managed to service-managed keys on the server is also not possible while a database encrypted with customer-managed keys is inaccessible. This is necessary to protect the data from unauthorized access while permissions to the TDE Protector have been revoked.

After a database has been inaccessible for more than 30 minutes, it will no longer autoheal. If the required Azure key vault key access has been restored after that period, you must revalidate the access to the key manually to bring the database back online. Bringing the database back online in this case can take a significant amount of time depending on the size of the database. Once the database is back online, previously configured settings such as failover group, PITR history, and any tags will be lost. Therefore, we recommend implementing a notification system using Action Groups that allows to become aware of and address the underlying key vault key access issues as soon as possible.

Common errors causing databases to become inaccessible

Most issues that occur when you use TDE with Key Vault are caused by one of the following misconfigurations:

The key vault is unavailable or doesn't exist

  • The key vault was accidentally deleted.
  • The firewall was configured for Azure Key Vault, but it doesn't allow access to Microsoft services.
  • An intermittent network error causes the key vault to be unavailable.

No permissions to access the key vault or the key doesn't exist

  • The key was accidentally deleted, disabled or the key expired.
  • The server's managed identity (system-assigned or user-assigned) was accidentally deleted.
  • The server was moved to a different subscription. A new managed identity (system-assigned or user-assigned) must be assigned to the server when it's moved to a different subscription.
  • Permissions granted to the server's managed identity for the keys aren't sufficient (they don't include Get, Wrap, and Unwrap permissions).
  • Permissions for the server's managed identity were revoked from the key vault.

Identify and resolve common errors

In this section, we list troubleshooting steps for the most common errors.

Missing server identity

Error message

401 AzureKeyVaultNoServerIdentity - The server identity is not correctly configured on server. Please contact support.

Detection

Use the following cmdlet or command to ensure that an identity has been assigned to the server:

Mitigation

Use the following cmdlet or command to configure a user-assigned or system-assigned managed identity for the server:

In the Azure portal, go to the key vault, and then go to Access policies. Complete these steps:

  1. Use the Add New button to add the AppId for the server you created in the preceding step.
  2. Assign the following key permissions: Get, Wrap, and Unwrap

To learn more, see Assign a managed identity to your server.

Important

If the logical server for Azure SQL Database or Azure SQL Managed Instance is moved to a new Microsoft Entra tenant after the initial configuration of TDE with Key Vault, this managed identity configuration must be redone in the new tenant.

Missing key vault

Error message

503 AzureKeyVaultConnectionFailed - The operation could not be completed on the server because attempts to connect to Azure Key Vault have failed.

Detection

To identify the key URI and the key vault:

  1. Use the following cmdlet or command to get the key URI of a specific server instance:

  2. Use the key URI to identify the key vault:

    • Azure PowerShell: You can inspect the properties of the $MyServerKeyVaultKey variable to get details about the key vault.

    • Azure CLI: Inspect the returned server encryption protector for details about the key vault.

Mitigation

Confirm that the key vault is available:

  • Ensure that the key vault is available and that the server has access.
  • If the key vault is behind a firewall, ensure that the check box to allow Microsoft services to access the key vault is selected.
  • If the key vault has been accidentally deleted, you must complete the configuration from the start.

Missing key

Error messages

404 ServerKeyNotFound - The requested server key was not found on the current subscription.

409 ServerKeyDoesNotExists - The server key does not exist.

Detection

To identify the key URI and the key vault:

  • Use the cmdlet or commands in Missing key vault to identify the key URI that's added to the logical SQL Server instance. Running the commands returns the list of keys.

Mitigation

Confirm that the TDE protector is present in Key Vault:

  1. Identify the key vault, then go to the key vault in the Azure portal.
  2. Ensure that the key identified by the key URI is present.

Expired key

Error messages

The server <server_name> requires the Key Vault Crypto Service Encryption User permission for the RBAC policy or following Azure Key Vault permissions: Get, WrapKey, UnwrapKey. Please grant the missing permissions to the service principal with ID <akv_key>. Ensure the key is active, not expired or disabled, set with the key activation date no later than the current date, and that trusted Microsoft services can bypass the firewall if applicable.

Detection

To identify the expired key in the key vault:

  • Use the Azure portal, go to the Key vault service menu > Objects > Keys, and check the key expiration.
  • Use the PowerShell command, Get-AzKeyVaultKey.
  • Use the Azure CLI command, az keyvault key show.

Mitigation

  • Check the expiration date for the key vault key to confirm that the key is expired.
  • Extend the key expiration date.
  • Bring the database back online by selecting the Key revalidation option with 1 of the following:
    • Retry existing key.
    • Select backup key.

For more information, see Inaccessible TDE protector.

Note

Keys should be rotated at a specified number of days prior to expiration to provide sufficient time to react to a failure. For more information, see Azure Policy built-in definitions for Key Vault.

Missing permissions

Error message

401 AzureKeyVaultMissingPermissions - The server is missing required permissions on the Azure Key Vault.

Detection

To identify the key URI and key vault:

  • Use the cmdlet or commands in Missing key vault to identify the key vault that the logical SQL Server instance uses.

Mitigation

Confirm that the server has permissions to the key vault and the correct permissions to access the key:

  • In the Azure portal, go to the key vault > Access policies. Find the server's managed identity (system-assigned or user-assigned).
  • If the server identity is present, ensure that it has the following key permissions: Get, WrapKey, and UnwrapKey.
  • If the server identity isn't present, add it by using the Add New button.

Getting TDE status from the Activity log

To allow for monitoring of the database status due to Azure Key Vault key access issues, the following events will be logged to the Activity Log for the resource ID based on the Azure Resource Manager URL.

Note

Events might take at least 15-30 mins to appear in the Activity Log from the time key vault access issue occurs.

Event when the service loses access to the Azure Key Vault key

Azure SQL Database

EventName: MakeDatabaseInaccessible

Status: Started

Description: Database {database_name} on Server {server_name} has lost access to Azure Key Vault Key and is now transitioning to inaccessible state.

Azure SQL Managed Instance

EventName: MakeManagedDbInaccessible

Status: Started

Description: Database { database_name} on managed server {server_name} has lost access to Azure Key Vault Key and is now transitioning to inaccessible state.

Event when the issue wasn't resolved within 30 minutes and Azure Key Vault key access has to be validated manually

Azure SQL Database

EventName: MakeDatabaseInaccessible

Status: Succeeded

Description: Database is inaccessible and requires user to resolve Azure key vault errors and reestablish access to Azure key vault key using Revalidate key.

Azure SQL Managed Instance

EventName: MakeManagedDbInaccessible

Status: Succeeded

Description: Database { database_name} on managed server {server_name} is inaccessible and requires user to re-establish access to Azure Key Vault Key.

Event when re-validation of Azure Key Vault key access has succeeded and the db is coming back online

Azure SQL Database

EventName: MakeDatabaseAccessible

Status: Started

Description: Access to Azure Key Vault Key has been re-established, operation to make database {database_name} on server {server_name} accessible started.

Azure SQL Managed Instance

EventName: MakeManagedDatabaseAccessible

Status: Started

Description: Access to Azure Key Vault Key has been re-established, operation to make database {database_name} on managed server {server_name} accessible started.

Event when the database has successfully come back online

Azure SQL Database

EventName: MakeDatabaseAccessible

Status: Succeeded

Description: Access to Azure Key Vault Key has been re-established and database {database_name} on server {server_name} is now online.

Azure SQL Managed Instance

EventName: MakeManagedDatabaseAccessible

Status: Succeeded

Description: Access to Azure Key Vault Key has been re-established and database {database_name} on managed server {server_name} is now online.

Event when re-validation of Azure Key Vault key access has failed

Azure SQL Database

EventName: MakeDatabaseAccessible

Status: Failed

Description: Access to Azure Key Vault Key has been re-established, operation to make database {database_name} accessible on server {server_name} failed

Azure SQL Managed Instance

EventName: MakeManagedDatabaseAccessible

Status: Failed

Description: Access to Azure Key Vault Key has been re-established, operation to make database {database_name} accessible on managed server {server_name} failed

Related content

  • Learn about Azure Resource Health.
  • Set up Action Groups to receive notifications and alerts based on your preferences, for example, Email/SMS/Push/Voice, Logic App, Webhook, ITSM, or Automation Runbook.