<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

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

Published on
26,836 Points
15,636 Views
12 Endorsements
Last Modified:
Awarded
Community Pick
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
Author:RiteshShah
7 Comments
LVL 31

Author Comment

by:RiteshShah
Thank you very much for your kind words Mark!!!
0

Expert Comment

by:jerra
Excellent article!

Question:
In the original database you did this to backup the DMK:
ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY;
OPEN MASTER KEY DECRYPTION BY PASSWORD = '$qlhub1234'
BACKUP MASTER KEY TO FILE = 'C:\Encry1.DMK' ENCRYPTION BY PASSWORD='$qlhub1234'
GO

What next when this has been done? Does it have to be altered back to be encrypted by the SMK? Again I am only referring to the original database. Otherwise the state of the DMK has changed?
0
LVL 6

Expert Comment

by:MuffyBunny
Great article
0
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Expert Comment

by:ShashiKumarSingh
Very nice idea about encryption.
0
LVL 9

Expert Comment

by:sachinpatil10d
Nice article!!!
0
LVL 67

Expert Comment

by:Jim Horn
Very well written.  Voted Yes.
0

Expert Comment

by:Crazy_SQL
Well Written
0

Featured Post

IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month