Overview
There are times when you must protect and secure data within SQL Server . SQL Server Always Encrypted is a feature which helps protect sensitive data, such as social security numbers or credit card numbers, providing confidential computing capabilities.
Always Encrypted provides a separation between those who own the data and can view it, and those who manage the data but should have no access. Always Encrypted does this by allowing clients to encrypt sensitive data inside client applications while never revealing the encryption key to the database engine.
One of our clients utilizes Always Encrypted to protect sensitive data. In our work for this client, we realized that a walkthrough documenting the end-to-end process of enabling Always Encrypted would be helpful for everyone.
The Environment
Before enabling SQL Server Always Encrypted, let’s first define the sample environment. In this example, a database named AETest contains a simple table called Table_1 with three columns: an ID column, a name column, and an SSN (social security number) column. In this example, we want to encrypt the SSN column. Currently, we have not enabled column encryption, so all SSN numbers show as plain text.
A quick look in the certificate store shows no certificates associated with Always Encrypted in the Local Computer certificate store.
Lastly, a quick look at the Always Encrypted Keys shows we haven’t created any Column Master or Column Encryption keys. This validates that no columns have been encrypted in this database.
Encrypting Columns with SQL Server Always Encrypted
This section will provide details about encrypting columns using Always Encrypted. This is on the server side, and the next section will continue the walkthrough on the client side.
The first step to encrypting columns with Always Encrypted is to select the columns you wish to encrypt. Right click on the database name and select Tasks -> Encrypt Columns from the context menu, which will start the Always Encrypted wizard.
In the Always Encrypted wizard, click Next on the Introduction page. On the Column Selection page, select the column or columns you wish to encrypt. In this example, select the SSN column from the Table_1 table.
Next, select the Encryption Type. There are two encryption types:
- Deterministic: Always generates the same encrypted value for any given plain text. Allows for joins, grouping, and indexing on encrypted columns but may allow unauthorized users to guess information about encrypted values.
- Randomized: Encrypts data in a less predictable manner. This is more secure, but prevents searching, grouping, indexing, and joining on encrypted columns.
We can’t change the Encryption Key name, so click Next.
In the Master Key Configuration page, select the Windows certificate store as the key store provider. Then select Local Machine as the master key source. Click Next.
On the Run Settings page, select the Proceed to finish now option. The Generate PowerShell script option lets you save the steps in PowerShell script to execute at a later time. This might come in handy when you want to implement Always Encrypted during off-hours, or save the script to reference in the future. Click Next.
On the Summary page, review the steps and operations to perform, then click Finish.
Depending on the number of columns to encrypt and the size of the table (number of rows), the encryption process could take a little bit of time. The Results page will show the progress of each step. Once each task has completed, click OK on the Always Encrypted wizard.
Always Encrypted on the Server
Now that the selected column or columns have been encrypted, we can verify its implementation and functionality by doing another check of the keys and certificate. Refreshing the Column Master Keys and Column Encryption Keys nodes in SQL Server Management Studio in the AETest database, we can see the generated Master and Encryption keys.
- Column Encryption Key: Used to encrypt data in an encrypted column
- Column Master Key: Key-protecting key that encrypts one or more encryption keys.
We can also see a new certificate in the Personal\Certificates store for the Local Computer specific to Always Encrypted.
Using SSMS on the server and connecting to the Server, we can open a new query window and query Table_1, showing that the SSN values are indeed encrypted. Close the query window.
In SQL Server Management Studio, disconnect from the server, and then click the connect button to reconnect.
Before connecting, switch to the Always Encrypted tab and click the Enable Always Encrypted (column encryption) option, as shown in the screenshot below. Go back to the Login tab and click Connect.
This scenario demonstrated how to use Always Encrypted while operating on the local server in which you have enabled Always Encrypted. In the following section, I’ll outline how to enable and use Always Encrypted at the client.
Enabling and Using Always Encrypted on a Client
In this example we used the local certificate store to store the column master key. In order for clients (other computers) to see the encrypted data in plain text, each client will need the key. Thus, we will export the key.
On the server in which you have Always Encrypted enabled, navigate to the store where you’ll find the Always Encrypted certificate (probably Local Computer\Personal\Certificates). Right click on the Always Encrypted certificate and select All Tasks -> Export.