Ask Learn
Preview
Please sign in to use this experience.
Sign inThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
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.
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]
You can launch the wizard from multiple entry points:
Once the wizard starts, the Introduction page should open. Click Next > to go to the next step.
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.
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:
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.
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:
Click Next > to proceed to the next step.
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.
In general, it is recommended you create a database backup before running the wizard.
Examine the encryption steps and click Finish to trigger the workflow. Monitor progress of encryption and then close the wizard.
Let us examine the results of running the wizard:
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]
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.
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.
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:
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.
You now have encrypted sensitive data in your database. Next:
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).
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
Please sign in to use this experience.
Sign in