Developing Databases using Always Encrypted with SQL Server Data Tools

  • [アーティクル]

We are excited to announce that SQL Server Data Tools (SSDT) now supports developing databases using Always Encrypted.

Always Encrypted is a feature in SQL Server 2016 and Azure SQL Database that ensures your sensitive data is never revealed in plaintext to the database system. An Always Encrypted-enabled client driver achieves that by transparently encrypting and decrypting any data that is sent to or received from database columns, set up as with Always Encrypted.

To start developing databases using Always Encrypted using SSDT, you will need to install the following software on your development machine:

Please note: Always Encrypted is not supported in SQL Server 2016 Express Edition. We recommend you install SQL Server 2016 Developer Edition or use Azure SQL Database as your development database when working with enterprise features. Always Encrypted is supported in all editions of SQL Server Database V12.

Below, we provide an end-to-end walkthrough for how to import an existing database into an SSDT project, enable Always Encrypted for selected database columns, and publish the changes back to the database.

Import a Database into a SQL Project

If you do not already have a SQL project you can get started by importing one from a development database. If you already use SQL projects you can skip to the next step. To import the database into SSDT:

  1. In Visual Studio, open SQL Server Object Explorer (select SQL Server Object Explorer in the View menu).
  2. Connect to your database: in SQL Server Object Explorer, right click on SQL Server, select Add SQL Server, specify the information for your database connection, and click Connect.
  3. Navigate to your database, right-click on it and select Create New Project... . You can leave the default settings in the Create New Project - Import Database dialog. Click Start. Once, the import operation completes, click Finish.

Provision a Column Master Key (CMK)

Always Encrypted uses two types of cryptographic keys: column encryption keys (CEKs) and column master keys (CMKs). A column encryption key is used to encrypt data in an encrypted column. A column master key is a key-protecting key that encrypts one or more column encryption keys. A column master key is stored in a key store that is external the database and is accessible to a client application, for example Windows Certificate Store or Azure Key Vault. The database server does not store the keys of either type in plaintext - it only contains metadata about the keys in column master key and column encryption key metadata objects.

Before you can configure encryption for a column, you need to provision a column master key and a column encryption key. Provisioning either key involves:

  • Generating the actual key
  • Create a metadata object, describing the key in the database.

Let's start with provisioning a column master key.

Create a column master key in your certificate store

First, you need to create the actual column master key. Currently SSDT does not support this step, but you can easily create a column master key using PowerShell or SSMS. Here is an example for how to create a column master key that is a certificate store the Current User certificate store location using PowerShell.
$cert = New-SelfSignedCertificate -Subject "AlwaysEncryptedCert" -CertStoreLocation Cert:CurrentUserMy -KeyExportPolicy Exportable -Type DocumentEncryptionCert -KeyUsage KeyEncipherment -KeySpec KeyExchange -KeyLength 2048 $cert.Thumbprint

Define the column master key in your project

In Solution Explorer, right click on your project and select Add > New Item... . Then, select Column Master Key, change the name (this is the name of the column master key metadata object) if you wish, and click Add. This will generate the following stub that creates a column master key metadata object in the database.
CREATE COLUMN MASTER KEY [ColumnMasterKey1] WITH ( KEY_STORE_PROVIDER_NAME = N'[SomeProviderName]', KEY_PATH = N'[SomeKeyPath]' ) GO

Set the column master key properties

Modify the generated stub to reference your column master key. You need to change the value of the KEY_STORE_PROVIDER_NAME  property to correspond to the key store containing your column master key, and you need to set KEY_PATH to identify your column master key in the key store. See CREATE COLUMN MASTER KEY (Transact-SQL) for more information. Here is how to do that for a column master key that is a certificate stored in the Current User certificate store location. Note, that the key path contains the certificate thumbprint, which is the output of the PowerShell script, you executed in the Create a column master key in your certificate store step above.
CREATE COLUMN MASTER KEY [ColumnMasterKey1] WITH ( KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE', KEY_PATH = N'CurrentUser/My/7DF4AC73178D365291DF943E0F09653DDE296FC7' ) GO

Provision a Column Encryption Key (CEK)

To provision a column encryption key you'll follow similar steps to the column master key provisioning:

Create a column master key encrypted by your CMK

First you need create the actual column encryption key and encrypt it with the column master key. Again, SSDT does not support this step so you need to use PowerShell or SSMS. The below example generates a column encryption key encrypted with a column master key that is a certificate stored in the Current User certificate store location, using the SqlServer PowerShell module (which ships with SSMS).
# Import the SqlServer module Import-Module "SqlServer" -DisableNameChecking # Create a SqlColumnMasterKeySettings object for your column master key. $cmkSettings = New-SqlCertificateStoreColumnMasterKeySettings -CertificateStoreLocation "CurrentUser" -Thumbprint $cert.Thumbprint # Generate a column encryption key, encrypt it with the column master key to produce an encrypted value of the column encryption key. $encryptedValue = New-SqlColumnEncryptionKeyEncryptedValue -TargetColumnMasterKeySettings $cmkSettings $encryptedValue

Define the column encyption key in your project

In Solution Explorer right click on your project and select Add > New Item... . Then select Column Encryption Key, change the name if you wish, and click Add. This will generate the following stub T-SQL that defines the column encryption key metadata object in the database. CREATE COLUMN ENCRYPTION KEY [ColumnEncryptionKey1] WITH VALUES ( COLUMN_MASTER_KEY = [SomeCMKName], ALGORITHM = N'AEAD_AES_256_CBC_HMAC_SHA_256', ENCRYPTED_VALUE = [SomeEncryptedValue] );

Set the column encryption key properties

Modify the generated stub to encapsulate the encrypted column encryption key value you generated in step 1. Set the COLUMN_MASTER_KEY property to the name of the metadata object for the column master key that encrypts the column encryption key. Set ALGORITHM to 'RSA_OAEP'. Copy the hexadecimal string, produced in step 1, as the value of the ENCRYPTED_VALUE property. Here is how the modified T-SQL should look like.
CREATE COLUMN ENCRYPTION KEY [ColumnEncryptionKey1] WITH VALUES ( COLUMN_MASTER_KEY = [ColumnMasterKey1], ALGORITHM = 'RSA_OAEP', ENCRYPTED_VALUE = 0x016E000001630075007200720065006E00740075007300650072002F006D0079002F003700640066003400610063003700330031003700380064003300360035003200390031006400660039003400330065003000660030003900360035003300640064006500320039003600660063003700A4E3998FA143C159CB3F54B819D1E95827F40122E563B9DCBB855F366B1192979EE73374992A2D074DF7F4B19B0D6BE84D674FDFD574A52C1FD09F375D954B8EA01D63F002D36BF7BBE3AE8D3279EAA189F589F4FE520CF76A0064929E094FC1BDEE91B7C53DAB47F09FA3888C5DF07C210A604329F9F5CEB4495E00F7CD7055993ACA7E1D7D6885553D06312C60396A0DF2449144FFC32008B7636901C2B9EDEC8588BAE8DA079496DDA9BCF8367AE618C3D1F1504559A523379C29C8BA8658972BA3A25F46DDE3FC50CB198A3D3EE24CE92E7739CE8443E01A12BD7ABB58790812EF49AB592D0E537D808E1205C92B00D188EB221740CA8891B43EF58CC1968735F7AE779F5A98DF2BC1B983E16D767BD0926AF4EB6847B7D928D0548D77EC76C935DF37C4FB2E410B4925E26BD52648FABF2ED08DDFFD49B13EE5CEC923A1CA3F222AB7E104E0C0649D0D158DBA33687DC1C6FBBF5573F057CF0ADCEF4033ECB832B57C658127109DB871D83CB7FAD6ADADC05089424EECAA57A27BA41AD71AA17109F710004BFC71FF0E30E826F131FB8A63D0284B5EC33C1FA34AD2982E5613F243F8F916C5A4DF9C46210255580E29DE4FC0DE189A013078D4C6E1BA672CE549F4B52672D2FC06B94B9E7F95BFB2238EF73B0694706AF068FD07A874E4ADAF86DD405F2A68D9ABEA50292824555AD4E319A3B024D189D08E8E43CAD235 ) GO

Configure Encryption for Selected Database Columns

Choose the column(s) to encrypt

Now comes the fun part - let's start encrypting your data! In Solution Explorer, navigate to a table that contains columns you want to encrypt. The CREATE TABLE T-SQL statement for the table will show up in the T-SQL editor in the main pane. We'll use a Patients table as our sample throughout the rest of this article.
CREATE TABLE [dbo].[Patients] ( [PatientID] INT IDENTITY (1, 1) NOT NULL, [SSN] CHAR (11) NOT NULL, [FirstName] NVARCHAR (50) NULL, [LastName] NVARCHAR (50) NOT NULL, [MiddleName] NVARCHAR (50) NULL, [StreetAddress] NVARCHAR (50) NOT NULL, [City] NVARCHAR (50) NOT NULL, [ZipCode] CHAR (5) NOT NULL, [State] CHAR (2) NOT NULL, [BirthDate] DATE NOT NULL, CONSTRAINT [PK_dbo.Patients] PRIMARY KEY CLUSTERED ([PatientID] ASC) );

Encrypt the SSN column

In the T-SQL editor, modify the schema of the table, by adding the ENCRYPTED WITH clause for each column you want to encrypt. Specify:

  • COLUMN_ENCRYPTION_KEY - this should be the name of your column encryption key metadata object.
  • ENCRYPTION_TYPE - set it to RANDOMIZED for randomized encryption or DETERMINISTC for deterministic encryption. See Always Encrypted on MSDN or Getting Started with Always Encrypted on this blog for more information.
  • ALGORITHM - it must be set to 'AEAD_AES_256_CBC_HMAC_SHA_256'

In addition, for a string column, you need to set a collation to a binary2 collation, as Always Encrypted requires that string columns use binary collation.
CREATE TABLE [dbo].[Patients] ( [PatientID] INT IDENTITY (1, 1) NOT NULL, [SSN] CHAR (11) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [ColumnEncryptionKey1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL, [FirstName] NVARCHAR (50) NULL, [LastName] NVARCHAR (50) NOT NULL, [MiddleName] NVARCHAR (50) NULL, [StreetAddress] NVARCHAR (50) NOT NULL, [City] NVARCHAR (50) NOT NULL, [ZipCode] CHAR (5) NOT NULL, [State] CHAR (2) NOT NULL, [BirthDate] DATE ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [ColumnEncryptionKey1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL, CONSTRAINT [PK_dbo.Patients] PRIMARY KEY CLUSTERED ([PatientID] ASC) );

Build and Publish your Project

You're all set to publish your changes and encrypt your first column.

  1. In Solution Explorer, right click on your project and select Publish... . If your built builds successfully, the Publish Database Dialog will open.
  2. Specify your target server and database, and click Publish.

SSDT will publish the schema changes you have made. It will also encrypt any data stored in columns, which you have configured as encrypted.

Publish vs. Generate Script

Note: when running against tables with data already present, you will need to use the Publish option to perform encryption. Because the data is pulled down to the client, encrypted in the client driver and published back in encrypted form, you cannot use pure T-SQL commands to encrypt your data.

Choosing Generate Script will produce a T-SQL script that works so long as there is no data in the table being encrypted. If data is detected, the operation will be blocked before any data movement occurs.

Verify Always Encrypted Configuration

You can now explore your database to confirm Always Encrypted has been setup.

Check your keys are in the database

Using SQL Server Object Explorer navigate to the Security folder under your database. You should find your column master key and column encryption keys objects under Always Encrypted Keys.

Verify table data has been encrypted

Right click on the table containing the columns you have encrypted and select View Data. The data in the columns you encrypted should appear encrypted (binary values).

 

Next Steps

You can now develop an application that queries encrypted columns in your database. See Getting Started with Always Encrypted for how to get started with developing applications using Always Encrypted.

Wrapping Up

SSDT July Update is the first release of SSDT supported in Always Encrypted. You can expect additional enhancements in the future releases, including support for key generation and editing encryption-related settings for your columns using the UI. As always, we are looking for your feedback or comments on this blog or at:

Comments

  • Anonymous
    January 08, 2019
    Is there a way to use a variable to set the ENCRYPTED_VALUE on the COLUMN ENCRYPTION KEY?
  • Anonymous
    February 04, 2019
    The example above for running New-SelfSignedCertificate errored for me on the CertStoreLocation. This worked for me: -CertStoreLocation "Cert:\CurrentUser\My"