SQL SERVER 2017 HOSTING – How to Use SQL Server Encryption with Symmetric Keys?
|You can quickly and securely encrypt data in SQL Server 2005+ by using the native Symmetric Keys functionality. The most common encryption algorithms symmetric key encryption supports are Des, Triple Des, RC4 128bit, AES 128bit and AES 256bit.
Setup your database
To create a symmetric key, we first need to setup our database with a master key and a certificate, which act as protectors of our symmetric key store.
Create a Database Master Key CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘myStrongPassword’
Create a Certificate
1 2 |
<span class="pln">CREATE CERTIFICATE </span><span class="typ">MyCertificateName</span><span class="pln"> WITH SUBJECT </span><span class="pun">=</span> <span class="str">'A label for this certificate'</span> |
Create your Symmetric Key
Now that we have setup our database, we can add the symmetric key to our certificate. To ensure we can replicate the key on another server, or rebuild the key if it is corrupted, you must very safely keep note of the KEY_SOURCE and IDENTITY_VALUE parameters, as these are what is used to create the key.
Create a Symmetric Key
1 2 3 4 5 |
<span class="pln">CREATE SYMMETRIC KEY </span><span class="typ">MySymmetricKeyName</span><span class="pln"> WITH IDENTITY_VALUE </span><span class="pun">=</span> <span class="str">'a fairly secure name'</span><span class="pun">,</span><span class="pln"> ALGORITHM </span><span class="pun">=</span><span class="pln"> AES_256</span><span class="pun">,</span><span class="pln"> KEY_SOURCE </span><span class="pun">=</span> <span class="str">'a very secure strong password or phrase'</span><span class="pln"> ENCRYPTION BY CERTIFICATE </span><span class="typ">MyCertificateName</span><span class="pun">;</span> |
The IDENTITY_VALUE parameter is used to generate the guid for the key and the KEY_SOURCE is used to generate the actual key. This allows you to run the above code on any server as many times as you like, with the same values, to generate the exact same key.
I’ve chosen AES_256, but you can choose from the following encryption algorithms: DES, TRIPLE_DES, RC2, RC4, RC4_128, DESX, AES_128, AES_192, and AES_256.
Encrypting and Decrypting Data
Finally, we can encrypt and decrypt data using the key we have just created by using the following snippets of code.
Open the Key
Before you can start encrypting or decrypting data, you must first initialize the key. This is done with the following piece of code.
1 2 |
<span class="pln">OPEN SYMMETRIC KEY </span><span class="typ">MySymmetricKeyName</span><span class="pln"> DECRYPTION BY CERTIFICATE </span><span class="typ">MyCertificateName</span> |
Encrypting data
You can encrypt data by using the EncryptByKey function, like so:
1 2 |
<span class="pln">DECLARE </span><span class="lit">@Result</span><span class="pln"> varbinary</span><span class="pun">(</span><span class="lit">256</span><span class="pun">)</span><span class="pln"> SET </span><span class="lit">@Result</span> <span class="pun">=</span> <span class="typ">EncryptByKey</span><span class="pun">(</span><span class="typ">Key_GUID</span><span class="pun">(</span><span class="str">'MySymmetricKeyName'</span><span class="pun">),</span> <span class="lit">@ValueToEncrypt</span><span class="pun">)</span> |
Note that the result of the above encryption is of type varbinary(256), and if you would like to store the value in a column to use this type.
Decrypting Data
You can decrypt data by using the DecryptByKey function, like so:
1 2 |
<span class="pln">DECLARE </span><span class="lit">@Result</span><span class="pln"> varchar</span><span class="pun">(</span><span class="pln">max</span><span class="pun">)</span><span class="pln"> SET </span><span class="lit">@Result</span> <span class="pun">=</span> <span class="typ">DecryptByKey</span><span class="pun">(</span><span class="lit">@ValueToDecrypt</span><span class="pun">)</span> |
Make sure you decrypt to the same type that you encrypted in the first place. In my example I encrypted a varchar(max), so I also decrypted to a varchar(max).
Using Symmetric Keys in a Function
Because symmetric keys use time based sessions, you cannot open them inside a function, however you can get around this by opening them first with a Stored Procedure, and then calling the function. Here’s an Example of the setup I have going.
The OpenKeys Stored Procedure
1 2 3 4 5 6 7 8 9 10 11 12 13 |
<span class="pln">CREATE PROCEDURE </span><span class="typ">OpenKeys</span><span class="pln"> AS </span><span class="kwd">BEGIN</span><span class="pln"> SET NOCCOUNT ON</span><span class="pun">;</span> <span class="kwd">BEGIN</span><span class="pln"> TRY OPEN SYMMETRIC KEY </span><span class="typ">MySymmetricKeyName</span><span class="pln"> DECRYPTION BY CERTIFICATE </span><span class="typ">MyCertificateName</span> <span class="kwd">END</span><span class="pln"> TRY </span><span class="kwd">BEGIN</span><span class="pln"> CATCH </span><span class="pun">--</span> <span class="typ">Handle</span><span class="pln"> non</span><span class="pun">-</span><span class="pln">existant key here </span><span class="kwd">END</span><span class="pln"> CATCH </span><span class="kwd">END</span> |
The Encrypt Function
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
<span class="pln">CREATE FUNCTION </span><span class="typ">Encrypt</span> <span class="pun">(</span> <span class="lit">@ValueToEncrypt</span><span class="pln"> varchar</span><span class="pun">(</span><span class="pln">max</span><span class="pun">)</span> <span class="pun">)</span><span class="pln"> RETURNS varbinary</span><span class="pun">(</span><span class="lit">256</span><span class="pun">)</span><span class="pln"> AS </span><span class="kwd">BEGIN</span> <span class="pun">--</span> <span class="typ">Declare</span><span class="pln"> the </span><span class="kwd">return</span><span class="pln"> variable here DECLARE </span><span class="lit">@Result</span><span class="pln"> varbinary</span><span class="pun">(</span><span class="lit">256</span><span class="pun">)</span><span class="pln"> SET </span><span class="lit">@Result</span> <span class="pun">=</span> <span class="typ">EncryptByKey</span><span class="pun">(</span><span class="typ">Key_GUID</span><span class="pun">(</span><span class="str">'MySymmetricKeyName'</span><span class="pun">),</span> <span class="lit">@ValueToEncrypt</span><span class="pun">)</span> <span class="pun">--</span> <span class="typ">Return</span><span class="pln"> the result of the </span><span class="kwd">function</span><span class="pln"> RETURN </span><span class="lit">@Result</span> <span class="kwd">END</span> |
The Decrypt Function
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
<span class="pln">CREATE FUNCTION </span><span class="typ">Decrypt</span> <span class="pun">(</span> <span class="lit">@ValueToDecrypt</span><span class="pln"> varbinary</span><span class="pun">(</span><span class="lit">256</span><span class="pun">)</span> <span class="pun">)</span><span class="pln"> RETURNS varchar</span><span class="pun">(</span><span class="pln">max</span><span class="pun">)</span><span class="pln"> AS </span><span class="kwd">BEGIN</span> <span class="pun">--</span> <span class="typ">Declare</span><span class="pln"> the </span><span class="kwd">return</span><span class="pln"> variable here DECLARE </span><span class="lit">@Result</span><span class="pln"> varchar</span><span class="pun">(</span><span class="pln">max</span><span class="pun">)</span><span class="pln"> SET </span><span class="lit">@Result</span> <span class="pun">=</span> <span class="typ">DecryptByKey</span><span class="pun">(</span><span class="lit">@ValueToDecrypt</span><span class="pun">)</span> <span class="pun">--</span> <span class="typ">Return</span><span class="pln"> the result of the </span><span class="kwd">function</span><span class="pln"> RETURN </span><span class="lit">@Result</span> <span class="kwd">END</span> |
An Example of How to Use Symmetric Keys in a Function
1 2 3 4 5 6 7 |
<span class="pln">EXEC </span><span class="typ">OpenKeys</span> <span class="pun">--</span> <span class="typ">Encrypting</span><span class="pln"> SELECT </span><span class="typ">Encrypt</span><span class="pun">(</span><span class="pln">myColumn</span><span class="pun">)</span><span class="pln"> FROM myTable </span><span class="pun">--</span> <span class="typ">Decrypting</span><span class="pln"> SELECT </span><span class="typ">Decrypt</span><span class="pun">(</span><span class="pln">myColumn</span><span class="pun">)</span><span class="pln"> FROM myTable</span> |
As long as you call the OpenKeys stored procedure in the same query as the function, it will work.