Using Transparent Data Encryption Feature of SQL Server 2008

The following example describe how to protect you database backup by using Transparent Data Encryption

-- create database
create database mydb1
go
use mydb1
go

-- create table mytable in database mydb1
create table mytable
(
id int primary key,
nam char(10)
)

-- insert record to table
insert into mytable values (1,'Mohamed')


-- make a backup for database mydb1
backup database mydb1 to disk ='c:\myback.bak'

goto file myback.bak that is found in 'c:\', open it by Notepad program and try to search for a keyword 'Mohamed'. note that it's can clearly see data into backup file.

to protect data in backup, do the following steps.

-- create master key for your instanceUSE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mY_P@$$w0rd'


-- create certificate object for your instance
CREATE CERTIFICATE mycert
WITH SUBJECT = 'My Certificate for database'


-- in specific database 'mydb1' create encryption key that associated with
-- certificate object of server that created in previous block.
USE mydb1
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE mycert
GO


-- open feature of encryption in database scope
ALTER DATABASE mydb1
SET ENCRYPTION ON
-- try to make backup again
backup database mydb1 to disk ='c:\myback2.bak'

goto again to 'c:\' try to open file 'myback2.bak' by Notepad program, note that all content of backup encrypted now.

now if need to restore this encrypted backup into another database in diffirence instance, the following error will be occurs.

To restore this backup without any error, wite the following code in the first server (the server which hold the main database)

USE master
GO
BACKUP CERTIFICATE mycert
TO FILE = 'C:\mydb_File.cer'
WITH PRIVATE KEY (FILE = 'C:\mydb_Key.pvk' ,
ENCRYPTION BY PASSWORD = 'mY_P@$$w0rd' )
Go

The above code create backup for the exist server certificate to file named 'mydb_file.cer' and backup key of database also (private key) in file 'mydb_key.pvk'

Now go to another server (which want to restore encypted backup) and write the following code.

-- creating master key for another server instance
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'EnKrYpt3d_P@$$w0rd'
Go

-- create certificate object for this server instance, this certificate
-- restored from file 'pvk', 'cer'
CREATE CERTIFICATE Inst2Cert
FROM FILE = 'C:\mydb_File.cer'
WITH PRIVATE KEY (FILE = 'C:\mydb_Key.pvk',
DECRYPTION BY PASSWORD = 'mY_P@$$w0rd');
GO

The data retored successfuly now in the second server.


** Some Verification for SQL object

* Verify Master key on the server

USE master
GO
SELECT * FROM sys.symmetric_keys WHERE name LIKE '%MS_DatabaseMasterKey%'
GO

* Verify Certifications for master key

SELECT * FROM sys.certificates

* Verify database encryption states

SELECT
DB_NAME(database_id) AS DatabaseName
,Encryption_State AS EncryptionState
,key_algorithm AS Algorithm
,key_length AS KeyLength
FROM sys.dm_database_encryption_keys
GO

and also

SELECT
NAME AS DatabaseName
,IS_ENCRYPTED AS IsEncrypted
FROM sys.databases
GO