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
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
Select allOpen 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
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
Select allOpen 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.
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
1:
2:
3:
4:
5:
6:
Select allOpen 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
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
Select allOpen 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
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
Select allOpen 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
1:
2:
3:
4:
5:
6:
7:
8:
Select allOpen 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
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
Select allOpen 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
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
Select allOpen 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
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
Select allOpen 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
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
Select allOpen 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
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
Select allOpen in new window
by: RiteshShah on 2010-02-09 at 02:54:25ID: 9397
Thank you very much for your kind words Mark!!!