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
CREATE DATABASE Encry1
( NAME = Encry1_dat,
FILENAME = 'C:\Encry1Data.mdf',
SIZE = 3,
MAXSIZE = 5,
FILEGROWTH = 1 )
( NAME = Encry1_log,
FILENAME = 'C:\Encry1Log.ldf',
SIZE = 1MB,
MAXSIZE = 5MB,
FILEGROWTH = 1MB ) ;
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
Create Table emps
Insert into emps (Name,Dept)
Select 'Ritesh','MIS' union all
Select * from emps
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'
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.