SQL Server Connector Maintenance & Troubleshooting
Supplemental information about the SQL Server Connector is provided in this topic. For more information about the SQL Server connector, see Extensible Key Management Using Azure Key Vault (SQL Server), Setup Steps for Extensible Key Management Using the Azure Key Vault, and Use SQL Server Connector with SQL Encryption Features.
A. Maintenance Instructions for SQL Server Connector
The SQL Server Connector requires the key name to only use the characters "a-z", "A-Z", "0-9", and "-", with a 26-character limit.
Different key versions under the same key name in Azure Key Vault will not work with SQL Server Connector. To rotate an Azure Key Vault key that's being used by SQL Server, a new key with a new key name must be created.
Typically server asymmetric keys for SQL Server encryption need to be versioned every 1-2 years. It's important to note that although the Key Vault allows keys to be versioned, customers should not use that feature to implement versioning. The SQL Server Connector cannot deal with changes in Key Vault key version. To implement key versioning, the customer must create a new key in the Key Vault and then re-encrypt the data encryption key in Management Studio.
For TDE, this is how this would be achieved:
In PowerShell: Create a new asymmetric key (with a different name from your current TDE asymmetric key) in the Key Vault.
Add-AzKeyVaultKey -VaultName 'ContosoDevKeyVault' ` -Name 'Key2' -Destination 'Software'
Using Management Studio or sqlcmd.exe: Use the following statements as shown in Step 3, section 3.
Import the new asymmetric key.
USE master CREATE ASYMMETRIC KEY [MASTER_KEY2] FROM PROVIDER [EKM] WITH PROVIDER_KEY_NAME = 'Key2', CREATION_DISPOSITION = OPEN_EXISTING GO
Create a new login to be associated with the new asymmetric key (as shown under the TDE instructions).
USE master CREATE LOGIN TDE_Login2 FROM ASYMMETRIC KEY [MASTER_KEY2] GO
Create a new credential to be mapped to the login.
CREATE CREDENTIAL Azure_EKM_TDE_cred2 WITH IDENTITY = 'ContosoDevKeyVault', SECRET = 'EF5C8E094D2A4A769998D93440D8115DAADsecret123456789=' FOR CRYPTOGRAPHIC PROVIDER EKM; ALTER LOGIN TDE_Login2 ADD CREDENTIAL Azure_EKM_TDE_cred2; GO
Choose the database whose database encryption key you would like to re-encrypt.
USE [database] GO
Re-encrypt the database encryption key.
ALTER DATABASE ENCRYPTION KEY ENCRYPTION BY SERVER ASYMMETRIC KEY [MASTER_KEY2]; GO
Upgrade of SQL Server Connector
Versions 184.108.40.2060 and older have been replaced and are no longer supported in production environments. Versions 220.127.116.11 and newer are supported in production environments. Use the following instructions to upgrade to the latest version available on the Microsoft Download Center.
If you are currently using Version 18.104.22.168 or newer, follow these steps to update to the latest version of the SQL Server Connector. These instructions avoid the need to restart the SQL Server instance.
Install the newest version of the SQL Server Connector from the Microsoft Download Center. In the installer wizard, save the new DLL file under a file path different from your original SQL Server Connector DLL's file path. For example, the new file path could be:
C:\Program Files\SQL Server Connector for Microsoft Azure Key Vault\<latest version number>\Microsoft.AzureKeyVaultService.EKM.dll
In the instance of the SQL Server, run the following Transact-SQL command to point your SQL Server instance to your new version of the SQL Server Connector:
ALTER CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov FROM FILE = 'C:\Program Files\SQL Server Connector for Microsoft Azure Key Vault\<latest version number>\Microsoft.AzureKeyVaultService.EKM.dll' GO
If you are currently using Version 22.214.171.1240 or older, follow these steps to update to the latest version of the SQL Server Connector.
Stop the instance of SQL Server.
Stop the SQL Server Connector service.
Uninstall the SQL Server Connector using the Windows Programs and Features feature.
(Alternatively, you can rename the folder that the DLL file is in. The default name of the folder is "SQL Server for Microsoft Azure Key Vault".
Install the newest version of the SQL Server Connector from the Microsoft Download Center.
Restart the instance of SQL Server.
Run the following statement to alter the EKM Provider to start using the newest version of the SQL Server Connector. Make sure that the file path is pointing to where you downloaded the newest version. (This step can be skipped if the new version is being installed in the same location as the original version.)
ALTER CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov FROM FILE = 'C:\Program Files\SQL Server Connector for Microsoft Azure Key Vault\Microsoft.AzureKeyVaultService.EKM.dll'; GO
Check that the databases using TDE are accessible.
After validating that the update works, you may delete the old SQL Server Connector folder (if you chose to rename it instead of uninstalling in Step 3.)
Rolling the SQL Server Service Principal
SQL Server uses Service Principals created in Azure Active Directory as credentials to access the Key Vault. Service Principal has a Client ID and Authentication Key. A SQL Server credential is set up with the VaultName, Client ID, and Authentication Key. The Authentication Key is valid for a certain period of time (one or two years). Before the time period expires a new key must be generated in Azure AD for the Service Principal. Then the credential has to be changed in SQL Server. Management Studio maintains a cache for the credential in the current session, so when a credential is changed, Management Studio should be restarted.
Key Backup and Recovery
The Key vault should be regularly backed up. If an asymmetric key in the vault is lost, it can be restored from backup. The key must be restored using the same name as before, which the Restore PowerShell command will do (see below steps).
If the vault has been lost, you will need to recreate a vault and restore the asymmetric key to the vault using the same name as before. The vault name can be different (or the same as before). You must also set the access permissions on the new vault to grant to the SQL Server service principal the access that is needed for the SQL Server encryption scenarios and then adjust the SQL Server credential so that the new vault name is reflected.
In summary, here are the steps:
- Back up the vault key (using the Backup-AzureKeyVaultKey Powershell cmdlet).
- In the case of vault failure, create a new vault in the same geographic region*. The user creating this should be in the same default directory as the service principal setup for SQL Server.
- Restore the key to the new vault (using the Restore-AzureKeyVaultKey Powershell cmdlet - this restores the key using the same name as before). If there is already a key with the same name, the restore will fail.
- Grant permissions to the SQL Server service principal to use this new vault.
- Modify the SQL Server credential used by the Database Engine to reflect the new vault name (if needed).
Key backups can be restored across Azure regions, as long as they remain in the same geographic region or national cloud: USA, Canada, Japan, Australia, India, APAC, Europe Brazil, China, US Government, or Germany.
B. Frequently Asked Questions
On Azure Key Vault
How do key operations work with Azure Key Vault?
The asymmetric key in the key vault is used to protect SQL Server encryption keys. Only the public portion of the asymmetric key ever leaves the vault; the private portion is never exported by the vault. All cryptographic operations using the asymmetric key are done within the Azure Key Vault service, and are protected by the service's security.
What is a Key URI?
Every key in Azure Key Vault has a Uniform Resource Identifier (URI), which you can use to reference the key in your application. Use the format
https://ContosoKeyVault.vault.azure.net/keys/ContosoFirstKey to get the current version, and use the format
https://ContosoKeyVault.vault.azure.net/keys/ContosoFirstKey/cgacf4f763ar42ffb0a1gca546aygd87 to get a specific version.
On Configuring SQL Server
What are the endpoints that the SQL Server Connector needs access to? The Connector talks to two endpoints, which need to be whitelisted. The only port required for outbound communication to these other services is 443 for Https:
What are the minimum permission levels required for each configuration step in SQL Server?
Though you could perform all the configuration steps as a member of the sysadmin fixed server role, Microsoft encourages you to minimize the permissions you use. The following list defines the minimum permission level for each action.
To create a cryptographic provider, requires
CONTROL SERVERpermission or membership in the sysadmin fixed server role.
To change a configuration option and run the
RECONFIGUREstatement, you must be granted the
ALTER SETTINGSserver-level permission. The
ALTER SETTINGSpermission is implicitly held by the sysadmin and serveradmin fixed server roles.
To create a credential, requires
ALTER ANY CREDENTIALpermission.
To add a credential to a login, requires
ALTER ANY LOGINpermission.
To create an asymmetric key, requires
CREATE ASYMMETRIC KEYpermission.
How do I change my default Active Directory so my key vault is created in the same subscription and Active Directory as the service principal I created for the SQL Server Connector?
Go to the Azure classic portal: https://manage.windowsazure.com
On the left-hand menu, scroll down and select Settings.
Select the Azure subscription you are currently using, and click Edit Directory from the commands at the bottom of the screen.
In the pop-up window, use the Directory dropdown to select the Active Directory you'd like to use. This will make it the default Directory.
Make sure you are the global admin of the newly selected Active Directory. If you are not the global admin, so might lose management permissions because you switched directories.
Once the pop-up window closes, if you don't see any of your subscriptions, you may need to update the Filter by Directory filter in the Subscriptions filter in the top-right hand menu of the screen to see subscriptions using your newly updated Active Directory.
You may not have permissions to actually change the default directory on your Azure subscription. In this case, create the AAD service principal within your default directory so that it is in the same directory as the Azure Key Vault used later.
To learn more about Active Directory, read How Azure subscription are related to Azure Active Directory
C. Error Code Explanations for SQL Server Connector
Provider Error Codes:
|0||scp_err_Success||The operation has succeeded.|
|1||scp_err_Failure||The operation has failed.|
|2||scp_err_InsufficientBuffer||This error tells engine to allocate more memory for the buffer.|
|3||scp_err_NotSupported||The operation is not supported. For example, the key type or algorithm specified is not supported by the EKM provider.|
|4||scp_err_NotFound||The specified key or algorithm could not be found by the EKM provider.|
|5||scp_err_AuthFailure||The authentication has failed with EKM provider.|
|6||scp_err_InvalidArgument||The provided argument is invalid.|
|7||scp_err_ProviderError||There is an unspecified error happened in EKM provider that is caught by SQL engine.|
|2049||scp_err_KeyNameDoesNotFitThumbprint||The key name is too long to fit into SQL engine's thumbprint. The key name must not exceed 26 characters.|
|2050||scp_err_PasswordTooShort||The secret string that is the concatenation of AAD client ID and secret is shorter than 32 characters.|
|2051||scp_err_OutOfMemory||SQL engine has run out of memory and failed to allocate memory for EKM provider.|
|2052||scp_err_ConvertKeyNameToThumbprint||Failed to convert key name to thumbprint.|
|2053||scp_err_ConvertThumbprintToKeyName||Failed to convert thumbprint to key name.|
|3000||ErrorSuccess||The AKV operation has succeeded.|
|3001||ErrorUnknown||The AKV operation has failed with an unspecified error.|
|3002||ErrorHttpCreateHttpClientOutOfMemory||Cannot create an HttpClient for AKV operation due to out of memory.|
|3003||ErrorHttpOpenSession||Cannot open an Http session because of network error.|
|3004||ErrorHttpConnectSession||Cannot connect an Http session because of network error.|
|3005||ErrorHttpAttemptConnect||Cannot attempt a connect because of network error.|
|3006||ErrorHttpOpenRequest||Cannot open a request due to network error.|
|3007||ErrorHttpAddRequestHeader||Cannot add request header.|
|3008||ErrorHttpSendRequest||Cannot send a request due to network error.|
|3009||ErrorHttpGetResponseCode||Cannot get a response code due to network error.|
|3010||ErrorHttpResponseCodeUnauthorized||Server responded 401 for the request.|
|3011||ErrorHttpResponseCodeThrottled||Server has throttled the request.|
|3012||ErrorHttpResponseCodeClientError||The request sent from the connector is invalid. This usually means the key name is invalid or contains invalid characters.|
|3013||ErrorHttpResponseCodeServerError||Server responded a response code between 500 and 600.|
|3014||ErrorHttpQueryHeader||Cannot query for response header.|
|3015||ErrorHttpQueryHeaderOutOfMemoryCopyHeader||Cannot copy the response header due to out of memory.|
|3016||ErrorHttpQueryHeaderOutOfMemoryReallocBuffer||Cannot query the response header due to out of memory when reallocating a buffer.|
|3017||ErrorHttpQueryHeaderNotFound||Cannot find the query header in the response.|
|3018||ErrorHttpQueryHeaderUpdateBufferLength||Cannot update the buffer length when querying the response header.|
|3019||ErrorHttpReadData||Cannot read response data due to network error.|
|3076||ErrorHttpResourceNotFound||The server responded 404, because the key name was not found. Make sure the key name exists in your vault.|
|3077||ErrorHttpOperationForbidden||The server responded 403, because the user doesn't have proper permission to perform the action. Make sure you have the permission for the specified operation. At minimum, the connector requires 'get, list, wrapKey, unwrapKey' permissions to function properly.|
If you don't see your error code in this table, here are some other reasons the error may be happening:
You may not have Internet access and cannot access your Azure Key Vault - please check your Internet connection.
The Azure Key Vault service may be down. Try again at another time.
You may have dropped the asymmetric key from Azure Key Vault or SQL Server. Restore the key.
If you receive a "Cannot load library" error, make sure you have the appropriate version of the Visual Studio C++ redistributable installed based on the version of SQL Server that you are running. The table below specifies which version to install from the Microsoft Download Center.
|SQL Server Version||Redistributable Install Link|
|2008, 2008 R2, 2012, 2014||Visual C++ Redistributable Packages for Visual Studio 2013|
|2016||Visual C++ Redistributable for Visual Studio 2015|
More About Extensible Key Management:
SQL Encryptions supporting EKM:
Related Transact-SQL Commands:
Azure Key Vault documentation:
PowerShell Azure Key Vault Cmdlets reference
Extensible Key Management Using Azure Key Vault
Use SQL Server Connector with SQL Encryption Features
EKM provider enabled Server Configuration Option
Setup Steps for Extensible Key Management Using the Azure Key Vault