Manage Learn to apply best practices and optimize your operations.

How to set up the new SQL Server data encryption feature

If you're tired of decrypting data to perform operations on it, the new SQL Server data encryption feature Always Encrypted may be the answer.

Organizational policies, or industry or government regulations, might require you to use encryption to protect...

sensitive data stored within databases. To help you meet these regulatory requirements, SQL Server data encryption provides several options for encrypting data when transmitted through the network, while creating a backup or when stored on the server or network. The SQL Server data encryption options include transparent data encryption, column-level encryption, encryption of SQL Server objects definitions, backup encryption, encryption of SQL Server connections, database file-level encryption through Windows EFS and BitLocker drive encryption. These data encryption options provide strong security for data at the database and operating system levels, preventing the likelihood of unauthorized disclosure of confidential information, even if the SQL Server infrastructure or databases are compromised.

The downside to using any of these SQL Server data encryption methods is that you cannot directly access encrypted data or make changes to it. To perform operations on encrypted data, you have to decrypt the data first, meaning data does not remain in encrypted form throughout its lifecycle.

To solve this problem, Microsoft introduced the Always Encrypted security feature with SQL Server 2016. Always Encrypted uses an enhanced ADO.NET client-side library and cipher text to encrypt and decrypt data. When data is encrypted using Always Encrypted, you can perform operations on encrypted data, without needing to decrypt the data first. This means that sensitive information encrypted using this feature will not be in plain text. So, unlike other SQL Server data encryption capabilities, this feature ensures that your data will be kept encrypted at rest and in motion.

Moreover, the data encryption and decryption is done behind the scenes within the application, reducing the number of changes that have to be made to existing applications. No other relational database management system provides a feature like Always Encrypted.

Always Encrypted uses two types of keys: column encryption keys (CEKs) and column master keys (CMKs). Column encryption keys are used to encrypt sensitive data stored in database columns; column master keys are used to encrypt the column encryption keys. You create CMKs before you create CEKs. It is possible to encrypt all column values using a single CEK. Information about the CMK and CEK is stored in the database's system catalog views. You should back up a CEK in a secured, trusted location on network.

Implementing the Always Encrypted security feature

For the purpose of this demo, I will use the OUTLANDER database hosted on the instance of SQL Server running on my machine.

Step 1: Create a column master key

Using Object Explorer in SQL Server 2016, expand the Databases folder, then the database in which you want to provision Always Encrypted keys. Next, expand the Security folder, and then expand the Always Encrypted Keys folder. Right-click on the Column Master Key Definitions folder, and then click New Column Master Key Definition. This opens a New Column Master Key Definition dialog box (Figure 1).

New Column Master Key Definition dialog box.
Figure 1. New Column Master Key Definition dialog box.

In the Name box, enter a name of a new CMK. Next, specify the name of the key store provider for the key store containing the CMK, as shown in Figure 1. For the CMK, the best option is to use a certificate stored in your local machine certificate store. For this example, I created a self-signed certificate by clicking the Generate Self-Signed Certificate button and putting the certificate in my personal store. Finally, click OK, to create a CMK in the database.

Step 2: Create a column encryption key

In the Always Encrypted Keys folder, right-click on the Column Encryption Keys folder, then click New Column Encryption Key. This opens up a New Column Encryption Key dialog box (Figure 2).

New Column Encryption Key dialog box.
Figure 2. New Column Encryption Key dialog box.

In the Name box, enter a name of a new CEK and define its corresponding CMK, as shown in Figure 2. Click OK to create your new CEK.

Step 3: Create a table with encrypted columns

In the SSMS 2014 query editor, type and run the CREATE TABLE Transact-SQL statement shown in Figure 3. This statement will create the dbo.Contact_AlwaysEncryptedDemo table with encrypted columns.

Note that columns encrypted using deterministic encryption support equality lookups, joins and group-by, while columns using randomized encryption do not support these operations.

The CREATE TABLE Transact-SQL statement.
Figure 3. The CREATE TABLE Transact-SQL statement.

Testing the Always Encrypted security feature

Now that we have created a table with encrypted columns, we will use the SQL Server import and export wizard to copy data to this table, so we can verify if the copied data is encrypted.

To import data into the dbo.Contact_AlwaysEncryptedDemo table, start the import and export wizard. Click Next to bypass the welcome screen. On the Choose a Data Source page (Figure 4), configure the following:

  • Data Source: Choose SQL Server Native Client 11.0 from the dropdown menu.
  • Server Name: Type the name of the SQL Server instance that contains the source data.
  • Authentication: Choose authentication mode for the data source connection.
  • Database: Choose the database that contains the source data. For example, in my case, it is the OULTANDER database.
The Choose a Data Source page.
Figure 4. The Choose a Data Source page.

Click Next to go to the Choose a Destination page (Figure 5). On this page, configure the following:

  • Data Source: Choose .Net Framework Data Provider for SqlServer from the dropdown menu.
  • Set the Column Encryption Setting property to Enabled.
  • Configure other properties as required (e.g., Data Source, Initial Catalog and authentication-related keywords).
The Choose a Destination page.
Figure 5. The Choose a Destination page.

Click Next to proceed to the Select Table Copy or Query page. You have two options on this page. You can either select to copy tables and views or to copy the results of a query from the data source. I will be copying all the data from the dbo.Contact table to dbo.Contact_AlwaysEncryptedDemo table. Click the circle next to the words Copy data from one or more tables or views. Click the Next button to continue.

On the Select Source Tables and Views page (Figure 6), select the dbo.Contact table as source table and dbo.Contact_AlwaysEncryptedDemo table as the destination table, and then click the Next button.

The Select Source Tables and Views page.
Figure 6. The Select Source Tables and Views page.

Click the Next button to proceed to the Save and Run Package page (Figure 7). Select the Run immediately option and click the Next button.

The Save and Run Package page.
Figure 7. The Save and Run Package page.

This takes you to the Complete the Wizard page (Figure 8), where you can view the choices you made. Click finish to run the package.

The Complete the Wizard page.
Figure 8. The Complete the Wizard page.

To verify the data is properly encrypted, query your encrypted table. For example, when I queried the dbo.Contact_AlwaysEncryptedDemo table, it returned the result set shown in Figure 9. Data in the encrypted columns is displayed as binary data (Figure 9).

Results from querying the dbo.Contact_AlwaysEncryptedDemo table.
Figure 9. Results from querying the dbo.Contact_AlwaysEncryptedDemo table.

Next Steps

Listen to this podcast about SQL Server 2016 security and analytics features

Learn about features like Always Encrypted in SQL Server 2016

This was last published in September 2015

Dig Deeper on SQL Server Security

Join the conversation

2 comments

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

What problems did you encounter when trying to set up the new SQL Server data encryption feature?
Cancel
Even after running import and export wizard, I'm unable to see any data in Encrypted column, not even binary data.
Cancel

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close