Encryption – Decryption in SQL Server 2008 and backup database with encrypted data and restore it somewhere else

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, we have improved and in-built facilities for Encryption and Decryption within SQL Server.

To encrypt data and decrypt it, it is really a very crucial task as one mistake and your data go out of your reach. It can become more difficult when you will encrypt some data in one database in one server and try to restore that database into different server.

In this Article, I am going to show you the script which can encrypt data in one database on one server, take its backup, and restore that encrypted database anywhere else and you will get your data with 100% security and no data risk.

We will now create one database which is going to be used in throughout this example.

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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Asymmetric Key (Encryption – Decryption in SQL Server 2008 Part 5)

Asymmetric Key is a small brother of Certificate which I have explained in my previous article. Asymmetric Key is a combination of public key and private key which uses some different algorithms to encrypt and decrypt key. Algorithms are RSA_512, RSA_1024, RSA_2048. Details of these algorithms are beyond the scope of this article, I will write detailed article on these kind of algorithm very soon.
Now let us look small T-SQL practice for this.
–create database master key before creating Asymmetric key
–if it is not already exist
CREATE MASTER KEY ENCRYPTION BY PASSWORD=‘$qlhub’


–creaet asymmetric key with
–RSA_512 algorithm
CREATE ASYMMETRIC KEY AsymADV
WITH ALGORITHM = RSA_512
go


–let us encrypt and decrypt data with Asymmetric key
DECLARE @Text nvarchar(max)
DECLARE @TextEnrypt varbinary(128)
DECLARE @TextDecrypt nvarchar(max)
SET @Text=N’hi’–, this is first Assymetric test, created by Ritesh Shah’
SET @TextEnrypt=ENCRYPTBYASYMKEY(AsymKey_ID(N’AsymADV’),@Text)
SET @TextDecrypt=DECRYPTBYASYMKEY (AsymKey_ID(N’AsymADV’),@TextEnrypt)
SELECT @Text AS ‘ORIGINAL TEXT’,@TextEnrypt AS ‘Encrypted Text’,@TextDecrypt as ‘Decrypted Text’
GO


–drop Asymmetric key
DROP ASYMMETRIC KEY AsymADV
GO


–drop master key
drop master key



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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Database master Key (Encryption – Decryption in SQL Server 2008 Part 3)

After writing general summary about this topic and Service Master Key this is time the time to give something about Database Master Key (DMK). Actually every database in SQL Server 2008 instance can have one DMK which used to encrypt and decrypt Asymmetric Key and Certificate Key. I will describe what Asymmetric Key is and what Certificate key is in later article as the scope of this article is DMK.
Well, after reading short description above, you must have came to know that DMK is somehow MUST to go further in native encryption and decryption of SQL Server 2008. Let us now look at some small practical snippets about DMK.
Use AdventureWorks
GO


–create database master key, it is good to kepp password with it
–Password use windows password complexiti policy, if there is any.
–when you create DMK with password, it uses triple Data Encryption Standard to protect it
CREATE MASTER KEY ENCRYPTION BY PASSWORD=‘$qlhub’
GO


–for backing up DMK, follow the script given here,
–it will backing up DMK in adv.dmk file to D drive
–with password ‘$qlhub’
–don’t forget to remember password as it will be need
–while restoring DMK from file in crisis situation
BACKUP MASTER KEY TO FILE = ‘D:\adv.DMK’
ENCRYPTION BY PASSWORD=‘$qlhub’
GO


–restoring DMK from file whenever needed
RESTORE MASTER KEY FROM FILE = ‘D:\adv.DMK’
DECRYPTION BY PASSWORD = ‘$qlhub’ –used for decrypt the DMK restored from file
ENCRYPTION BY PASSWORD=‘$qlhub.com’ –this password will be used to encrypt DMK after it gets loaded into the DB
GO
By default, when you generate the Database Master Key (DMK), it is encrypted by SMK (Service Master Key) so that anybody with sysAdmin role can decrypt your DMK, this could be a security thread in some environment so you have to turn this feature off by following command.
–altering DMK
ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY;



Now, let me share you one of my practical example I have used so many times. Generally while developing the project, I used to create DMK in development environment, when it is needed to put the work in live environment, I used to take backup of DMK from development server, put a .DMK file to live server, restore that .DMK file in live server and execute following commands so that certificates I created on my development DB works well on my live server too.
OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘$qlhub’
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY



Hope this will be helpful to you.
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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Service Master Key (Encryption – Decryption in SQL Server 2008 Part 2)

You might have read my first article on Encryption-Decryption series that was an introduction only; herewith we are moving one step ahead.
Service Master Key is a base in hierarchy of SQL Server Encryption and Decryption, it directly access Windows Data Protection API. Only one Service Master Key can exist per SQL Server instance. I have already mentioned that Service Master Key (SMK) used to get generated by its own whenever it is needed first time than question may pops up in your mind that what should we need to do if it is generated by its own? Well, interesting question but the answer is also interesting. You need to do some administrative task for SMK as there is a possibility that sometime your SMK key gets corrupt and you need to restore it in your instance. If you don’t have it, you will definitely lose your encrypted data as there will not be any possibility that you can decrypt your data and use it if you don’t have your SMK.
I highly recommend whenever you install new instance of SQL Server 2008, get the copy of your SMK in file at some safe place so that you can restore it whenever you need it.
Now let us see some important administrative commands for managing SMK.
–backing up Service Master Key
–recommended that you take backup of
–your service master key as soon as you install new instance
BACKUP SERVICE MASTER KEY TO FILE= ‘D:\SQL2K8.SMK’
ENCRYPTION BY PASSWORD = ‘$qlhub’
GO
–restoring service master key
–you can use file which we have backed up, whenever you need
RESTORE SERVICE MASTER KEY FROM FILE=‘D:\SQL2K8.SMK’
DECRYPTION BY PASSWORD = ‘$qlhub’
GO
–alterring service master key
–SMK use current service account of SQL Server
–so it is good practise to regenerate SMK whenever you
–make any change in your service account.
 ALTER SERVICE MASTER KEY REGENERATE;
 GO
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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Encryption – Decryption in SQL Server 2008 – Part 1

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!!!!

 

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

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah