AdventureWorks2014<\/code> sample database installed. I used the following T-SQL script to create a database, add a table to the database, and populate the table with data from the AdventureWorks2014<\/code> database:<\/p>\nUSE master;\r\nGO\r\nCREATE DATABASE EmpData;\r\nUSE master;\r\nGO\r\n\r\nCREATE DATABASE EmpData;\r\nGO\r\n\r\nUSE EmpData;\r\nGO\r\n\r\nCREATE TABLE EmpInfo(\r\n EmpID INT PRIMARY KEY,\r\n NatID NVARCHAR(15) NOT NULL,\r\n EncryptedID VARBINARY(128) NULL);\r\nGO\r\n\r\nINSERT INTO EmpInfo(EmpID, NatID) \r\nSELECT BusinessEntityID, NationalIDNumber\r\nFROM AdventureWorks2014.HumanResources.Employee\r\nWHERE NationalIDNumber IS NOT NULL;\r\n<\/pre>\nThe BusinessEntityID<\/code> and NationalIDNumber<\/code> columns in the Employee<\/code> table of the AdventureWorks2014<\/code> database provide the data for the EmpID<\/code> and NatID<\/code> columns in the EmpInfo<\/code> table. The table\u2019s EncryptedID<\/code> column is empty for now, but that\u2019s where we\u2019ll store our encrypted values, which will be based on the values in the NatID<\/code> column. (Normally we would not leave the NatID<\/code> data in an unencrypted state, but we\u2019re doing so here so we can easily verify our efforts.)<\/p>\nIf you don\u2019t have the AdventureWorks2014<\/code> database installed on your system, you can still follow along with the examples in this article. Just create the EmpInfo<\/code> table and add a few rows of sample data to the EmpID<\/code> and NatID<\/code> columns.<\/p>\nOnce we have our test environment up and running, we must prepare the EmpData<\/code> database to support column-level encryption. Only then can we encrypt the EncryptedID<\/code> column. The following steps outline the process we\u2019ll follow to complete these tasks:<\/p>\n\n- Create the database master key (DMK)<\/li>\n
- Create a self-signed certificate<\/li>\n
- Create a symmetric key<\/li>\n
- Encrypt the column data<\/li>\n
- Query the encrypted data<\/li>\n<\/ol>\n
This process takes advantage of SQL Server\u2019s encryption hierarchy and key management infrastructure to deliver a layered architecture made up of a public key certificate and several symmetric keys. Each layer encrypts the layer below it, using the security mechanisms built into SQL Server.<\/p>\n
Create the database master key<\/h1>\n
Encrypting a column starts with creating the DMK, if you haven\u2019t already done so. The DMK is a symmetric key used primarily to protect the private keys in the database\u2019s certificates and asymmetric keys. Since SQL Server 2012, the DMK has been encrypted with the 256-bit AES algorithm. Prior to that, it was the Triple DES algorithm.<\/p>\n
Within a database, the DMK represents the top layer of the encryption hierarchy, but this is not the entire picture. SQL Server uses the service master key (SMK) and a user-supplied password to protect the DMK. The SMK sits above the DMK in the encryption hierarchy, at the server level.<\/p>\n
The SMK is a symmetric key generated the first time a SQL Server instance is started. Once created, the SMK can be used to encrypt credentials, linked server passwords, and the DMK in each database.<\/p>\n
SQL Server uses the Windows Data Protection API (DPAPI) to create and encrypt the SMK, which puts the DPAPI at a hierarchical level above the SMK. The following figure provides an overview of how these pieces fit together, with the DPAPI at the top of the hierarchy, followed by the SMK, and then by the DMK, which protects the certificates and asymmetric keys below it.<\/p>\n
![\"..\/..\/..\/..\/..\/..\/..\/..\/..\/Documents\/DataFiles\/Screenshots\/SqlSr\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/12\/documents-datafiles-scr.png\")
<\/p>\n
The hierarchy shown here represents only part of the entire encryption picture, but it includes all the pieces that go into encrypting a column of data, at least when taking the approach we\u2019re taking in this article. There are other methods we can use for encrypting data, such as implementing an Extensible Key Management (EKM) module, but for now we\u2019ll focus on the method reflected in the figure.<\/p>\n
Because Windows and SQL Server take care of the top two levels of the hierarchy, we can turn our attention to the next level: the DMK. (We\u2019ll get into the lower levels in a bit.) To create the DMK in the EmpData<\/code> database, we can use the following CREATE<\/code> MASTER<\/code> KEY<\/code> statement:<\/p>\nUSE EmpData;\r\nGO\r\n\r\nCREATE MASTER KEY \r\nENCRYPTION BY PASSWORD = 'pw1234!'\r\n<\/pre>\nThe statement itself is simple enough. Each element is required and there are no optional arguments from which to choose. We need only add the password. In this case, I used a very simple password for demonstration purposes. We would of course want to provide a much stronger password under normal circumstances.<\/p>\n
We can verify that the DMK key has been created by querying the sys.symmetric_keys<\/code> catalog view in the EmpData<\/code> database, as shown in the following SELECT<\/code> statement:<\/p>\nSELECT name KeyName,\r\n symmetric_key_id KeyID,\r\n key_length KeyLength,\r\n algorithm_desc KeyAlgorithm\r\nFROM sys.symmetric_keys;\r\n<\/pre>\nGiven that we just created the EmpData<\/code> database, the statement should return only a single row, as shown in the following results.<\/p>\n\n\n\n\nKeyName<\/b><\/p>\n<\/td>\n
\nKeyID<\/b><\/p>\n<\/td>\n
\nKeyLength<\/b><\/p>\n<\/td>\n
\nKeyAlgorithm<\/b><\/p>\n<\/td>\n<\/tr>\n\n\n##MS_DatabaseMasterKey##<\/p>\n<\/td>\n
\n101<\/p>\n<\/td>\n
\n256<\/p>\n<\/td>\n
\nAES_256<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
SQL Server automatically names the DMK ##MS_DatabaseMasterKey##<\/code> and encrypts it with the 256-bit AES algorithm.<\/p>\nThat\u2019s all there is to creating the DMK. SQL Server automatically uses the SMK in conjunction with the password to protect the DMK. Note, however, that after you create the DMK, you should use a BACKUP<\/code> MASTER<\/code> KEY<\/code> statement to back up the DMK and then store the backup in a secure, off-site location.<\/p>\nCreate a self-signed certificate<\/h1>\n
The next level down in our encryption hierarchy is the asymmetric encryption mechanism, which can take the form of a certificate or asymmetric key. Each one uses a private key and public key to provide asymmetric encryption, with the public key encrypting the data and the private key decrypting it. By default, SQL Server uses the DMK to encrypt the private key.<\/p>\n
Certificates and asymmetric keys are fairly comparable, in terms of their ability to protect data. The main advantage of a certificate is that it can contain more information than the asymmetric key, such as an issuer or expiration data. The primary purpose of either type in SQL Server is to encrypt the symmetric keys used to protect the actual data at the next level down the encryption hierarchy.<\/p>\n
For this article, we\u2019ll define a certificate, rather than an asymmetric key. A certificate is a digitally signed statement that binds the public and private keys. In SQL Server, it is a database-level securable that is based on the X.509 standard and supports X.509 V1 fields.<\/p>\n
Normally, a certificate authority (CA) issues and signs a certificate, which we can then incorporate into our encryption infrastructure. However, SQL Server also supports the ability to create a self-signed certificate, a handy alternative when we\u2019re developing and testing functionality and want to bypass the CA, like we\u2019re doing now.<\/p>\n
To create a self-signed certificate, we can use the CREATE<\/code> CERTIFICATE<\/code> statement, providing a name for the certificate and a pseudo-issuer, as shown in the following statement:<\/p>\nCREATE CERTIFICATE Cert1\r\nWITH SUBJECT = 'Employee national IDs';\r\n<\/pre>\nI\u2019ve named the certificate Cert1<\/code> and included a WITH<\/code> SUBJECT<\/code> clause, which specifies the issuer name, Employee<\/code> national<\/code> IDs<\/code>. In a self-signed certificate, the issuer name serves primarily as a description for the certificate. In addition, I\u2019ve created the certificate without specifying a password, which means SQL Server will automatically use the DMK to protect the private key.<\/p>\nWe can now verify that the certificate has been created by querying the sys.certificates<\/code> catalog view:<\/p>\nSELECT name CertName,\r\n certificate_id CertID,\r\n pvt_key_encryption_type_desc EncryptType,\r\n issuer_name Issuer\r\nFROM sys.certificates;\r\n<\/pre>\nThe following table shows the results returned by the SELECT<\/code> statement.<\/p>\n\n\n\n\nCertName<\/b><\/p>\n<\/td>\n
\nCertID<\/b><\/p>\n<\/td>\n
\nEncryptType<\/b><\/p>\n<\/td>\n
\nIssuer<\/b><\/p>\n<\/td>\n<\/tr>\n\n\nCert1<\/p>\n<\/td>\n
\n256<\/p>\n<\/td>\n
\nENCRYPTED_BY_MASTER_KEY<\/p>\n<\/td>\n
\nEmployee national IDs<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Notice that the EncryptType<\/code> column has returned the value ENCRYPTED_BY_MASTER_KEY<\/code>, which confirms that the DMK is being used to protect the certificate. Also notice that the CertName<\/code> and Issuer<\/code> values match what we specified in our statement.<\/p>\nSQL Server generated the certificate ID (the CertID<\/code> value) when we created the certificate. The certificate ID is unique within the database, although it\u2019s not clear why SQL Server started with the value 256<\/code>. (I re-created the database and certificate several times and SQL Server always assigned the value 256<\/code> to the first certificate, incrementing by one for additional certificates.)<\/p>\nThe CREATE<\/code> CERTIFICATE<\/code> statement supports far more complex syntax than what we\u2019ve shown here. For example, can add an expiration date, create a certificate based on a certificate file, retrieve the private key from a file, or use a password to encrypt the file, rather than the DMK.<\/p>\nAs with the DMK, you should back up any certificates you create. For this, you can use the BACKUP<\/code> CERTIFICATE<\/code> statement to export the certificate to a file.<\/p>\nCreate a symmetric key<\/h1>\n
The final encryption layer we need to create is a symmetric key, which sits directly above the data in the encryption hierarchy. Unlike an asymmetric key, the symmetric key uses only one key to encrypt and decrypt the data.<\/p>\n
Symmetric keys tend to perform faster than asymmetric encryption, which is why Microsoft recommends that you use only symmetric keys to encrypt the actual data. That said, asymmetric encryption provides a higher level of security than symmetric encryption. By utilizing a layered approach to data encryption, we can take advantage of the protection we get from asymmetric encryption, but still realize the benefits of symmetric encryption for the actual data.<\/p>\n
To generate a symmetric key, we can use a CREATE<\/code> SYMMETRIC<\/code> KEY<\/code> statement to define a symmetric key that is encrypted with the Cert1<\/code> certificate we created earlier, as shown in the following T-SQL script:<\/p>\nCREATE SYMMETRIC KEY SymKey1\r\nWITH ALGORITHM = AES_256\r\nENCRYPTION BY CERTIFICATE Cert1;\r\n<\/pre>\nThe statement names the symmetric key SymKey1<\/code> and specifies that the 256-bit AES algorithm be used to encrypt the column data. SQL Server supports numerous other algorithms; however, starting with SQL Server 2016, all algorithms have been deprecated except 128-bit AES, 192-bit AES, and 256-bit AES, the one we\u2019re using and the strongest of the three. (The higher the number of bits, the stronger the algorithm.)<\/p>\nThe statement\u2019s ENCRYPTION<\/code> BY<\/code> CERTIFICATE<\/code> clause specifies that the Cert1<\/code> certificate be used to encrypt the symmetric key. If desired, we can instead specify another symmetric key, an asymmetric key, a password, or an EKM module to encrypt the new symmetric key. In fact, we can use multiple encryption mechanisms concurrently to protect our symmetric key, depending on the level of protection we want to achieve and the performance and maintenance overhead we\u2019re willing to incur.<\/p>\nAfter we\u2019ve created our symmetric key, we can once again use the sys.symmetric_keys<\/code> catalog view in the EmpData<\/code> database to verify that the key has been added:<\/p>\nSELECT name KeyName,\r\n symmetric_key_id KeyID,\r\n key_length KeyLength,\r\n algorithm_desc KeyAlgorithm\r\nFROM sys.symmetric_keys;\r\n<\/pre>\nNow our results include both the DMK and the new SymKey1<\/code> symmetric key, as shown in the following table.<\/p>\n\n\n\n\nKeyName<\/b><\/p>\n<\/td>\n
\nKeyID<\/b><\/p>\n<\/td>\n
\nKeyLength<\/b><\/p>\n<\/td>\n
\nKeyAlgorithm<\/b><\/p>\n<\/td>\n<\/tr>\n\n\n##MS_DatabaseMasterKey##<\/p>\n<\/td>\n
\n101<\/p>\n<\/td>\n
\n256<\/p>\n<\/td>\n
\nAES_256<\/p>\n<\/td>\n<\/tr>\n
\n\nSymKey1<\/p>\n<\/td>\n
\n256<\/p>\n<\/td>\n
\n256<\/p>\n<\/td>\n
\nAES_256<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
With the creation of the symmetric key, we have our encryption hierarchy in place and ready to go. The DPAPI protects the SMK. The SMK protects the DMK. The DMK protects the Cert1<\/code> certificate. The certificate protects the SymKey1<\/code> symmetric key. Now we can use the symmetric key to protect the actual data.<\/p>\nEncrypt the data<\/h1>\n
Encrypting the data is a relatively straightforward process that requires only three simple T-SQL statements:<\/p>\n
\n- An
OPEN<\/code> SYMMETRIC<\/code> KEY<\/code> statement to decrypt the symmetric key and make it available to our session<\/li>\n- An
UPDATE<\/code> statement that uses the ENCRYPTBYKEY<\/code> system function to encrypt the data<\/li>\n- A
CLOSE<\/code> SYMMETRIC<\/code> KEY<\/code> statement to close the symmetric key so it\u2019s no longer available to the session<\/li>\n<\/ol>\nThe following T-SQL script demonstrates how to write the three statements to encrypt the EncryptedID<\/code> column, using data from the NatID<\/code> column:<\/p>\nOPEN SYMMETRIC KEY SymKey1\r\nDECRYPTION BY CERTIFICATE Cert1;\r\n\r\nUPDATE EmpInfo\r\nSET EncryptedID = ENCRYPTBYKEY(KEY_GUID('SymKey1'), NatID);\r\n\r\nCLOSE SYMMETRIC KEY SymKey1;\r\n<\/pre>\nThe OPEN<\/code> SYMMETRIC<\/code> KEY<\/code> statement should be self-explanatory. It calls the SymKey1<\/code> symmetric key, using the Cert1<\/code> certificate to decrypt the key. In this way, the key is available throughout our current session.<\/p>\nThe UPDATE<\/code> statement sets the EncryptedID<\/code> values based on the NatID<\/code> values, using the ENCRYPTBYKEY<\/code> function to encrypt the values with the SymKey1<\/code> symmetric key. The statement also uses the KEY_GUID<\/code> system function to retrieve the key\u2019s GUID, which is then passed into the ENCRYPTBYKEY<\/code> function.<\/p>\nWe follow the UPDATE<\/code> statement with a CLOSE<\/code> SYMMETRIC<\/code> KEY<\/code> statement to explicitly close the symmetric key so we don\u2019t leave it lingering out there. Otherwise, the key remains available until the session has been terminated.<\/p>\nThat\u2019s all there is to performing the actual encryption. The EncryptedID<\/code> column should now populated and the values protected by the encryption hierarchy we\u2019ve put into place.<\/p>\nQuery the encrypted data<\/h1>\n
We can query the EmpInfo<\/code> table to verify that the data appears correct. This requires only a simple SELECT<\/code> statement:<\/p>\nSELECT TOP 5 * FROM EmpInfo;<\/pre>\nIn this case, I\u2019m querying only the first five rows, which should be enough to demonstrate what\u2019s going on. The following table shows the results I received on my system.<\/p>\n
\n\n\n\nEmpID<\/b><\/p>\n<\/td>\n
\nNatID<\/b><\/p>\n<\/td>\n
\nEncryptedID<\/b><\/p>\n<\/td>\n<\/tr>\n\n\n1<\/p>\n<\/td>\n
\n295847284<\/p>\n<\/td>\n
\n0x00F3E5341DE5A94DA6121750553DC9AC01000000D492F7588822FA1720BF01E6CFAFD1C885AA39CBEE6A397B593206CEFF32FFAECCE98DE127F3C1818AE5DC9480DB8A5E<\/p>\n<\/td>\n<\/tr>\n
\n\n2<\/p>\n<\/td>\n
\n245797967<\/p>\n<\/td>\n
\n0x00F3E5341DE5A94DA6121750553DC9AC0100000080B8D4DA39A5AD0A61E5538516A6F513F022FCED2D6741E8B0DEE723B94D23A70880D888A77747BD601D9EAF649DAC79<\/p>\n<\/td>\n<\/tr>\n
\n\n3<\/p>\n<\/td>\n
\n509647174<\/p>\n<\/td>\n
\n0x00F3E5341DE5A94DA6121750553DC9AC010000009D7BA49B4BB46CF86282EC15F0388246E7A19191D8FBC7490FAEE267938304377F5FB24852A54450968D3038C4E8A767<\/p>\n<\/td>\n<\/tr>\n
\n\n4<\/p>\n<\/td>\n
\n112457891<\/p>\n<\/td>\n
\n0x00F3E5341DE5A94DA6121750553DC9AC010000005CAD125D96F196BCF1F9F14310DB29887B62F084F848CFFD6D6AC5D6CF211CD188141A1254A9684C121FE362562CD7E3<\/p>\n<\/td>\n<\/tr>\n
\n\n5<\/p>\n<\/td>\n
\n695256908<\/p>\n<\/td>\n
\n0x00F3E5341DE5A94DA6121750553DC9AC01000000AE23D1BD2DEE7A811F98956E1E4E54FDAA7E34F4697170EA7EEA103E627420BD9F8A73A34B2F3D41ADCB840E41C4FA8A<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
As you can see, the EncryptedID<\/code> values are returned as binary values that represent the encrypted data. If we want to see the actual values, we must again open our symmetric key and then use the DECRYPTBYKEY<\/code> system function to decrypt the data when we retrieve it, as shown in the following T-SQL script:<\/p>\nOPEN SYMMETRIC KEY SymKey1\r\nDECRYPTION BY CERTIFICATE Cert1;\r\n\r\nSELECT TOP 5 EmpID, NatID,\r\n CONVERT(NVARCHAR(15), DECRYPTBYKEY(EncryptedID)) DecryptedID\r\nFROM EmpInfo;\r\n\r\nCLOSE SYMMETRIC KEY SymKey1;\r\n<\/pre>\nIf we were to run the SELECT<\/code> statement as is, without opening the symmetric key, the DecryptedID<\/code> column would return only null values because the DECRYPTBYKEY<\/code> function cannot decrypt the data. However, when we open the symmetric key, the SELECT<\/code> statement returns the results shown in the following table.<\/p>\n\n\n\n\nEmpID<\/b><\/p>\n<\/td>\n
\nNatID<\/b><\/p>\n<\/td>\n
\nDecryptedID<\/b><\/p>\n<\/td>\n<\/tr>\n\n\n1<\/p>\n<\/td>\n
\n295847284<\/p>\n<\/td>\n
\n295847284<\/p>\n<\/td>\n<\/tr>\n
\n\n2<\/p>\n<\/td>\n
\n245797967<\/p>\n<\/td>\n
\n245797967<\/p>\n<\/td>\n<\/tr>\n
\n\n3<\/p>\n<\/td>\n
\n509647174<\/p>\n<\/td>\n
\n509647174<\/p>\n<\/td>\n<\/tr>\n
\n\n4<\/p>\n<\/td>\n
\n112457891<\/p>\n<\/td>\n
\n112457891<\/p>\n<\/td>\n<\/tr>\n
\n\n5<\/p>\n<\/td>\n
\n695256908<\/p>\n<\/td>\n
\n695256908<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
As we would hope, the DecryptedID<\/code> values match the NatID<\/code> values. Out column is encrypted until we explicitly open our symmetric key and decrypt the data.<\/p>\nWorking with encrypted data<\/h1>\n
Once you have a basic understanding of how to create an encryption hierarchy to protect column data, you\u2019ll be able to implement it as needed. Keep in mind, however, that the approach we\u2019ve taken here is only one way we can encrypt column data. For example, we can instead use the symmetric key without a certificate or asymmetric key, use a symmetric key in conjunction with an EKM module, or use a symmetric key with a password-protected certificate, rather than a DMK-protected certificate.<\/p>\n
You\u2019ll have to determine the best approach for your system based on the amount of data you have, your security and performance requirements, how the data will be accessed, and the other security mechanisms you already have in place.<\/p>\n
You will also need to determine whether column-level encryption is the best solution for your circumstances. It could be that the TDE or Always Encrypted features will serve you better. Before you make any decisions, you might want to wait until you\u2019ve had a chance to review the subsequent articles in this series, when we look at some of the other methods you can use to encrypt SQL Server data.<\/p>\n","protected":false},"excerpt":{"rendered":"
SQL Server Encryption is an essential part of what is required for protecting data. Column-level encryption can be a very effective way of doing this. In the first in a series of articles on the theme of SQL Server Encryption, Robert Sheldon once more makes it all seem easy.…<\/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":[143531],"tags":[4275,4619],"coauthors":[6779],"class_list":["post-69372","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-encryption","tag-security"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/69372","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=69372"}],"version-history":[{"count":9,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/69372\/revisions"}],"predecessor-version":[{"id":91257,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/69372\/revisions\/91257"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=69372"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=69372"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=69372"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=69372"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}