I was doing some work that required that some parts of the database were encrypted and couldn’t find an all-in-one location for information. If that’s what you’re looking for, this ain’t it either. I will, however, briefly explain how to create symmetric and asymmetric keys that use passwords.
First off, symmetric keys. Symmetric keys are faster than asymmetric keys by a long shot but are (arguably) less secure since they use the same password to encrypt/decrypt things. To create a symmetric key by password you do not need a database master key, they are for asymmetric keys and certificates only. When you create the key you can choose between 10 different encryption algorithms, DES | TRIPLE_DES | TRIPLE_DES_3KEY | RC2 | RC4 | RC4_128 | DESX | AES_128 | AES_192 | AES_256. You can read more about those here. To create the key just simply use:
CREATE SYMMETRIC KEY YourKeynameHere WITH ALGORITHM = AES_256 ENCRYPTION BY PASSWORD = N'YourPasswordHere'
For a sample table we’ll use the following:
CREATE TABLE MyTable ( Email varbinary(max) )
To use the key to insert data, first open the key, then insert using the ENCRYPTBYKEY function and finally close the key:
OPEN SYMMETRIC KEY YourKeynameHere DECRYPTION BY PASSWORD = N'YourPasswordHere' INSERT INTO MyTable (Email) VALUES (ENCRYPTBYKEY(KEY_GUID('YourKeynameHere'), 'test@example.com')) CLOSE SYMMETRIC KEY YourkeynameHere
It’s very important that you open the key first. Failure to do so will not result in an error, NULL data will just be inserted instead.
To read the data you perform a similar pattern except you use the DECRYPTBYKEY function which already knows which key to decrypt with (but still needs to be opened first). Also remember that DECRYPTBYKEY returns binary data so its up to you to convert it properly.
OPEN SYMMETRIC KEY YourKeynameHere DECRYPTION BY PASSWORD = N'YourPasswordHere' SELECT CONVERT(varchar(max), DECRYPTBYKEY(Email)) FROM MyTable CLOSE SYMMETRIC KEY YourkeynameHere
Now, if you want to use a symmetric key with a stored procedure but you don’t want to store the password within the sproc you’ll run into an issue. DDL statements don’t allow variables as parameters. What this means is that if you try creating the following sproc you’ll receive the error “Incorrect syntax near '@EncPass'.”
CREATE PROC MyProc ( @EncPass nvarchar(255) ) AS OPEN SYMMETRIC KEY YourKeynameHere DECRYPTION BY PASSWORD = @EncPass SELECT CONVERT(varchar(max), DECRYPTBYKEY(Email)) FROM MyTable CLOSE SYMMETRIC KEY YourKeynameHere
The only fix I’ve found is to use EXECUTE to run your open key for you. If you’re here, I assume (or hope) that you know the dangers of execute. So the above sproc becomes:
CREATE PROC MyProc ( @EncPass nvarchar(255) ) AS EXEC ('OPEN SYMMETRIC KEY YourKeynameHere DECRYPTION BY PASSWORD = N''' + @EncPass + '''') SELECT CONVERT(varchar(max), DECRYPTBYKEY(Email)) FROM MyTable CLOSE SYMMETRIC KEY YourKeynameHere
More information:
Okay, I said I’d go into asymmetric keys here too but that will have to wait for another day.