Community Pick: Many members of our community have endorsed this article.
Editor's Choice: This article has been selected by our editors as an exceptional contribution.

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

Published:
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.

 
--CREATE First Database for encryption and decryption testing
                      USE master;
                      GO
                      CREATE DATABASE Encry1
                      ON 
                      ( NAME = Encry1_dat,
                          FILENAME = 'C:\Encry1Data.mdf',
                          SIZE = 3,
                          MAXSIZE = 5,
                          FILEGROWTH = 1 )
                      LOG ON
                      ( NAME = Encry1_log,
                          FILENAME = 'C:\Encry1Log.ldf',
                          SIZE = 1MB,
                          MAXSIZE = 5MB,
                          FILEGROWTH = 1MB ) ;
                      GO
                      

Open in new window


As soon as you are ready with database, let us now move on to create one table, we will encrypt data in that table after inserting some records.

 
--create one table which will use encrypted and decrypted data in it
                      Use Encry1
                      GO
                      Create Table emps
                      (
                            name varchar(20),
                            dept varchar(20),
                            EncryptedName Varbinary(256),
                            DecryptedName Varchar(20)
                      )
                      
                      Insert into emps (Name,Dept)
                      Select 'Ritesh','MIS' union all
                      Select 'Rajan','Acct'
                      union all
                      Select 'Bhaumik','IT'
                      
                      Select * from emps
                      go
                      

Open in new window


Ok. Now we have database and table ready to encrypt. Before we really encrypt the data, we should be armed with some weapons. Let us prepare it.

 
--create one Database Master Key
                      Create master key
                      Encryption by Password ='$qlhub1234'
                      

Open in new window


To know more about Database Master Key, Please Click Here.

Now, we will need Certificate based on the database master key we have generated above.

 
--Create One Certificate
                      Create Certificate AdvCert
                      With Subject= 'Certificate to encrypt emps table',
                      Start_date = '2009-09-29',
                      Expiry_date ='2012-02-07'
                      GO
                      

Open in new window


To know more about Certificate, Please Click Here.

Based on the certificate, we should create Symmetric key which will be used to actually encrypt the data.


 
--create symmetric key
                      Create Symmetric Key AdvSym
                      With Algorithm =AES_256
                      Encryption by Certificate AdvCert
                      GO
                      
                      --open symmetric key
                      Open Symmetric key AdvSym
                      Decryption by certificate AdvCert
                      GO
                      

Open in new window


To know more about Symmetric key, please click here.

Now before we actually update our table with encrypted data, I would prefer to see it with SELECT query. Let us do that.


 
--check encryption function whether it is working or not
                      Select Name,Dept,EncryptByKey(Key_Guid(N'AdvSym'),Name) as EncryptedName from emps
                      go
                      
                      --there is null in EncryptedName column right now,
                      --we are updating EncryptedName column with encrypted data
                      update emps set EncryptedName=EncryptByKey(Key_Guid(N'AdvSym'),Name)
                      GO
                      
                      --look at the status of data now.
                      select * from emps
                      GO
                      

Open in new window



Now, we are fine with encryption, but, if we can’t decrypt it is of no worth. Let us check whether decryption is properly working or not.

 
--let us check whether decryption is working or not
                      Select Name,Dept,EncryptedName,
                      Convert(Varchar(20), DecryptByKey(EncryptedName)) as DecryptedName from emps
                      go
                      
                      --update emps table DecryptedName column with Decrypted data
                      Update emps set DecryptedName=Convert(Varchar(20), DecryptByKey(EncryptedName))
                      GO
                      

Open in new window


So finally we achieved encryption and decryption in our test database.

Now, while we can encrypt data and decrypt data, but what happens when it is time to backup database and restore it ? What do you need to do?

For making the same database work anywhere else (including a restore to Live), we are use to backup the database and restore it somewhere. But, with it being encrypted, we will have to give same treatment to database master key as well (backup and restore)

 
--since we want to backup DMK and want to restore it in 
                      --different server, I am removing encryption by SMK
                      --as SMK is instance based, it may be possible that
                      --you are trying to restore database in different instance
                      ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY;
                      
                      --again opening master key before taking up backup
                      OPEN MASTER KEY DECRYPTION BY PASSWORD = '$qlhub1234'
                      
                      --backing up master key to physical file
                      BACKUP MASTER KEY TO FILE = 'C:\Encry1.DMK'
                      ENCRYPTION BY PASSWORD='$qlhub1234'
                      GO
                      
                      --now backing up database Encry1
                      BACKUP DATABASE Encry1
                        TO DISK = 'C:\Encry1.bak' 
                      GO
                      

Open in new window


Once we took back up of database and master key. If you are having different server, use it or/else create new database and restored above backed up database and key in new database in same server.

 
--create another database in same instance 
                      --if you wish, you can do it in other server
                      --or may be in different instance too.
                      USE master;
                      GO
                      CREATE DATABASE Encry2
                      ON 
                      ( NAME = Encry2_dat,
                          FILENAME = 'D:\Encry2Data.mdf',
                          SIZE = 3,
                          MAXSIZE = 5,
                          FILEGROWTH = 1 )
                      LOG ON
                      ( NAME = Encry2_log,
                          FILENAME = 'D:\Encry2Log.ldf',
                          SIZE = 1MB,
                          MAXSIZE = 5MB,
                          FILEGROWTH = 1MB ) ;
                      GO
                      
                      
                      --restore our Encry1 database to Encry2
                      USE master;
                      GO
                      RESTORE DATABASE Encry2
                         from disk = 'C:\Encry1.bak'
                         WITH REPLACE, 
                            MOVE 'Encry1_dat' TO 
                      'D:\Encry2Data.mdf', 
                            MOVE 'Encry1_log' 
                      TO 'D:\Encry2Log.ldf'
                      go
                      

Open in new window


if you think that you have already restored database which has everything in it (data, sps, views, certificates, master key etc.) and it should directly working as it is then you are wrong. Check it out.

 
--as soon as you restore database
                      --look at the status of the data which are encrypted.
                      --you will not getting anything, even you have certificate
                      --master key everything is in backup of Encry1
                      USE Encry2
                      go
                      
                      Select Name,Dept,EncryptByKey(Key_Guid(N'AdvSym'),Name) as EncryptedName,
                      Convert(Varchar(20), DecryptByKey(EncryptedName)) as DecryptedName from emps
                      go
                      

Open in new window


So, now what to do in order to get our data back (decrypt it)? Simply follow the steps given below.

--you need to open master key
                      OPEN MASTER KEY DECRYPTION BY PASSWORD = '$qlhub1234'
                      GO
                      
                      --put the service level encryption back
                      ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
                      GO
                      
                      --restore your Database master key from file
                      RESTORE MASTER KEY FROM FILE = 'C:\Encry1.DMK'
                      DECRYPTION BY PASSWORD = '$qlhub1234' 
                      ENCRYPTION BY PASSWORD='$qlhub1234' 
                      GO
                      
                      --open symmetric key,
                      --no need to create it as it was there in backup
                      Open Symmetric key AdvSym
                      Decryption by certificate AdvCert
                      GO
                      
                      --check the data back, you will get everything as it is.
                      Select Name,Dept,EncryptByKey(Key_Guid(N'AdvSym'),Name) as EncryptedName,
                      Convert(Varchar(20), DecryptByKey(EncryptedName)) as DecryptedName from emps
                      go
                      

Open in new window


Now, you are done!!!

Please do leave your comments and feedback about this article; it will encourage me to come up with more interesting articles like this.
--you need to open master key
                      OPEN MASTER KEY DECRYPTION BY PASSWORD = '$qlhub1234'
                      GO
                      
                      --put the service level encryption back
                      ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
                      GO
                      
                      --restore your Database master key from file
                      RESTORE MASTER KEY FROM FILE = 'C:\Encry1.DMK'
                      DECRYPTION BY PASSWORD = '$qlhub1234' 
                      ENCRYPTION BY PASSWORD='$qlhub1234' 
                      GO
                      
                      --open symmetric key,
                      --no need to create it as it was there in backup
                      Open Symmetric key AdvSym
                      Decryption by certificate AdvCert
                      GO
                      
                      --check the data back, you will get everything as it is.
                      Select Name,Dept,EncryptByKey(Key_Guid(N'AdvSym'),Name) as EncryptedName,
                      Convert(Varchar(20), DecryptByKey(EncryptedName)) as DecryptedName from emps
                      go
                      

Open in new window

12
16,552 Views

Comments (7)

Great article
Very nice idea about encryption.
Nice article!!!
Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Very well written.  Voted Yes.
Well Written

View More

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.