SQL Server – Creating Symmetric Key from External Provider

sql serversql-server-2016symmetric-keyt-sql

Knowing the security objects hierarchy, we can see that in order to create a Symmetric Key and encrypt data, we need to create:

  1. Database Master Key which is protected by password
  2. Certificate which is protected by the Database Master key

  3. The Symmetric Key itself, which is protect by the Certificate

    enter image description here

In Always Encrypted, we can use EKM module to simplify this hierarchy. For example, we can store the certificates in the Windows Certification Store and these certificates are protecting our encryption master keys.

I am wondering, if I want to use not Always Encrypted built-in encryption functionalities, can I use EKM module to create and manage my symmetric keys (like it is shown on the diagram). In the CREATE SYMMETRIC KEY documentation we have provider option, but not enough information about possible providers and examples.

I am interesting in storing the Certificate which is protecting the Symmetric keys or the Symmetric keys in external storage, because in such way the data is separated from the keys and in the database we are storing only references to the keys (like in always encrypted). Windows Certification Store will be best option for me, but Azure Key Vault or something else will work as well, I guess.

Best Answer

tl;dr - use CREATE CRYPTOGRAPHIC PROVIDER to create a connection inside SQL Server to your EKM/HSM hardware/software, through a vendor-provided .DLL file designed for that purpose.

You can use Azure Key Vault to protect on-premises data. There are many steps required to enable your Azure account with Azure Key Vault, which are detailed at setup steps for extensible key management using Azure Key Vault.

The steps inside SQL Server consist of the following:

  1. Install the SQL Server Connector for Azure Key Vault from the Microsoft download center.

  2. Run the following code to enable EKM providers:

    -- Enable advanced options.  
USE master;  
GO  

sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  

-- Enable EKM provider  
sp_configure 'EKM provider enabled', 1;  
GO  
RECONFIGURE;

  3. Register the EKM module:

    CREATE CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov   
FROM FILE = 'C:\Program Files\SQL Server Connector for Microsoft Azure Key Vault\Microsoft.AzureKeyVaultService.EKM.dll';  
GO

  4. Setup a credential for SQL Server to connect to Azure:

    USE master;  
CREATE CREDENTIAL sysadmin_ekm_cred   
    WITH IDENTITY = 'ContosoDevKeyVault', -- for public Azure
    -- WITH IDENTITY = 'ContosoDevKeyVault.vault.usgovcloudapi.net', -- for Azure Government
    -- WITH IDENTITY = 'ContosoDevKeyVault.vault.azure.cn', -- for Azure China
    -- WITH IDENTITY = 'ContosoDevKeyVault.vault.microsoftazure.de', -- for Azure Germany   
    SECRET = 'EF5C8E094D2A4A769998D93440D8115DReplace-With-AAD-Client-Secret'   
FOR CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov;  

-- Add the credential to the SQL Server administrator's domain login   
ALTER LOGIN [<domain>\<login>]  
ADD CREDENTIAL sysadmin_ekm_cred;

  5. Open the key vault:

    CREATE ASYMMETRIC KEY CONTOSO_KEY   
FROM PROVIDER [AzureKeyVault_EKM_Prov]  
WITH PROVIDER_KEY_NAME = 'ContosoRSAKey0',  
CREATION_DISPOSITION = OPEN_EXISTING;

You can then enable TDE by following the steps listed to use SQL Server with SQL Encryption.

Related Question