Edit

Provision Always Encrypted keys using SQL Server Management Studio

  • Article

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

This article provides the steps to provision column master keys and column encryption keys for Always Encrypted using SQL Server Management Studio (SSMS). Make sure you install the latest general availability (GA) version of SSMS when provisioning encryption keys.

For an overview of Always Encrypted key management, including best practice recommendations and important security considerations, see Overview of key management for Always Encrypted.

Provision Column Master Keys with the New Column Master Key Dialog

The New Column Master Key dialog allows you to generate a column master key or pick an existing key in a key store, and create column master key metadata for the created or selected key in the database.

  1. Using Object Explorer, navigate to the Security -> Always Encrypted Keys node under your database.

  2. Right-click on the Column Master Keys node and select New Column Master Key....

  3. In the New Column Master Key dialog, enter the name of the column master key metadata object.

  4. Select a key store:

    • Certificate Store - Current User - indicates the Current User certificate store location in the Windows Certificate Store, which is your personal store.

    • Certificate Store - Local computer - indicates the Local computer certificate store location in the Windows Certificate Store.

    • Azure Key Vault - you need to sign in to Azure (click Sign in). Once you sign in, you are able to select one of your Azure subscriptions and a key vault or a managed HSM (requires SSMS 18.9 or later).

      Note

      Using column master keys stored in a managed HSM in Azure Key Vault requires SSMS 18.9 or a later version.

    • Key Store Provider (KSP) - indicates a key store that is accessible via a key store provider (KSP) that implements the Cryptography Next Generation (CNG) API. Typically, this type of a store is a hardware security module (HSM). After you select this option, you'll need to pick a KSP. Microsoft Software Key Store Provider is selected by default. If you want to use a column master key stored in an HSM, select a KSP for your device (it must be installed and configured on the computer before you open the dialog).

    • Cryptographic Service Provider (CSP) - a key store that is accessible via a cryptographic service provider (CSP) that implements the Cryptography API (CAPI). Typically, such a store is a hardware security module (HSM). After you select this option, you'll need to pick a CSP. If you want to use a column master key stored in an HSM, select a CSP for your device (it must be installed and configured on the computer before you open the dialog).

    Note

    Since CAPI is a deprecated API, the Cryptographic Service Provider (CAPI) option is disabled by default. You can enable by creating the CAPI Provider Enabled DWORD value under the [HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\sql13\Tools\Client\Always Encrypted] key in Windows Registry, and setting it to 1. You should use CNG instead of CAPI, unless your key store does not support CNG.

    For more information about the above key stores, see Create and store column master keys for Always Encrypted.

  5. If you're using SQL Server 2019 (15.x) and your SQL Server instance is configured with a secure enclave, you can select the Allow enclave computations checkbox to make the master key enclave-enabled. See Always Encrypted with secure enclaves for details.

    Note

    The Allow enclave computations checkbox does not appear if your SQL Server instance is not correctly configured with a secure enclave.

  6. Pick an existing key in your key store, or click the Generate Key or Generate Certificate button, to create a key in the key store.

  7. Click OK and the new key appears in the list.

Once you complete the dialog, SQL Server Management Studio creates metadata for your column master key in the database. The dialog generates and issues a CREATE COLUMN MASTER KEY (Transact-SQL) statement.

If you're configuring an enclave-enabled column master key, SSMS also signs the metadata using the column master key.

Permissions for provisioning a column master key

You need the ALTER ANY COLUMN MASTER KEY database permission in the database for the dialog to create a column master key. You also need key store permissions to access and use your key column master key. For detailed information on key store permissions required for key management operations, see Create and store column master keys for Always Encrypted and review the section relevant for your key store.

Provision Column Encryption Keys with the New Column Encryption Key Dialog

The New Column Encryption Key dialog allows you to generate a column encryption key, encrypt it with a column master key, and create the column encryption key metadata in the database.

  1. Using Object Explorer, navigate to the Security/Always Encrypted Keys folder under your database.
  2. Right-click on the Column Encryption Keys folder and select New Column Encryption Key....
  3. In the New Column Encryption Key dialog, enter the name of the column encryption key metadata object.
  4. Select a metadata object that represents your column master key in the database.
  5. Click OK.

Once you complete the dialog, SQL Server Management Studio (SSMS) generates a new column encryption key. SSMS then retrieves the metadata for the column master key you selected from the database. SSMS then uses the column master key metadata to contact the key store containing your column master key and encrypt the column encryption key. Finally, SSMS creates the metadata data for the new column encryption in the database by generating and issuing a CREATE COLUMN ENCRYPTION KEY (Transact-SQL) statement.

Note

Using column master keys stored in a managed HSM in Azure Key Vault requires SSMS 18.9 or a later version.

Permissions for provisioning a column encryption key

You need the ALTER ANY COLUMN ENCRYPTION KEY and VIEW ANY COLUMN MASTER KEY DEFINITION database permissions in the database for the dialog to create the column encryption key metadata and to access column master key metadata. You also need key store permissions to access and use your column master key. For detailed information on key store permissions required for key management operations, go to Create and store column master keys for Always Encrypted and find a section relevant for your key store.

Provision Always Encrypted Keys using the Always Encrypted Wizard

The Always Encrypted Wizard is a tool for encrypting, decrypting, and re-encrypting selected database columns. While it can use already configured keys, it also allows you to generate a new column master key and a new column encryption.

Next Steps

See Also