SSMS Encryption Wizard - Enabling Always Encrypted in a Few Easy Steps

  • [アーティクル]

As we explained in the previous articles, Always Encrypted is a client-side encryption technology - the database system (SQL Server or Azure SQL Database) does not have access to plaintext encryption keys and cannot encrypt or decrypt data protected with Always Encrypted. Consequently, enabling Always Encrypted in a database requires the use of client-side tools to provision Always Encrypted keys and to download encrypted and upload encrypted data back to the database. The new Encrypt Columns wizard in the latest version of SQL Server Management Studio makes it possible to enable Always Encrypted in your database in just a few easy steps, hiding complexities of key provisioning and data encryption.

To start with the new wizard (and to follow the examples presented in this article), you will need:

Note: Until CTP3/October 2015 refresh of SSMS, encrypting existing data either required writing custom code or using the SQL Server Import/Export Wizard, which we described in the Encrypting Existing Data with Always Encrypted article. While the Import/Export Wizard continues to be a useful tool when you need to migrate your data from a plaintext version of a table/database to a different table/table using Always Encrypted, the new Encrypt Columns wizard is now the recommended approach to enable Always Encrypted and encrypt data in an existing database/table.

Sample Database

In the following examples, we assume our database contains the following table, named Patients, defined below.

 CREATE TABLE [dbo].[Patients](
 [PatientId] [int] IDENTITY(1,1) NOT NULL,
 [SSN] [nvarchar](11) NOT NULL,
 [FirstName] [nvarchar](50) NOT NULL,
 [LastName] [nvarchar](50) NOT NULL,
 [MiddleName] [nvarchar](50) NULL,
 [StreetAddress] [nvarchar](50) NULL,
 [City] [nvarchar](50) NULL,
 [ZipCode] [int] NULL,
 [State] [nvarchar](50) NULL,
 [BirthDate] [datetime2](7) NOT NULL,
 PRIMARY KEY CLUSTERED 
 (
 [PatientId] ASC
 ) WITH (
 PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
 ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 ) ON [PRIMARY] 

Encrypt Column Wizard Steps

Starting the Wizard

You can launch the wizard from multiple entry points:

  • To encrypt columns located in multiple tables, right-click your database in Object Explorer and select Tasks>Encrypt Columns… .

  • To encrypt columns located in one particular table, navigate to your table in Object Explorer, right click on it and select Encrypt Columns… .

 

  • To encrypt one particular column, navigate to the column in Object Explorer, right click on the column and select Encrypt Column… .

Once the wizard starts, the Introduction page should open. Click Next > to go to the next step.

 

Column Selection

 

 

On the Column Selection page, select columns you want to encrypt using the checkboxes next to column names.

Note: Always Encrypted is not supported for columns using certain data types (e.g. XML, CRL types, aliases, etc.) or certain features (e.g. columns with check constraints, statistics, etc.). For the complete list of limitations, see Always
Encrypted (Database Engine). In its current version, the wizard checks your schema against a subset of unsupported conditions. For example, the wizard will not let you select a column that uses an unsupported data type. However, the validation against the complete list of limitations is only performed when you complete the wizard steps and trigger the actual encryption. In one of the next releases of SSMS, we will enhance the logic in the Column Selection page to ensure the validation is more complete.

Next, for each selected column, you need to pick an encryption type: Deterministic or Randomized.

  • Deterministic encryption supports equality comparison on encrypted columns, i.e. the database system can infer whether two plaintext values are equal by comparing their ciphertext values. Deterministic encryption supports grouping, filtering (exact match) and equality joins, but it can also enable an unauthorized user to guess plain text values by examining patterns in encrypted values.
  • Randomized encryption generates a different ciphertext value for the same plaintext, so it is more secure, but it also prevents any T-SQL operations on encrypted columns and indexing.

You will also need to choose a column encryption key for each column to be encrypted. As we discussed in previous articles, Always Encrypted uses two types of keys:

  • Column encryption keys – used to encrypt data in your database columns.
  • Column master keys – key-protecting keys used to encrypt column encryption keys.

In this step of the wizard, you need to specify a column encryption key for the columns you are encrypting. If no column encryption key is defined in your database yet, the default value in the Encryption Key column in the Column Selection page is CEK_Auto1(New) , which indicates the wizard will generate a new column encryption key named CEK_Auto1.

Click Next > to go proceed to the next step.

Best Practice

  • Keep in mind that once a column is encrypted, T-SQL functionality for the columns becomes restricted (no operations with randomized encryption, only equality comparison with deterministic encryption). Therefore, before enabling encryption in a production database, you should encrypt columns in a test database first, and carefully test your application workload to identify queries that are impacted by encryption. In general, it is recommended to start by encrypting a small subset of columns that contain the most sensitive information that requires the highest level of protection.
  • Use deterministic encryption if your application need to perform exact match searches, equality joins or group on the selected column AND if the set of values, stored in the column, is large and randomly distributed.
  • Use randomized encryption if your application does not perform operations on encrypted data on the column, or if the column contains few values (e.g. true/false, gender) or if the distribution of values stored in the column is publically known (e.g. race of medical patients or college students).

Master Key Configuration

 

If you chose an auto-generated column encryption key in the previous step, you now need to configure its column master key. If you already have a column master key setup in your database, simply select it. If not, you can use the wizard to also generate the column master key. For that, specify where your column master key is to be stored. You have a couple of options:

  • Windows certificate store. If you pick this option, the wizard will generate a self-signed certificate and will put it in Windows certificate store on the machine, where SSMS is running. You will need to set the master key source to one of the following:
    • Current User – the generated certificate will be stored in your user-specific certificate store location.
    • Local Machine – the generated certificate will be stored in the global certificate store location that is shared by multiple users on the machine. Note: to create certificates in the Location Machine store location, you need to be running SSMS as an administrator
  • Azure Key Vault – an Azure cloud service (currently in preview) for storing secrets, including cryptographic keys. If you choose this option, you will be prompted to sign in to Azure, and select an existing vault in Azure Key Vault. See The Official Azure Key Vault Team Blog as well as our preview article Creating Custom Key Store Providers for Always Encrypted (Azure Key Vault Example) for how to create and set up a vault in Azure Key Vault.

Click Next > to proceed to the next step.

Generate PowerShell or Proceed with Encryption

 

In this step, you can either choose to save the encryption workflow as a PowerShell script to be executed later, or to proceed with encryption now.

Note: When using the current version of the wizard, you need to make sure no other application inserts or updates rows in the tables, containing encrypted columns, while the encryption workflow is running. During the encryption workflow, the wizard creates a temporary table, downloads the data from your original table, encrypts the data and uploads it to the temporary table. Finally, the wizard deletes the original table and renames the temporary table to the original table. If another app is inserting or modifying data in the original table, the new or updated data may be lost. Make sure, you only run the encryption workflow in a planned maintenance window. This issue will be addressed in a later version of SSMS.

Best Practice

In general, it is recommended you create a database backup before running the wizard.

Reviewing Workflow Steps

 Examine the encryption steps and click Finish to trigger the workflow. Monitor progress of encryption and then close the wizard.

Explore the Results

Let us examine the results of running the wizard:

  • Table schema changes,
  • New key metadata,
  • Generated key in a column master key store,
  • Encrypted data.

Table Schema Changes

If you script the schema of the table, containing columns you have encrypted, you will notice the ENRYPTED WITH clause has been added to the encrypted columns.

 CREATE TABLE [dbo].[Patients](
 [PatientId] [int] IDENTITY(1,1) NOT NULL,
 [SSN] [nvarchar](11) ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL,
 [FirstName] [nvarchar](50) NOT NULL,
 [LastName] [nvarchar](50) NOT NULL,
 [MiddleName] [nvarchar](50) NULL,
 [StreetAddress] [nvarchar](50) NULL,
 [City] [nvarchar](50) NULL,
 [ZipCode] [int] NULL,
 [State] [nvarchar](50) NULL,
 [BirthDate] [datetime2](7) ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL,
 PRIMARY KEY CLUSTERED 
 (
 [PatientId] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 ) ON [PRIMARY]

New Key Metadata

In Object Explorer, navigate to the Security/AlwaysEncrypted Keys folder under your database. There, you will find entries for both the column master key and the column encryption key, the wizard generated.

Generated Key

If you chose to generate a self-signed certificate to be used a column master key, you can find and explore the properties of the certificate. Run certmgr.msc if your certificate is stored in the Current User location, or run certlm.msc if it is stored in the Local Machine location. You will find your certificate under the Personal store.

 

Encrypted Data

When you retrieve data from your table, you will notice binary encrypted values in the columns you have encrypted.

Now, reconnect to the database by adding the following connection string keyword/value: column encryption setting=enabled:

Re-run the same query using the new connection. This time, the query should return plaintext values from encrypted columns. Why? Because the above connection string keyword/value enables Always Encrypted in the client driver and causes the driver to transparently decrypt the data retrieved from encrypted columns. Since you have an access to the column master key protecting your columns (the key is either in Windows certificate store on your machine or in a vault you can access), decryption succeeds and you can view plaintext data.

To see what happens if you instruct the driver to decrypt query results, but you do not have access to your column master key, perform the following test:

  1. If your column master key is:
    • A certificate stored in the Current User location: log on to the same machine using a different account, or log on to another machine.
    • A certificate stored in the Local Machine location: log on to another machine.
    • A key in Azure Key Vault: log on to the same or a different machine as another user, who does not have a permission in Azure Key Vault to access the key.
  2. Start SSMS and connect to your database with column encryption setting=enabled.
  3. Run a query to retrieve data from encrypted columns.

Even if you connected to the database as a DBA, you will get an error, indicating you have no access to the column master key and, therefore you will not be able to see the data in plaintext (you can still retrieve ciphertext, if you connect without column encryption setting=enabled). See the previous article, Always Encrypted Key Metadata, for details on how the keys and key metadata are used for decryption.

Next Steps

You now have encrypted sensitive data in your database. Next:

  • You can re-run the wizard to encrypt more columns, change any encryption settings, or to decrypt a column you encrypted previously.
  • Test your stored procedures, views, and queries inside your applications. You might need to refactor your stored procedures/views/queries if they use operations that are unsupported for the type of encryption you configured for your columns.
  • Make sure your client applications or middle-tier services that need access to sensitive data in plaintext, are able to decrypt the data. This will involve:
    • Configuring the applications to use a SQL client driver that supports Always Encrypted.
    • Modifying database connection strings to include column encryption setting=enabled.
    • Making the column master key available to the application. This steps will depend on where you column master key is stored.

If your column master key is a certificate in Windows Certificate Store, you will need to deploy the certificate to each machine hosting your application/service to the right store location (Current User or Local Machine).

Comments

  • Anonymous
    November 13, 2015
    I tried using the Azure Key Vault option. I selected my Azure subscription and my Azure Key Vault. The following error occurs after creating the Column Master Key: 'Generate new column encryption key CEK_Auto1' -- Status : 'Failed' -- Details : 'Task failed due to following error: Operation "wrapKey" is not allowed'.

  • Anonymous
    November 13, 2015
    Update to my previous comment. I was able to solve that error by giving my subscription user the proper key permissions using the Set-AzureKeyVaultAccessPolicy.

  • Anonymous
    January 01, 2016
    The comment has been removed

  • Anonymous
    January 06, 2016
    The comment has been removed

  • Anonymous
    January 27, 2016
    Hi Jakub Szymaszek,2 weeks before I tried to create an encrypted column in one particular table and suppose if it fails then i was getting an detailed error report. but today i tried to encrypt the other column in the same table, but i could not get any detailed report. I just see the info like thiscould not deploy package.Error Deploy: 72002 one or more errors occured.could u please let me know how can i look in to the detailed error report. that will be more helpful for us to resolve the major issue. this will be really blocking us to proceed further.thanks in advance

  • Anonymous
    February 01, 2017
    Please help Getting following error at summary of Encrypt columns (configure Always Encrypted) Topic in SSMS 2016Thanks in advancehttps://s30.postimg.org/rjl3ctbpd/0_D3_F63113475_B7196236_D70385_A7_EEC44323_D164_EFB899_A.png

  • Anonymous
    February 01, 2017
    Error image for referencehttps://s30.postimg.org/rjl3ctbpd/0_D3_F63113475_B7196236_D70385_A7_EEC44323_D164_EFB899_A.png