Symmetric Key (Encryption – Decryption in SQL Server 2008 Part 6)

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.
Use AdventureWorks
GO
Create Table emps
(
      name varchar(20),
      dept varchar(20),
      EncryptedName Varbinary(256),
      DecryptedName Varchar(20)
)
go
Insert into emps (Name,Dept)
Select ‘Ritesh’,‘MIS’ union all
Select ‘Rajan’,‘Acct’
GO
Select * from emps
go
–create Database Master Key (DMK)
Create master key
Encryption by Password =‘$qlhub’
–create certificate which will be used to
–encrypt symmetric key
Create Certificate AdvCert
With Subject= ‘Certificate to encrypt emps table’,
Start_date = ‘2009-09-29’,
Expiry_date =‘2012-02-07’
GO
–creating symmetric key
Create Symmetric Key AdvSym
With Algorithm =AES_256
Encryption by Certificate AdvCert
GO
–once you create symmetric key
–you need to open it before use.
Open Symmetric key AdvSym
Decryption by certificate AdvCert
GO
Select Name,Dept,EncryptByKey(Key_Guid(N‘AdvSym’),Name) as EncryptedName from emps
go
–now this is time to update table
update emps set EncryptedName=EncryptByKey(Key_Guid(N‘AdvSym’),Name)
–let us check data
select * from emps
Select Name,Dept,EncryptByKey(Key_Guid(N‘AdvSym’),Name) as EncryptedName,
Convert(Varchar(20), DecryptByKey(EncryptedName)) as DecryptedName from emps
go
–now this is time to decrypt data and update table.
Update emps set DecryptedName=Convert(Varchar(20), DecryptByKey(EncryptedName))
–check your data again
select * from emps
go
So this is how Symmetric key works!!!
I want to add one more article in this series which will be complete practice script from top to bottom.
Hope to have it for you very soon.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com
Advertisements

Symmetric Key (Encryption – Decryption in SQL Server 2008 Part 6)

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.
Use AdventureWorks
GO
Create Table emps
(
      name varchar(20),
      dept varchar(20),
      EncryptedName Varbinary(256),
      DecryptedName Varchar(20)
)
go
Insert into emps (Name,Dept)
Select ‘Ritesh’,‘MIS’ union all
Select ‘Rajan’,‘Acct’
GO
Select * from emps
go
–create Database Master Key (DMK)
Create master key
Encryption by Password =‘$qlhub’
–create certificate which will be used to
–encrypt symmetric key
Create Certificate AdvCert
With Subject= ‘Certificate to encrypt emps table’,
Start_date = ‘2009-09-29’,
Expiry_date =‘2012-02-07’
GO
–creating symmetric key
Create Symmetric Key AdvSym
With Algorithm =AES_256
Encryption by Certificate AdvCert
GO
–once you create symmetric key
–you need to open it before use.
Open Symmetric key AdvSym
Decryption by certificate AdvCert
GO
Select Name,Dept,EncryptByKey(Key_Guid(N‘AdvSym’),Name) as EncryptedName from emps
go
–now this is time to update table
update emps set EncryptedName=EncryptByKey(Key_Guid(N‘AdvSym’),Name)
–let us check data
select * from emps
Select Name,Dept,EncryptByKey(Key_Guid(N‘AdvSym’),Name) as EncryptedName,
Convert(Varchar(20), DecryptByKey(EncryptedName)) as DecryptedName from emps
go
–now this is time to decrypt data and update table.
Update emps set DecryptedName=Convert(Varchar(20), DecryptByKey(EncryptedName))
–check your data again
select * from emps
go
So this is how Symmetric key works!!!
I want to add one more article in this series which will be complete practice script from top to bottom.
Hope to have it for you very soon.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Certificate (Encryption – Decryption in SQL Server 2008 Part 4)

Well, we are moving one step forward for data encryption and decryption, I have already described some of the essential topics in this area which are mandatory before implementing this step. If you want to go backward, have a look at series of article on this topic at below given link.
Once you have SMK and DMK, you will have choice to go for Certificates, Asymmetric key, Symmetric key. Scope of this article is use of certificate. Let us start our journey.
Certificate is a kind of Asymmetric encryption with some additional metadata. In Asymmetric encryption data got encryption by two different methods but mathematically both are same. You can use “CREATE CERTIFICATE” T-SQL for creating new or use existing certificate.
–create new cerfiticate
CREATE CERTIFICATE ADV
ENCRYPTION BY PASSWORD =‘$qlhub’
WITH SUBJECT =‘ADVENTUREWORKS CERTIFICATE’,
START_DATE=’09/14/2009′, –IF NOT PROVIDED, CURRENT DATE IS START DATE
EXPIRY_DATE=’09/13/2015′— IF NOT PROVIDED, ONE YEAR AFTER START_DATE IS EXPIRY_DATE
go


I have observed that people are really very cautious about the data and used to take regular backup of data but not that much cautious for certificates. I HIGHLY recommend taking backup of your certificate as soon as you create it, so let us seeing the script of that.
BACKUP CERTIFICATE ADV
TO FILE = ‘d:\ADV.CER’
WITH PRIVATE KEY
(
      FILE=‘d:\ADV.PVK’,
      ENCRYPTION BY PASSWORD=‘$qlhub’,
      DECRYPTION BY PASSWORD=‘$qlhub’
)
go


Generally you think of restore command as soon as you finish backup. Let me tell you that there is no restore certificate command as you can restore your existing certificate from your backup file itself from CREATE CERTIFICATE command only. Have a look.
–LET US drop just created certificate and restore it from
–.cer file so that we are sure that certificate is created
–perfectly
DROP CERTIFICATE ADV
–restore from file
CREATE CERTIFICATE ADV
FROM FILE=‘D:\ADV.CER’


Once you create certificate and take a backup of it, now, you are ready to encrypt your data with certificate and decrypt it whenever it is needed.


DECLARE @Text nvarchar(max)
DECLARE @TextEnrypt varbinary(128)
DECLARE @TextDecrypt nvarchar(max)
SET @Text=N’hi, this is first certificate test, created by Ritesh Shah’
SET @TextEnrypt=ENCRYPTBYCERT(CERT_ID(‘ADV’),@Text)
SET @TextDecrypt=DECRYPTBYCERT(CERT_ID(‘ADV’),@TextEnrypt,N’$qlhub’)
SELECT @Text AS ‘ORIGINAL TEXT’,@TextEnrypt AS ‘Encrypted Text’,@TextDecrypt as ‘Decrypted Text’
GO
Hope you have enjoyed this small tour.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder ofhttp://www.SQLHub.com