AdventureWorks2014<\/strong> database (installed on a local instance of SQL Server 2016). To create these objects, I used the following T-SQL script:<\/p>\nUSE master;\r\nGO\r\n\r\nCREATE DATABASE EmpData3;\r\nGO\r\n\r\nUSE EmpData3;\r\nGO\r\n\r\nCREATE TABLE EmpInfo(\r\n EmpID INT PRIMARY KEY,\r\n JobTitle NVARCHAR(50) NOT NULL,\r\n LoginID NVARCHAR(256) NOT NULL,\r\n NatID NVARCHAR(15) NOT NULL);\r\nGO\r\n\r\nINSERT INTO EmpInfo(EmpID, JobTitle, LoginID, NatID) \r\nSELECT BusinessEntityID, JobTitle, LoginID, NationalIDNumber\r\nFROM AdventureWorks2014.HumanResources.Employee\r\nWHERE NationalIDNumber IS NOT NULL;\r\n<\/pre>\nYou 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.<\/p>\n
That said, you do need to be working in SQL Server Management Studio (SSMS) to follow along. The reason we\u2019re 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\u2019t help us here.<\/p>\n
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\u2019ll 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\u2019ve done for these examples.<\/p>\n
Run the Always Encrypted wizard<\/h2>\n
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\u2019s tables. To launch the wizard, right-click the database in Object Explorer, point to Tasks<\/strong>, and then click Encrypt<\/strong> Columns<\/strong>. By default, when you first launch the wizard, it displays the Introduction<\/strong> page, as shown in the following figure.<\/p>\n![\"..\/..\/..\/..\/..\/..\/..\/..\/..\/Documents\/DataFiles\/Screenshots\/wiza\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/02\/documents-datafiles-scr.png\")
<\/p>\n
There\u2019s nothing you need to do on this page, unless you don\u2019t want it to appear next time you launch the wizard. In that case, you can select the Do<\/strong> not<\/strong> show<\/strong> this<\/strong> page<\/strong> again<\/strong> check box. When you\u2019re ready to continue, click Next<\/strong>.<\/p>\nOn the Column<\/strong> Selection<\/strong> page, you select the check box associated with each column you want to encrypt, as shown in the following figure. For this example, we\u2019ll be encrypting the LoginID<\/strong> and NetID<\/strong> columns.<\/p>\n![\"..\/..\/..\/..\/..\/..\/..\/..\/..\/Documents\/DataFiles\/Screenshots\/wizard_02a_Colu\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/02\/documents-datafiles-scr-1.png\")
<\/p>\n
For each column that will be encrypted, we must select the encryption type, which can be one of two options:<\/p>\n
\n- Deterministic:<\/strong> 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<\/strong> and False<\/strong>.<\/li>\n
- Randomized:<\/strong> 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.<\/li>\n<\/ul>\n
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\u2019t yet defined an encryption key on this database, we\u2019ll let the wizard create the key, which means sticking with the default option, CEK_Auto1<\/strong> (New)<\/strong>.<\/p>\nYou might have noticed that when you configured your columns, a warning icon appeared with each one. If you hover over the icon, you\u2019ll see the message in the following figure, which informs you that the collation will be changed to Latin1_General_BIN2<\/strong>. We must use this collation when encrypting string data.<\/p>\n![\"..\/..\/..\/..\/..\/..\/..\/..\/..\/Documents\/DataFiles\/Screenshots\/wizard_02b_Colla\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/02\/documents-datafiles-scr-2.png\")
<\/p>\n
After you\u2019ve configure the columns you want to encrypt, click Next<\/strong> to advance to the Master<\/strong> Key<\/strong> Configuration<\/strong> page, shown in the following figure. Here you specify the details the column master key, which is used to protect the column encryption key.<\/p>\n![\"..\/..\/..\/..\/..\/..\/..\/..\/..\/Documents\/DataFiles\/Screenshots\/wizard_03_MasterKeyCo\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/02\/documents-datafiles-scr-3.png\")
<\/p>\n
Once again, we\u2019re going to let the wizard do the work by allowing it to create the master key, which we\u2019ll 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.<\/p>\n
For now, however, we\u2019re keeping things simple by letting the wizard generate the key and adding it to the local certificate store, so click Next<\/strong> to advance to the Run<\/strong> Settings<\/strong> page, where you can choose to set up encryption immediately or generate a PowerShell script to run later, as shown in the following figure.<\/p>\n![\"..\/..\/..\/..\/..\/..\/..\/..\/..\/Documents\/DataFiles\/Screenshots\/wizard_04_\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/02\/documents-datafiles-scr-4.png\")
<\/p>\n
In this case, we\u2019re going to stick with the default option, Proceed<\/strong> to<\/strong> finish<\/strong> now<\/strong>, but just so you know what you\u2019d be getting with the PowerShell script, I\u2019ve included a copy here:<\/p>\n# Generated by SQL Server Management Studio at 12:33 PM on 1\/31\/2017\r\n Import-Module SqlServer\r\n # Load reflected assemblies\r\n [reflection.assembly]::LoadwithPartialName('System.Data.SqlClient') | Out-Null\r\n [reflection.assembly]::LoadwithPartialName('Microsoft.SQLServer.SMO') | Out-Null\r\n [reflection.assembly]::LoadwithPartialName('Microsoft.SQLServer.ConnectionInfo') | Out-Null\r\n # Set up connection and database SMO objects\r\n $sqlConnectionString = 'Data Source=localhost\\sqlsrv16;Integrated Security=True;MultipleActiveResultSets=False;Encrypt=False;TrustServerCertificate=False;Packet Size=4096;Application Name=\"Microsoft SQL Server Management Studio\"'\r\n $sqlConnection = New-Object 'System.Data.SqlClient.SqlConnection' $sqlConnectionString\r\n $serverConnection = New-Object 'Microsoft.SqlServer.Management.Common.ServerConnection' $sqlConnection\r\n $smoServer = New-Object 'Microsoft.SqlServer.Management.Smo.Server' $serverConnection\r\n $smoDatabase = $smoServer.Databases['EmpData3']\r\n # If your encryption changes involve keys in Azure Key Vault, uncomment one of the lines below in order to authenticate:\r\n # * Prompt for a username and password:\r\n #Add-SqlAzureAuthenticationContext -Interactive\r\n # * Enter a Client ID, Secret, and Tenant ID:\r\n #Add-SqlAzureAuthenticationContext -ClientID '<Client ID>' -Secret '<Secret>' -Tenant '<Tenant ID>'\r\n # Change encryption schema\r\n $encryptionChanges = @()\r\n # Add changes for table [dbo].[EmpInfo]\r\n $encryptionChanges += New-SqlColumnEncryptionSettings -ColumnName dbo.EmpInfo.LoginID -EncryptionType Deterministic -EncryptionKey AEColumnKey\r\n $encryptionChanges += New-SqlColumnEncryptionSettings -ColumnName dbo.EmpInfo.NatID -EncryptionType Randomized -EncryptionKey AEColumnKey\r\n Set-SqlColumnEncryption -ColumnEncryptionSettings $encryptionChanges -InputObject $smoDatabase<\/pre>\nThe script loads the SqlServer module and provides the commands necessary to implement Always Encrypted on your target database. Although we\u2019re 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.<\/p>\n
With that in mind, let\u2019s return to the wizard. Click Next<\/strong> to advance to the Summary<\/strong> page, which should look similar to the one shown in the following figure.<\/p>\n![\"..\/..\/..\/..\/..\/..\/..\/..\/..\/Documents\/DataFiles\/Screenshots\/wizard\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/02\/documents-datafiles-scr-5.png\")
<\/p>\n
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<\/strong> to complete the process.<\/p>\nIf all goes well, your Results<\/strong> page should eventually look like the one in the next figure, with each step having successfully completed.<\/p>\n![\"..\/..\/..\/..\/..\/..\/..\/..\/..\/Documents\/DataFiles\/Screenshots\/wizard\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/02\/documents-datafiles-scr-6.png\")
<\/p>\n
That\u2019s 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.<\/p>\n
View encrypted data<\/h2>\n
Now that we have our columns encrypted, let\u2019s look at what happens when we try to run the following SELECT<\/strong> statement within an SSMS query tab:<\/p>\nUSE EmpData3;\r\n GO\r\n SELECT TOP 5 * FROM EmpInfo;<\/pre>\nAs it turns out, the LoginID<\/strong> and NatID<\/strong> columns are returned as encrypted values, as shown in the following table.<\/p>\n\n\n\n| EmpID<\/td>\n | JobTitle<\/td>\n | LoginID<\/td>\n | NatID<\/td>\n<\/tr>\n<\/thead>\n |
\n\n| 1<\/td>\n | Chief Executive Officer<\/td>\n | 0x015B48DB2AE3AB143C4F61DEB44F2CDF53A23EBABBA71117C1AD29D7FEE6A9EF0E6715749613B219D870C3826C6C32C80DFDEB95986EA6F3120B164F71811614A4F44FECF2CB875A8B1DCC79DC6696DB397A458826E4C0F78E3FEC50A21CE826E6<\/td>\n | 0x01334FD67127650B72E06A627A0C07B96181B912EC63994044730AACB0EDA4F2C30B10BAEBEDC9ADAA75D4B2101B9D2F28D7B0552F3A2D054D5631B87D7519AAE529E9645FECADD3E0D371851E8A57E144<\/td>\n<\/tr>\n |
\n| 2<\/td>\n | Vice President of Engineering<\/td>\n | 0x01B1EC7731FB28D28D752C1DCACE8C5DB14E3F0374B80EFEF14893C78E098C07D80F0907984EC249D456B4EFA5AE416B93D4CFF09B0CEE3615A57A2C3424753E86FE37042D14157A5420EC88CD6486B8B4A3C3E0CACB85F943F64143E9AB5484D9<\/td>\n | 0x017C8987711ECF4B9C1C3FC06895B3DFA044F1EA055E826BA6696618F7CD3B3336DC3910BD4A71FDC3082F6BB7AF021B883266A1CB457400F4CC26E51676FF90E7263508D5CB69D130E7BF163742DBC6EA<\/td>\n<\/tr>\n |
\n| 3<\/td>\n | Engineering Manager<\/td>\n | 0x01C7F7DB8AB66D08B3C3F5EA1C71A46B6A5F15364D47AB15ED97BE93D867EF91D783B263AFF72FC2B9730C85F105280A071225CF3376D693CD2AF9D39EEA63D6EC02D896D712C95FAEF9478D5D8F1EDFDAE42AE255B5E1E60F9563FF4BA9AC40390438FF87BC1AAEEC72CEE6AE4A026665<\/td>\n | 0x019F6BFABF4BB6A153E4A36208527CF120FF199E29322A0406C1B023C11B4B78E65EC5ED37B23514F847EB2D1C30392BFD6EA315EE3DC8F58428399A2DD3E33C2DE987AF0F6FD92B72572FD6B98233B5EF<\/td>\n<\/tr>\n |
\n| 4<\/td>\n | Senior Tool Designer<\/td>\n | 0x018177E520A82993F8A7D995FB73E757B5971B3E39EB65AF8BD2C6C90F1C2841A074586C9CEAAEB087FA5CA0E7F895EE1A737A846CA4559C70C7E6F5EDC61F4FAA5DEE81D745F096CF16465C96B2E1087A6389EDCA4F51B00D2274F4035D69FAA2<\/td>\n | 0x01E4AAC5D85F398DD922FB9DCF25B075F31E280C7740E1DF28C89AC1288DB81C6B778C27175B5CC4D718A8C044E81EB55131066AA4892898B210663204263CEBF7328DD479D9BCF4C9E18C813A9036912D<\/td>\n<\/tr>\n |
\n| 5<\/td>\n | Design Engineer<\/td>\n | 0x01C4D0486D164ED495516C15F56437C69D084DE1AAC9F8DC2CCB2040B30DCF87BD88A8E3BBECC4EEB71FF9EE3937EAE2B00FA8D998CFFC521A9F2C772543988E90B5CD17E89CBA6C0711F22DC99E6780F406147E79A9D29774D6037BBA2CF70568<\/td>\n | 0x011E0F5E4C3B53EE699BC8DF7FA945EEA20E647033B260B24C482B6C58A483A7D91140ACF5CB08DC239D6427FF1992714ADAC13BCCEB8C59EFD31AADA5D70BD7913796899D88D043B6412A711D12CEF67D<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n You might have expected the values in the LoginID<\/strong> and NatID<\/strong> 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.<\/p>\nTo enable the connection, right-click the query tab in SSMS, point to Connection<\/strong>, and then click Change<\/strong> Connection<\/strong>. This launches the Connect<\/strong> to<\/strong> Database<\/strong> Engine<\/strong> dialog box. Here you must click Options<\/strong> and then go to the Additional<\/strong> Connection<\/strong> Parameters<\/strong> tab, as shown in the following figure.<\/p>\n![\"..\/..\/..\/..\/..\/..\/..\/..\/..\/Documents\/DataFiles\/Screenshots\/Connect\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/02\/documents-datafiles-scr-7.png\") <\/p>\n
On this tab, type the following value in the large text box:<\/p>\n Column Encryption Setting=Enabled<\/pre>\nAfter you type the setting, click Connect<\/strong>, and then rerun the SELECT<\/strong> statement. Your results should now look similar to those in the following table.<\/p>\n\n\n\n| EmpID<\/td>\n | JobTitle<\/td>\n | LoginID<\/td>\n | NatID<\/td>\n<\/tr>\n<\/thead>\n | \n\n| 1<\/td>\n | Chief Executive Officer<\/td>\n | adventure-works\\ken0<\/td>\n | 295847284<\/td>\n<\/tr>\n | \n| 2<\/td>\n | Vice President of Engineering<\/td>\n | adventure-works\\terri0<\/td>\n | 245797967<\/td>\n<\/tr>\n | \n| 3<\/td>\n | Engineering Manager<\/td>\n | adventure-works\\roberto0<\/td>\n | 509647174<\/td>\n<\/tr>\n | \n| 4<\/td>\n | Senior Tool Designer<\/td>\n | adventure-works\\rob0<\/td>\n | 112457891<\/td>\n<\/tr>\n | \n| 5<\/td>\n | Design Engineer<\/td>\n | adventure-works\\gail0<\/td>\n | 695256908<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n 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.<\/p>\n 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.<\/p>\n View key and certificate information<\/h2>\nAfter we\u2019ve created our column master key and encryption key, we can view information about them in the target database. For example, the following SELECT<\/strong> statement retrieves data about the master key from the sys.column_master_keys<\/strong> catalog view:<\/p>\nSELECT name KeyName,\r\n column_master_key_id KeyID,\r\n key_store_provider_name KeyStore,\r\n key_path KeyPath\r\nFROM sys.column_master_keys;\r\n<\/pre>\nThe 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.<\/p>\n \n\n\n| KeyName<\/td>\n | KeyID<\/td>\n | KeyStore<\/td>\n | KeyPath<\/td>\n<\/tr>\n<\/thead>\n | \n\n| CMK_Auto1<\/td>\n | 1<\/td>\n | MSSQL_CERTIFICATE_STORE<\/td>\n | CurrentUser\/my\/B27A4A9FCC37F2C5B1807249FE1285CD4A40B88F<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n 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<\/strong> value.<\/p>\nThe format of the KeyPath<\/strong> 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\u2019s thumbprint, a hexadecimal string that uniquely identifies the certificate. The following figure shows how the certificate is listed on my system, with the Certificate<\/strong> dialog box opened.<\/p>\n![\"..\/..\/..\/..\/..\/..\/..\/..\/..\/Documents\/DataFiles\/Screenshots\/Certi\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/02\/documents-datafiles-scr-8.png\") <\/p>\n
Now that we\u2019ve verified the existence of the master key certificate, let\u2019s 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<\/strong> catalog view:<\/p>\nSELECT name KeyName,\r\n column_encryption_key_id KeyID\r\nFROM sys.column_encryption_keys;\r\n<\/pre>\nIn this case, all we\u2019re 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.<\/p>\n \n\n\n| KeyName<\/td>\n | KeyID<\/td>\n<\/tr>\n<\/thead>\n | \n\n| CEK_Auto1<\/td>\n | 1<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n Another catalog view we can use to get information about our column encryption keys is the sys.column_encryption_keys_values<\/strong> view:<\/p>\nSELECT column_encryption_key_id ColumnKeyID,\r\n column_master_key_id MasterKeyID,\r\n encrypted_value EncryptValue,\r\n encryption_algorithm_name EncryptAlgorithm\r\nFROM sys.column_encryption_key_values;\r\n<\/pre>\nThis 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<\/strong>) and the actual encrypted value of that key, as shown in the following table.<\/p>\n\n\n\n| ColumnKeyID<\/td>\n | MasterKeyID<\/td>\n | EncryptValue<\/td>\n | EncryptAlgorithm<\/td>\n<\/tr>\n<\/thead>\n | \n\n| 1<\/td>\n | 1<\/td>\n | \n0x016E000001630075007200720065006E00740075007300650072002F006D0079002F003600310032003600320033003200360065003300320063006200380030003900390030003300340063003000640031003400630030003500610036006300630062006300360035006100340065003500BF3F003560F5F19C01E23A2AAEE9B4883C4E653A69012DDCB859246AE7B05E0DE98A2A71D2490990D65828A4A0354A163B0F96AB4F8C9B9437D71F28D084B934001EE50893B10E5949EBC506450C3BB5D57F5152532D7A9EF752E6E010E6AC7110913CC5A17D6958D66AE5F1ABA65881BF5295AEE58B3B1BA9DD22B20566492B03EAD1D84BDC43190B5135119791D0C7CBA6D08886544C191DE52D6CD64669826396AC7A41A0C8EA29483D0140196F6DCB3620EF5B02680F9483F6E734DE1345D33ABBD2EF3778D79B5560FF3AD4475F21650390C3C83C7800D2F5F1F25B43DEB9927B9329BAD256138C6E9179D849DC234F289D887B6AEE193C56665EA76E467312DD842C1D3E16C418667EE5B4B38ECE782DEE325F5929AF722244E2845CBCCE65FBF99585C891904038DB45B2FF72403312B67C0BD716C51244A4AD128830990A82F7D543834E1D36147299FD804E0787ABB2B97AF8CDEF38326C7944501AE8CDD09774F4AC6DD999099D628D57F97CCD296279214A7CB758AC416CF8D2D208D232946013FBC865560174D532F5913613DA756C4293C131C02C8F7DDABEDCD3E13E147C7D0DF92C4C77B7CA68C643FD78402CE73D2EA34C8279F051818F81DD4F3DCF4200ADA3CE6FC8FA2550C74274E7F91C692CE0DE95F5723C704D991D6E3202099015B4A46804034E7155D34A5828E1C8AC6F785468D424113BDF7331<\/td>\n | RSA_OAEP<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n As noted earlier, the column encryption key is itself always encrypted within SQL Server, but we can use the sys.column_encryption_keys_values<\/strong> view to get at that value.<\/p>\nCreate a column master key<\/h2>\nEarlier 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.<\/p>\n 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<\/strong> COLUMN<\/strong> MASTER<\/strong> KEY<\/strong> statement to create a master key, as shown in the following example:<\/p>\nCREATE COLUMN MASTER KEY AEMasterKey \r\nWITH ( \r\n KEY_STORE_PROVIDER_NAME = 'MSSQL_CERTIFICATE_STORE', \r\n KEY_PATH = 'CurrentUser\/my\/B27A4A9FCC37F2C5B1807249FE1285CD4A40B88F');\r\n<\/pre>\nIn this case, we\u2019re again specifying that we use the Windows certificate store. However, unlike the Always Encrypted wizard, the CREATE<\/strong> COLUMN<\/strong> MASTER<\/strong> KEY<\/strong> 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.<\/p>\nIf we were to run the above statement as is, we would create a master key named AEMasterKey<\/strong> that points to an existing certificate in the Windows certificate store. The KEY_PATH<\/strong> value provides a pointer to the certificate within the store.<\/p>\nThe implication of this is that a certificate must exist within the certificate store prior to running the statement. If you don\u2019t 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.<\/p>\n In Object Explorer, expand the Security<\/strong> node under the EmpData3<\/strong> database, and then expand the Always<\/strong> Encrypted<\/strong> Keys<\/strong> node. Right-click Column<\/strong> Master<\/strong> Keys<\/strong> and then click New<\/strong> Column<\/strong> Master<\/strong> Key<\/strong>. This launches the New<\/strong> Column<\/strong> Master<\/strong> Key<\/strong> dialog box shown in the following figure<\/p>\n![\"..\/..\/..\/..\/..\/..\/..\/..\/..\/Documents\/DataFiles\/Screenshots\/Crea\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/02\/documents-datafiles-scr-9.png\") <\/p>\n
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<\/strong> Certificate<\/strong> button.<\/p>\nThat\u2019s all there is to creating a master key. You can then query the sys.column_master_keys<\/strong> catalog view to verify the key was created:<\/p>\nSELECT name KeyName,\r\n column_master_key_id KeyID,\r\n key_store_provider_name KeyStore,\r\n key_path KeyPath\r\nFROM sys.column_master_keys;\r\n<\/pre>\nThe statement should return at least one row that contains the master key metadata, as shown in the following table.<\/p>\n \n\n\n| KeyName<\/td>\n | KeyID<\/td>\n | KeyStore<\/td>\n | KeyPath<\/td>\n<\/tr>\n<\/thead>\n | \n\n| AEMasterKey<\/td>\n | 1<\/td>\n | MSSQL_CERTIFICATE_STORE<\/td>\n | CurrentUser\/my\/B27A4A9FCC37F2C5B1807249FE1285CD4A40B88F<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n 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.<\/p>\n Create a column encryption key<\/h2>\nAs with the master key, we can use T-SQL to create a column encryption key, using the CREATE<\/strong> COLUMN<\/strong> ENCRYPTION<\/strong> KEY<\/strong> statement, as shown in the following example:<\/p>\nCREATE COLUMN ENCRYPTION KEY AEColumnKey \r\nWITH VALUES \r\n( \r\n COLUMN_MASTER_KEY = AEMasterKey, \r\n ALGORITHM = 'RSA_OAEP', \r\n ENCRYPTED_VALUE = 0x016E000001630075007200720065006E00740075007300650072002F006D0079002F00620032003700610034006100390066006300630033003700660032006300350062003100380030003700320034003900660065003100320038003500630064003400610034003000620038003800660053FD933BC3E3E6E5FFD935F452A5C4113FF56E4D946D78B22A69415FF8EF69D9B3A5541F2463BBC32D06AC88AE95B4CDBBEE7A9D1DD80043D7C900F28917637F4414565CB3F2B29CEEE5C03DF182C4F62395CDAD59A59BFCBD421889DB9EFB2B5250AA597268011B8ACCFFA7A1B5D846BD476BBD8F8239D2681C800E3BCD848485AEC6E69FE76D06D2E213FB36FCBCA5E8B75FE67D21C1C05EB7CF819AD9F96701116A2B642F690455FC7DC48AEEB1825BB20ECD428F910C002EE3D186706E00F76C608EF78FBB147ABA798309092517A39C9C4031B3857C599B238174AA1E8433A649D63D194278B0A4EFBF15DF4E4B5B4468FB73FC8992B3E34606AB306E2E19BADEE4B38288FF77B9A8E45A56BB321091EF0CF3567076ED27D875286CB2232177F610B9A0DAEBFA34ABA9856A094E26E995987AD050D27954DDB08BED9A34C6D19CBE6B2271A7E716C33850DB8781C9D3B762C0920EED57BB9D2BA581F7AC1A46EA55962200FD26405FE31005D413BA5B624E5AF2770377A13EB68FB681242B8B719499175113E84073013BDC6E03E5F82EC070B9151705F1C564106B93E3C7566E41BAD00209AB4587278640FE225F797DD9BB83284E8A674DFC7F48558441E00BC856161FC93A38E337B050915450E7B0ED848CDB63272B65319B26B45119ED081852DEBE53DFF7A6CD21935FC3CBF2C4852AD01CFF0153B76C196F7667 \r\n);\r\n<\/pre>\nIn 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<\/strong> COLUMN<\/strong> ENCRYPTION<\/strong> KEY<\/strong> statement.<\/p>\nMicrosoft 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: \u201cThe 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.\u201d<\/p>\n 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.<\/p>\n Once again, go into Object Explorer, expand the Security<\/strong> node under the EmpData3<\/strong> database, and then expand the Always<\/strong> Encrypted<\/strong> Keys<\/strong> node. Right-click Column<\/strong> Encryption<\/strong> Keys<\/strong> and then click New<\/strong> Column<\/strong> Encryption<\/strong> Key<\/strong>. This launches the New<\/strong> Column<\/strong> Encryption<\/strong> Key<\/strong> dialog box, shown in the following figure.<\/p>\n![\"..\/..\/..\/..\/..\/..\/..\/..\/..\/Documents\/DataFiles\/Screenshots\/Crea\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/02\/documents-datafiles-scr-10.png\") <\/p>\n
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<\/strong>, the key we just created. SSMS takes care of generating the encrypted key value.<\/p>\nAfter you finish with this, you can run the sys.column_encryption_keys<\/strong> or sys.column_encryption_keys_values<\/strong> 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\u2019re going to do next.<\/p>\nCreate a table with an encrypted column<\/h2>\nAlthough 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<\/strong> TABLE<\/strong> statement uses Always Encrypted to encrypt the NatID<\/strong> column:<\/p>\nUSE EmpData3;\r\nGO\r\nCREATE TABLE EmpNatID(\r\n EmpID INT PRIMARY KEY,\r\n NatID NVARCHAR(15) \r\n COLLATE Latin1_General_BIN2 \r\n ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = AEColumnKey, \r\n ENCRYPTION_TYPE = Randomized, \r\n ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL);\r\n<\/pre>\nAs you can see, the column definition specifies the collation (Latin1_General_BIN2<\/strong>), name of the column encryption key (AEColumnKey<\/strong>), encryption type (Randomized<\/strong>), and algorithm (AEAD_AES_256_CBC_HMAC_SHA_256<\/strong>), which is the only algorithm we can use when applying Always Encrypted to a column. After you run the CREATE<\/strong> TABLE<\/strong> statement, you should be good to go.<\/p>\nBut here\u2019s 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:<\/p>\n USE EmpData3;\r\n GO\r\n INSERT INTO EmpNatID(EmpID, NatID) VALUES(1, '295847284');<\/pre>\nThe error message you\u2019ll receive takes the following form:<\/p>\n Operand type clash: varchar is incompatible with varchar(8000) encrypted with (encryption_type = 'RANDOMIZED', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'AEColumnKey', column_encryption_key_database_name = 'EmpData3') collation_name = 'SQL_Latin1_General_CP1_CI_AS'<\/pre>\nAccording to Microsoft, any value that targets an encrypted column must be encrypted inside the client application, otherwise you\u2019ll 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.<\/p>\n 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<\/strong> Top<\/strong> 200<\/strong> Rows<\/strong>. You\u2019ll then be presented with a grid, where you can manually add one row at a time. Not a great solution, but it\u2019s something.<\/p>\nYou can also use T-SQL in SSMS to delete rows with encrypted data, as well as view that data. But that\u2019s about all you can do.<\/p>\n The brave new world of Always Encrypted<\/h2>\nAlways Encrypted helps you ensure that only approved applications and individuals can access sensitive data. The encryption and decryption processes remain outside of SQL Server\u2019s purview. Only those with access to the column master key in the certificate store can access the data as plain text.<\/p>\n 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.<\/p>\n 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\u2019t 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\u2019ve already made the SQL Server 2016 leap or plan to do so in the near future.<\/p>\n","protected":false},"excerpt":{"rendered":" Is ‘Always Encrypted’ SQL Server 2016’s most widely important new feature? It is significant that ‘Always Encrypted’ in SQL Server is in all editions of SQL Server. Because of the increasing importance of encryption to data governance, it allows encryption for the sensitive application data for everywhere beyond the application’s client connection, including network, server, database and storage. Robert Sheldon explains what it is, why you should try it out, and how to set about it.…<\/p>\n","protected":false},"author":221841,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143527,143531],"tags":[],"coauthors":[6779],"class_list":["post-69859","post","type-post","status-publish","format-standard","hentry","category-database-administration-sql-server","category-t-sql-programming-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/69859","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221841"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=69859"}],"version-history":[{"count":24,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/69859\/revisions"}],"predecessor-version":[{"id":91254,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/69859\/revisions\/91254"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=69859"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=69859"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=69859"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=69859"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}} | | | | | |