Data is very important asset. If it’s stolen or “misplaced” then that can cost a fortune to the company. SQL Server provides encryption that can avoid the embarrassment and legal issues if data is stolen. Here we will discuss two important levels at which SQL Server supports encryption. In this part 1 of the blog post we will explore column level encryption. In Part 2 we will explorer encryption at the entire database level.
1. Data Encryption (column level encryption)
You can decide and select the columns that are of utmost importance and encrypt the data for those columns. Benefit is that if you have low end server then encrypting and decrypting hand picked columns won’t hurt the performance. Example of candidate columns for encryption would be password, credit card number, SIN number (Canadian, eh ), account number, PIN number etc.
Let’s create a sample database and table and apply encryption at column level
2: GO3: USE Bank4: GO6: (8: AccountNumber varchar(50),9: EncryptedAccountNumber varbinary(128)10: )11: GO12:
AccountNumber will contain the data without any encryption where as EncryptedAccountNumber will contain the encrypted version of AccountNumber.
Now our next step is to create master key. Master key will be used for creating digital certificate and certificate will be used for generating the symmetric key that will finally be used for encrypting data in individual columns.
Create master key:2: PASSWORD = 'Pa$$w0rd'
Create digital certificate1: CREATE CERTIFICATE BankCert2: WITH SUBJECT = 'Account Numbers';3: GO
Create symmetric key for encrypting data
For encrypting the data we use the EncryptByKey function and in order to use this function we have to first open the symmetric key that we created in one of the previous steps. Let’s insert some records:2: DECRYPTION BY CERTIFICATE BankCert;3:4: --insert original and encrypted values6: VALUES7: ('123456789', EncryptByKey(Key_GUID('BankAccountKey'), '123456789')),8: ('987654321', EncryptByKey(Key_GUID('BankAccountKey'), '987654321'))Let’s query the table and find out how the encrypted data looks like:
In the 3rd column we can see that Account Number is encrypted. Original data occupies 9 bytes but encrypted data is occupying 68 bytes. There’s overhead involved due to encryption.
Now how do we decrypt and read the data? Good question. Actually it’s quite similar to encryption. We use DecryptByKey function and before that we have to open the symmetric key that was used for encrypting the data. Let’s do it2: DECRYPTION BY CERTIFICATE BankCert;3: GO4:5: --list original and decrypted values6: SELECT AccountNumber, EncryptedAccountNumber,len(EncryptedAccountNumber) AS Size, CONVERT(varchar, DecryptByKey(EncryptedAccountNumber)) AS DecryptedAccountNumber FROM Account
And that’s about it.
Column level security is useful for securing hand picked columns. DBA’s will have to make sure that they encrypt the certificates and keys so that we can recover the encrypted data in case of a disaster. Developers are responsible for writing the code that encrypts and decrypts the data. Actually it’s pretty straight forward to create stored procedures that encrypts and decrypts the data.