Always Encrypted is a new feature included in SQL Server 2016 for encrypting column data at rest and in motion. This represents an important difference from the original column-level encryption, which is concerned only with data at rest. Always Encrypted also differs from Transparent Data Encryption (TDE), which is also limited to data at rest. In addition, TDE can be applied only to the database as a whole, not to individual columns.

With Always Encrypted, the client application handles the actual data encryption and decryption outside of the SQL Server environment. In this way, you can better control who can access the data in an unencrypted state, allowing you to enforce separation of roles and minimize the risks to sensitive data.

To be able to encrypt and decrypt the data, the application must use an Always Encrypted-enabled driver that interfaces with SQL Server 2016. It is this driver that carries out the actual encryption and decryption processes, rewriting the T-SQL queries as necessary, while keeping these operations transparent to the application.

To implement Always Encrypted on a column, you need to generate a column encryption key and a column master key. The column encryption key encrypts the column data, and the master key encrypts the column encryption key.

The database engine stores the column encryption key on the SQL Server instance where Always Encrypted is implemented. For the master key, the database engine stores only metadata that points to the key’s location. The actual master key is saved to a trusted external key store, such as the Windows certificate store. At no time does the database engine use or store either key in plain text.

You’ll get a better sense of how all this works as we go through the article’s examples, which walk you through the process of implementing Always Encrypted in the test database. The article focuses primarily on the SQL Server side of the equation, demonstrating how to create the two encryption keys and encrypt the columns.

Note that this is the third article in a series on SQL Server encryption. The first two cover basic column-level encryption and TDE. You can access the articles through the following links:

• Encrypting SQL Server: Using an Encryption Hierarchy to Protect Column Data
• Encrypting SQL Server: Transparent Data Encryption (TDE)

For information about enabling Always Encrypted in client applications and their drivers, refer to the MSDN article Always Encrypted (client development), which points you to details about several drivers, including .NET Framework Data Provider for SQL Server, Microsoft JDBC Driver for SQL Server, and ODBC Driver for SQL Server.

Prepare for Always Encrypted

When SQL Server 2016 was first released, the Always Encrypted feature was available only to the Enterprise and Developer editions, but with the release of SQL Server 2016 Service Pack 1, Always Encrypted is now available to all editions.

There’s not much you need to do to prepare a database for enabling Always Encrypted, other than to be running an instance of SQL Server 2016, with SP1 installed if necessary. However, before you try to implement Always Encrypted, you should be aware of the many limitations that come with this feature.

To begin with, you cannot use Always Encrypted to protect columns configured with the following data types:

• XML
• ROWVERSIONTIMESTAMP
• IMAGE
• TEXTNTEXT
• SQL_VARIANT
• HIERARCHYID
• GEOGRAPHY
• GEOMETRY

Always Encrypted also comes with a number of other restrictions. For example, you cannot encrypt columns that use aliases or user-defined data types or are configured with default constraints or the ROWGUID property. And you’ll find other limitations as well. A good place to start for understanding when you can and cannot use Always Encrypted is with the MSDN article Always Encrypted (Database Engine).

For the examples in this article I created a database that contains one table and populated the table with data from the AdventureWorks2014 database (installed on a local instance of SQL Server 2016). To create these objects, I used the following T-SQL script:

You do not need to create this database and table to try out the examples. You can use whatever environment you want. Just substitute the target database and table as appropriate when going through the examples.

That said, you do need to be working in SQL Server Management Studio (SSMS) to follow along. The reason we’re using SSMS is because of additional limitations with Always Encrypted. To carry out all the tasks that go with enabling Always Encrypted, we must use either the SSMS interface or PowerShell. We can carry out some of these tasks with T-SQL alone, but not all of them, and even SQL Server Data Tools (SSDT) can’t help us here.

For this reason, the article focuses primarily on how to use the SSMS interface to implement Always Encrypted, with a smattering of T-SQL thrown in for good measure. We’ll leave PowerShell for a different article. Note that Microsoft strongly recommends you configure Always Encrypted on a computer separate from where the database resides to prevent the keys from leaking to the server environment. For testing purposes, however, we can use a single machine, as I’ve done for these examples.

Run the Always Encrypted wizard

The simplest way to implement Always Encrypted is to run the SSMS Always Encrypted wizard, which steps you through the process of applying encryption to one or more existing columns within a database’s tables. To launch the wizard, right-click the database in Object Explorer, point to Tasks, and then click Encrypt Columns. By default, when you first launch the wizard, it displays the Introduction page, as shown in the following figure.

There’s nothing you need to do on this page, unless you don’t want it to appear next time you launch the wizard. In that case, you can select the Do not show this page again check box. When you’re ready to continue, click Next.

On the Column Selection page, you select the check box associated with each column you want to encrypt, as shown in the following figure. For this example, we’ll be encrypting the LoginID and NetID columns.

For each column that will be encrypted, we must select the encryption type, which can be one of two options:

• Deterministic: Always generates the same encrypted value for a given plain text value, making it possible to use the column for equality joins, point lookups, grouping, and indexing. This method is not as secure as randomizing the values because an unauthorized user might be able to guess the encrypted values by examining data patterns, especially for columns that support a small set of possible values, such as True and False.
• Randomized: Generates encrypted values in a less predictable manner, making it more difficult for users to infer values from the data patterns. However, this also means you cannot use the column for such operations as equality joins, point lookups, grouping, or indexing.

In addition to the encryption type, we must also specify the column encryption key for each column. For this, we can use one that has already been created, or we can have the wizard do the work. Because we haven’t yet defined an encryption key on this database, we’ll let the wizard create the key, which means sticking with the default option, CEK_Auto1 (New).

You might have noticed that when you configured your columns, a warning icon appeared with each one. If you hover over the icon, you’ll see the message in the following figure, which informs you that the collation will be changed to Latin1_General_BIN2. We must use this collation when encrypting string data.

After you’ve configure the columns you want to encrypt, click Next to advance to the Master Key Configuration page, shown in the following figure. Here you specify the details the column master key, which is used to protect the column encryption key.

Once again, we’re going to let the wizard do the work by allowing it to create the master key, which we’ll save to the Windows certificate store for the current user. If we have already created a master key, we can use that. We can also make use of the Azure Key Vault, instead of the local Windows certificate store. To use the Azure Key Vault service, we must provide the necessary login credentials.

For now, however, we’re keeping things simple by letting the wizard generate the key and adding it to the local certificate store, so click Next to advance to the Run Settings page, where you can choose to set up encryption immediately or generate a PowerShell script to run later, as shown in the following figure.

In this case, we’re going to stick with the default option, Proceed to finish now, but just so you know what you’d be getting with the PowerShell script, I’ve included a copy here:

The script loads the SqlServer module and provides the commands necessary to implement Always Encrypted on your target database. Although we’re not getting into the specifics of PowerShell in this article, know that anything we do in SSMS related to Always Encrypted, we can also do in PowerShell.

With that in mind, let’s return to the wizard. Click Next to advance to the Summary page, which should look similar to the one shown in the following figure.

The page describes the steps that will be taken to implement Always Encrypted: creating the master key, creating the encryption key, and encrypting the two columns. Click Finish to complete the process.

If all goes well, your Results page should eventually look like the one in the next figure, with each step having successfully completed.

That’s all there is to implementing Always Encrypted on our columns. Keep in mind, however, that we were working with a very small set of data. Applying encryption to existing data in this way can take a fair amount of time, depending on the amount of data. In addition, you should not perform write operations against the table during the encryption process because of the potential for data loss.

View encrypted data

Now that we have our columns encrypted, let’s look at what happens when we try to run the following SELECT statement within an SSMS query tab:

As it turns out, the LoginID and NatID columns are returned as encrypted values, as shown in the following table.

EmpID	JobTitle	LoginID	NatID
1	Chief Executive Officer	0x015B48DB2AE3AB143C4F61DEB44F2CDF53A23EBABBA71117C1AD29D7FEE6A9EF0E6715749613B219D870C3826C6C32C80DFDEB95986EA6F3120B164F71811614A4F44FECF2CB875A8B1DCC79DC6696DB397A458826E4C0F78E3FEC50A21CE826E6	0x01334FD67127650B72E06A627A0C07B96181B912EC63994044730AACB0EDA4F2C30B10BAEBEDC9ADAA75D4B2101B9D2F28D7B0552F3A2D054D5631B87D7519AAE529E9645FECADD3E0D371851E8A57E144
2	Vice President of Engineering	0x01B1EC7731FB28D28D752C1DCACE8C5DB14E3F0374B80EFEF14893C78E098C07D80F0907984EC249D456B4EFA5AE416B93D4CFF09B0CEE3615A57A2C3424753E86FE37042D14157A5420EC88CD6486B8B4A3C3E0CACB85F943F64143E9AB5484D9	0x017C8987711ECF4B9C1C3FC06895B3DFA044F1EA055E826BA6696618F7CD3B3336DC3910BD4A71FDC3082F6BB7AF021B883266A1CB457400F4CC26E51676FF90E7263508D5CB69D130E7BF163742DBC6EA
3	Engineering Manager	0x01C7F7DB8AB66D08B3C3F5EA1C71A46B6A5F15364D47AB15ED97BE93D867EF91D783B263AFF72FC2B9730C85F105280A071225CF3376D693CD2AF9D39EEA63D6EC02D896D712C95FAEF9478D5D8F1EDFDAE42AE255B5E1E60F9563FF4BA9AC40390438FF87BC1AAEEC72CEE6AE4A026665	0x019F6BFABF4BB6A153E4A36208527CF120FF199E29322A0406C1B023C11B4B78E65EC5ED37B23514F847EB2D1C30392BFD6EA315EE3DC8F58428399A2DD3E33C2DE987AF0F6FD92B72572FD6B98233B5EF
4	Senior Tool Designer	0x018177E520A82993F8A7D995FB73E757B5971B3E39EB65AF8BD2C6C90F1C2841A074586C9CEAAEB087FA5CA0E7F895EE1A737A846CA4559C70C7E6F5EDC61F4FAA5DEE81D745F096CF16465C96B2E1087A6389EDCA4F51B00D2274F4035D69FAA2	0x01E4AAC5D85F398DD922FB9DCF25B075F31E280C7740E1DF28C89AC1288DB81C6B778C27175B5CC4D718A8C044E81EB55131066AA4892898B210663204263CEBF7328DD479D9BCF4C9E18C813A9036912D
5	Design Engineer	0x01C4D0486D164ED495516C15F56437C69D084DE1AAC9F8DC2CCB2040B30DCF87BD88A8E3BBECC4EEB71FF9EE3937EAE2B00FA8D998CFFC521A9F2C772543988E90B5CD17E89CBA6C0711F22DC99E6780F406147E79A9D29774D6037BBA2CF70568	0x011E0F5E4C3B53EE699BC8DF7FA945EEA20E647033B260B24C482B6C58A483A7D91140ACF5CB08DC239D6427FF1992714ADAC13BCCEB8C59EFD31AADA5D70BD7913796899D88D043B6412A711D12CEF67D

You might have expected the values in the LoginID and NatID columns to have been returned as clear text because of the built-in support for Always Encrypted in SSMS. However, we must take another step: enable Always Encrypted on the connection itself.

To enable the connection, right-click the query tab in SSMS, point to Connection, and then click Change Connection. This launches the Connect to Database Engine dialog box. Here you must click Options and then go to the Additional Connection Parameters tab, as shown in the following figure.

On this tab, type the following value in the large text box:

After you type the setting, click Connect, and then rerun the SELECT statement. Your results should now look similar to those in the following table.

EmpID	JobTitle	LoginID	NatID
1	Chief Executive Officer	adventure-works\ken0	295847284
2	Vice President of Engineering	adventure-works\terri0	245797967
3	Engineering Manager	adventure-works\roberto0	509647174
4	Senior Tool Designer	adventure-works\rob0	112457891
5	Design Engineer	adventure-works\gail0	695256908

What all this points to is that any client application you use to access encrypted data must incorporate a driver that supports Always Encrypted. In addition, that driver must be enabled to handle the encryption and decryption operations.

For example, SSMS uses the .NET Framework Data Provider for SQL Server driver, which has been updated to support Always Encrypted. However, we still need to explicitly enable encryption on the connection for the driver to be able to view the data as clear text. The data remains encrypted within the database and in transit until the driver decrypts it on behalf of the SSMS client, making it possible to view the data on our query tab.

View key and certificate information

After we’ve created our column master key and encryption key, we can view information about them in the target database. For example, the following SELECT statement retrieves data about the master key from the sys.column_master_keys catalog view:

The query returns the key name and ID, the name of the certificate store, and the path within the store, as shown in the following table.

KeyName	KeyID	KeyStore	KeyPath
CMK_Auto1	1	MSSQL_CERTIFICATE_STORE	CurrentUser/my/B27A4A9FCC37F2C5B1807249FE1285CD4A40B88F

The first thing to note is that the database stores only metadata about the master key. The key itself is saved to the Windows certificate store, as indicated by the MSSQL_CERTIFICATE_STORE value.

The format of the KeyPath value is specific to the type of certificate store and tells us where to find the key within the store. In this case, the path specifies that the certificate is saved to the personal section of the Current User store and provides the key’s thumbprint, a hexadecimal string that uniquely identifies the certificate. The following figure shows how the certificate is listed on my system, with the Certificate dialog box opened.

Now that we’ve verified the existence of the master key certificate, let’s move on to the column encryption key. One way we can view information about the encryption key is to use the sys.column_encryption_keys catalog view:

In this case, all we’re pulling from this view is the name and ID, as shown in the following results, but we can also retrieve the created and modified dates if we need them.

KeyName	KeyID
CEK_Auto1	1

Another catalog view we can use to get information about our column encryption keys is the sys.column_encryption_keys_values view:

This view provides us with the IDs of both the encryption key and the master key used to encrypt the encryption key. The view also returns the algorithm used to encrypt the column encryption key (which is always RSA_OAEP) and the actual encrypted value of that key, as shown in the following table.

ColumnKeyID	MasterKeyID	EncryptValue	EncryptAlgorithm
1	1	0x016E000001630075007200720065006E00740075007300650072002F006D0079002F003600310032003600320033003200360065003300320063006200380030003900390030003300340063003000640031003400630030003500610036006300630062006300360035006100340065003500BF3F003560F5F19C01E23A2AAEE9B4883C4E653A69012DDCB859246AE7B05E0DE98A2A71D2490990D65828A4A0354A163B0F96AB4F8C9B9437D71F28D084B934001EE50893B10E5949EBC506450C3BB5D57F5152532D7A9EF752E6E010E6AC7110913CC5A17D6958D66AE5F1ABA65881BF5295AEE58B3B1BA9DD22B20566492B03EAD1D84BDC43190B5135119791D0C7CBA6D08886544C191DE52D6CD64669826396AC7A41A0C8EA29483D0140196F6DCB3620EF5B02680F9483F6E734DE1345D33ABBD2EF3778D79B5560FF3AD4475F21650390C3C83C7800D2F5F1F25B43DEB9927B9329BAD256138C6E9179D849DC234F289D887B6AEE193C56665EA76E467312DD842C1D3E16C418667EE5B4B38ECE782DEE325F5929AF722244E2845CBCCE65FBF99585C891904038DB45B2FF72403312B67C0BD716C51244A4AD128830990A82F7D543834E1D36147299FD804E0787ABB2B97AF8CDEF38326C7944501AE8CDD09774F4AC6DD999099D628D57F97CCD296279214A7CB758AC416CF8D2D208D232946013FBC865560174D532F5913613DA756C4293C131C02C8F7DDABEDCD3E13E147C7D0DF92C4C77B7CA68C643FD78402CE73D2EA34C8279F051818F81DD4F3DCF4200ADA3CE6FC8FA2550C74274E7F91C692CE0DE95F5723C704D991D6E3202099015B4A46804034E7155D34A5828E1C8AC6F785468D424113BDF7331	RSA_OAEP

As noted earlier, the column encryption key is itself always encrypted within SQL Server, but we can use the sys.column_encryption_keys_values view to get at that value.

Create a column master key

Earlier in the article, when we applied Always Encrypted to our columns, we used the SSMS Always Encrypted wizard, which not only made it easy, but also allowed us to do something we cannot do with T-SQL statement, that is, apply Always Encrypted to existing data.

However, we can use T-SQL to create a column master key. For example, suppose we had not used the wizard and were starting from scratch in our shiny new database. We can use a CREATE COLUMN MASTER KEY statement to create a master key, as shown in the following example:

In this case, we’re again specifying that we use the Windows certificate store. However, unlike the Always Encrypted wizard, the CREATE COLUMN MASTER KEY statement also allows us to create a master key that points to a store, such as a hardware security module (HSM), that supports the Microsoft CryptoAPI or Cryptography API: Next Generation, in addition to Azure Key Vault.

If we were to run the above statement as is, we would create a master key named AEMasterKey that points to an existing certificate in the Windows certificate store. The KEY_PATH value provides a pointer to the certificate within the store.

The implication of this is that a certificate must exist within the certificate store prior to running the statement. If you don’t have a certificate in place and are looking for a simple way to get around this (at least for testing purposes), you can once again turn to the SSMS interface.

In Object Explorer, expand the Security node under the EmpData3 database, and then expand the Always Encrypted Keys node. Right-click Column Master Keys and then click New Column Master Key. This launches the New Column Master Key dialog box shown in the following figure

To create the master key, you need only provide a name for the key, select the certificate store, and select a certificate. The nice part about this feature is that it provides more certificate store options than the wizard, such as being able to a store that uses an HSM. Plus, you can generate a certificate simply by clicking the Generate Certificate button.

That’s all there is to creating a master key. You can then query the sys.column_master_keys catalog view to verify the key was created:

The statement should return at least one row that contains the master key metadata, as shown in the following table.

KeyName	KeyID	KeyStore	KeyPath
AEMasterKey	1	MSSQL_CERTIFICATE_STORE	CurrentUser/my/B27A4A9FCC37F2C5B1807249FE1285CD4A40B88F

Once you have your master key, you can use it when you run the Always Encrypted wizard or when creating a columns master key, which is our next task to tackle.

Create a column encryption key

As with the master key, we can use T-SQL to create a column encryption key, using the CREATE COLUMN ENCRYPTION KEY statement, as shown in the following example:

In addition to specifying the master key and algorithm when creating the encryption key, you must also provide the encrypted value for the key, which is where things get a little tricky. You must come up with a method for encrypting the encryption key value and then passing it into the CREATE COLUMN ENCRYPTION KEY statement.

Microsoft documentation is not very helpful in terms of explaining the best way to go about getting the encrypted value. One MSDN blog has this to say: “The encrypted value is assumed to be produced by encrypting the plaintext of the column encryption key using the specified column master key and the specified algorithm.”

Assumptions aside, you still need a way to generate that value. One approach is to use the SSMS or PowerShell to generate another column encryption key and then copy its value. An easier approach is to again turn to the SSMS interface.

Once again, go into Object Explorer, expand the Security node under the EmpData3 database, and then expand the Always Encrypted Keys node. Right-click Column Encryption Keys and then click New Column Encryption Key. This launches the New Column Encryption Key dialog box, shown in the following figure.

All we need to do here is provide a name for the encryption key and specify the master key to use to encrypt the encryption key, which in this case, is AEMasterKey, the key we just created. SSMS takes care of generating the encrypted key value.

After you finish with this, you can run the sys.column_encryption_keys or sys.column_encryption_keys_values catalog view to verify that you key has been created. You can then use the key in the Always Encrypted wizard to encrypt existing data, or you can create a table that uses the key, which is what we’re going to do next.

Create a table with an encrypted column

Although you cannot use T-SQL to encrypt existing data, you can use T-SQL to define a column that incorporates Always Encrypted protection. For example, the following CREATE TABLE statement uses Always Encrypted to encrypt the NatID column:

As you can see, the column definition specifies the collation (Latin1_General_BIN2), name of the column encryption key (AEColumnKey), encryption type (Randomized), and algorithm (AEAD_AES_256_CBC_HMAC_SHA_256), which is the only algorithm we can use when applying Always Encrypted to a column. After you run the CREATE TABLE statement, you should be good to go.

But here’s where things get tricky in SSMS. You cannot simply insert data into the encrypted column. For example, if you were to try to run the following statement, you would generate an error, whether or not you enabled Always Encrypted on the connection:

The error message you’ll receive takes the following form:

According to Microsoft, any value that targets an encrypted column must be encrypted inside the client application, otherwise you’ll get an error. The application must perform that encryption based on the requirements of the Always Encrypted architecture. In other words, it is up to the client application to prepare the data before sending it off to SQL Server, whether inserting or updating data. Again, refer to Microsoft documentation for details about the client side of the equation.

If you get desperate and need to add a row from within SSMS, you can add values directly through the SSMS interface. To do so, right-click the table in Object Explorer and then click Edit Top 200 Rows. You’ll then be presented with a grid, where you can manually add one row at a time. Not a great solution, but it’s something.

You can also use T-SQL in SSMS to delete rows with encrypted data, as well as view that data. But that’s about all you can do.

The brave new world of Always Encrypted

Always Encrypted helps you ensure that only approved applications and individuals can access sensitive data. The encryption and decryption processes remain outside of SQL Server’s purview. Only those with access to the column master key in the certificate store can access the data as plain text.

For the most part, implementing Always Encrypted on the SQL Server side is a relatively straightforward process. The real work happens in the client application, which must use an approved driver and be configured to use that driver before making use of the Always Encrypted feature.

The best part of Always Encrypted is that it is now available to all editions of SQL Server 2016, starting with SP1. That means organizations interested in this feature can give it a try without having to license a product they don’t want. Again, it comes down to the client application and the investment needed to modify the application. Even so, for organizations already invested in the SQL Server universe, Always Encrypted might be worth a try, especially if they’ve already made the SQL Server 2016 leap or plan to do so in the near future.