Background

Error code 33186 occurs when an Azure SQL Database cannot access Azure Key Vault to retrieve encryption keys, usually related to Transparent Data Encryption (TDE) or Always Encrypted features. This issue arises due to insufficient permissions or configuration problems when trying to access secrets (encryption keys) stored in Azure Key Vault.

Azure SQL Database integrates with Azure Key Vault to store and manage encryption keys for data encryption (TDE or Always Encrypted). This error typically indicates that the SQL Database’s identity (either managed identity or service principal) doesn’t have permission to access Key Vault.

Summary Table

Error Explanation

The error message typically reads:

Error 33186: Access to the Azure Key Vault is denied. The SQL server is not able to retrieve the required encryption keys from the Azure Key Vault.

This means that Azure SQL Database is unable to retrieve the encryption key required for encrypting or decrypting data because of insufficient access rights or misconfiguration between the SQL Database and Key Vault.

Common Causes

1. Insufficient Key Vault Permissions: The managed identity or service principal of the SQL Database does not have the correct access policies (e.g., get, list permissions) to access Key Vault secrets or encryption keys.
2. Firewall Rules or Network Restrictions: The Key Vault’s firewall or network settings block access from the SQL Database.
3. Invalid Key Vault URI or Name: The Key Vault URI provided in the SQL Database configuration is incorrect.
4. Misconfigured Managed Identity: The system-assigned or user-assigned managed identity for the SQL Database is not properly configured or lacks access to Key Vault.
5. Key Expiration: The encryption key in the Key Vault may have expired or been deleted.

Steps to Troubleshoot and Resolve Error Code 33186

1. Verify Azure Key Vault Access Policy

The most common cause of this error is missing permissions for the SQL Database to access the Azure Key Vault. You need to verify that the SQL Database’s managed identity (or service principal) has sufficient permissions to access Key Vault secrets and encryption keys.

Steps to Verify and Grant Access to Key Vault:

1. Go to the Azure Portal.
2. Navigate to your Key Vault.
3. Under Settings, click Access policies.
4. Verify if the SQL Database’s managed identity or service principal has the required permissions:
   • Key permissions: get, list
   • Secret permissions: get, list (if accessing secrets)
5. If the SQL Database is not listed, click Add Access Policy, assign the necessary permissions, and select the managed identity or service principal of the SQL Database.

Example Using PowerShell to Add Access Policy:

$vaultName = "your-keyvault-name"
$identityObjectId = "managed-identity-object-id"

Set-AzKeyVaultAccessPolicy -VaultName $vaultName -ObjectId $identityObjectId -PermissionsToKeys get,list

Ensure the identity being used by the SQL Database has the necessary get and list permissions for keys in the Key Vault.

2. Check Firewall and Network Settings of the Key Vault

If the Azure Key Vault is configured with restrictive firewall or network rules, it may be blocking access from the SQL Database.

Steps to Check Key Vault Network Settings:

1. Go to the Azure Portal.
2. Navigate to your Key Vault.
3. Under Settings, select Networking.
4. Ensure that the Firewall and Virtual Networks settings allow access from trusted Microsoft services, including Azure SQL Database. You can:
   • Allow access to trusted Microsoft services.
   • Configure a Virtual Network (VNet) rule to allow access from specific subnets.
   • Add the IP ranges used by the SQL Database server to the firewall rules.

If your SQL Database and Key Vault are in different virtual networks, ensure that there is proper peering between the networks.

3. Verify Key Vault URI and Name

Ensure that the Key Vault URI (Uniform Resource Identifier) provided when setting up TDE or Always Encrypted is correct. An incorrect Key Vault URI or name could result in this error.

Steps to Verify Key Vault URI:

1. Go to Azure Portal.
2. Navigate to SQL Server or SQL Database that is configured with Azure Key Vault for TDE or Always Encrypted.
3. Under Transparent Data Encryption (TDE) or Always Encrypted, verify that the Key Vault URI is correct.

You can check the Key Vault URI format. It typically looks like:

https://<keyvault-name>.vault.azure.net/

Ensure that the <keyvault-name> is the actual name of your Key Vault and that it matches the Key Vault resource in your Azure account.

4. Check and Configure Managed Identity for SQL Database

Ensure that your SQL Database is using the correct managed identity (system-assigned or user-assigned) to access Key Vault.

Steps to Verify Managed Identity Configuration:

1. Go to Azure Portal.
2. Navigate to your SQL Database.
3. Under Settings, click Identity.
4. Ensure that System-assigned managed identity is enabled or that the correct User-assigned managed identity is selected.
5. If necessary, enable or configure the managed identity and assign the required Key Vault access permissions as shown in Step 1.

If you’re using a user-assigned managed identity, ensure that the identity is assigned to the SQL Database instance and that it has proper access to the Key Vault.

5. Check for Key Expiration or Deletion

If the encryption key stored in Key Vault has expired or been deleted, Azure SQL Database will not be able to retrieve it, leading to this error. Check the status of the key to ensure it is still valid.

Steps to Verify Key Status in Key Vault:

1. Go to Azure Portal.
2. Navigate to your Key Vault.
3. Under Settings, click Keys.
4. Ensure the required key is listed and check its status. Verify:
   • The key has not expired.
   • The key has not been disabled or deleted.

If the key has expired or been deleted, you will need to create or restore a new key and update the SQL Database configuration.

6. Ensure SQL Database and Key Vault Are in the Same Azure Region

In some cases, network latency or region mismatches between the SQL Database and the Azure Key Vault may lead to access issues. Ensure that both the SQL Database and Key Vault are located in the same Azure region to minimize connectivity issues.

Workarounds

• Use Service Principal Authentication: Instead of managed identity, you can authenticate to Azure Key Vault using a service principal if you have control over credentials and don’t want to use managed identities.
• Use Manual Encryption Keys: If access to Key Vault cannot be resolved, you can manually manage encryption keys outside of Key Vault for some scenarios, though this adds complexity.

Solutions

1. Grant Permissions: Ensure that the managed identity or service principal for the SQL Database has the necessary get and list permissions for Key Vault keys.
2. Configure Key Vault Networking: Allow Azure SQL Database to access Key Vault by configuring firewalls, VNets, and allowing trusted Microsoft services.
3. Check Key Vault URI: Ensure the correct Key Vault URI is provided in the SQL Database configuration.
4. Configure Managed Identity: Ensure that the SQL Database has a properly configured managed identity (system-assigned or user-assigned).
5. Verify Key Validity: Ensure that the key used in Key Vault has not expired or been deleted.

Example Scenario

Suppose you’re configuring Transparent Data Encryption (TDE) in Azure SQL Database and you want to store your encryption keys in Azure Key Vault. When trying to enable TDE, you receive the following error:

Error 33186: Access to the Azure Key Vault is denied.

Step 1: You go to the Azure Portal and navigate to your Key Vault. Under Access Policies, you notice that your SQL Database’s managed identity does not have get and list permissions for the encryption key.

Step 2: You add the managed identity to the access policies and grant it the necessary permissions:

Set-AzKeyVaultAccessPolicy -VaultName 'your-keyvault-name' -ObjectId 'your-managed-identity-id' -PermissionsToKeys get,list

Step 3: After configuring the access policies, you retry enabling TDE, and the operation succeeds.