Well this is sixth article in the series of Encryption and Decryption. It was really good to have good response from all of you about this series. I would like to give links about past 5 article in these series for those who directly landed to this page.
Ok, so above links are well enough, if you want to get the concept of encryption – decryption in Microsoft SQL Server 2008 from scratch, do read all articles given above too from part 1.
Anyway, let us move ahead with Symmetric key. This is at the very bottom level in hierarchy of Encryption-Decryption. Further you can refer hierarchy chart given in my first article. Symmetric key can either use same key for encryption and decryption or use different key for encryption and decryption but do keep in mind that both the keys are mathematically related via simple transformation. Symmetric key could be encrypted by Certificates or via Asymmetric key as per your need.
Insertinto emps (Name,Dept)
–create Database Master Key (DMK)
Create master key
Encryption by Password =‘$qlhub’
–create certificate which will be used to
–encrypt symmetric key
With Subject=‘Certificate to encrypt emps table’,
–creating symmetric key
With Algorithm =AES_256
Encryption byCertificate AdvCert
–once you create symmetric key
–you need to open it before use.
Decryption bycertificate AdvCert
SelectName,Dept,EncryptByKey(Key_Guid(N‘AdvSym’),Name)as EncryptedName from emps
–now this is time to update table
update emps set EncryptedName=EncryptByKey(Key_Guid(N‘AdvSym’),Name)
Though Encryption and Decryption process is resource intensive, it is become necessary in some cases. If you look back in SQL Server 2000 and 7.0 days, you didn’t have any in-built mechanism and had to depend on 3rd party tools. But from SQL Server 2005, Microsoft started providing in-built support for encryption and decryption. Let us look in details about what is it? How does it work?
Since this is pretty big topic, it is not desirable to have it in one article so I will upload it in part. Before we move further in topic, let us find out Architecture (Hierarchy) of Encryption and Decryption mechanism in SQL Server 2008. Following image will give you an idea about that.
SQL Server 2008 encryption model inherits Windows Crypto API to encrypt and decrypt data in your database and supports layered approach. At the second level of encryption, there will be SMK (Service Master Key) . You can find one SMK (Service Master Key) per instance. It used to get generated by its own when it needs to encrypt any other key. Well as I just told you that each server instance can have only one SMK but every database in your instance can have separate DMK (Database Master Key) which is encrypted by SMK.
At the bottom level of Encryption, you can find Certificates, Asymmetric key and Symmetric key. Detailed article with example about each of these are going to come soon. Keep Reading!!!!