TDE for encryption and decryption

To Drop encryption keys

/* Clean up database */
USE EncryptTest
GO
CLOSE SYMMETRIC KEY TestTableKey
GO
DROP SYMMETRIC KEY TestTableKey
GO
DROP CERTIFICATE EncryptTestCert
GO
DROP MASTER KEY
GO
USE [master]
GO
DROP DATABASE [EncryptTest]
GO

Database details

SP_HELPDB AND SEE THE STATUS COLUMN

OR

SELECT * FROM master.dbo.sysdatabases

Ex:

SELECT name,  
       DATABASEPROPERTYEX(name, 'Recovery'), 
       DATABASEPROPERTYEX(name, 'Status') 
FROM   master.dbo.sysdatabases 
ORDER BY 1

Value	Description	Value returned
IsAutoClose	Database shuts down cleanly and frees resources after the last user exits.	1 = TRUE 0 = FALSE NULL = Invalid input
IsAutoCreateStatistics	Existing statistics are automatically updated when the statistics become out-of-date because the data in the tables has changed.	1 = TRUE 0 = FALSE NULL = Invalid input
IsAutoShrink	Database files are candidates for automatic periodic shrinking.	1 = TRUE 0 = FALSE NULL = Invalid input
IsAutoUpdateStatistics	Auto update statistics database option is enabled.	1 = TRUE 0 = FALSE NULL = Invalid input
IsPublished	The tables of the database can be published for snapshot or transactional replication, if replication is installed.	1 = TRUE 0 = FALSE NULL = Invalid input
IsSubscribed	Database can be subscribed for publication.	1 = TRUE 0 = FALSE NULL = Invalid input
IsTornPageDetectionEnabled	Microsoft® SQL Server™ detects incomplete I/O operations caused by power failures or other system outages.	1 = TRUE 0 = FALSE NULL = Invalid input
Recovery	Recovery model for the database.	FULL = full recovery model BULK_LOGGED = bulk logged model SIMPLE = simple recovery model
Status	Database status.	ONLINE = database is available for query OFFLINE = database was explicitly taken offline RESTORING = database is being restored RECOVERING = database is recovering and not yet ready for queries SUSPECT = database cannot be recovered
Updateability	Indicates whether data can be modified.	READ_ONLY = data can be read but not modified READ_WRITE = data can be read and modified
UserAccess	Indicates which users can access the database.	SINGLE_USER = only onedb_owner, dbcreator, orsysadmin user at a time RESTRICTED_USER = only members of db_owner,dbcreator, and sysadminroles MULTI_USER = all users
Version	Internal version number of the Microsoft SQL Server code with which the database was created. For internal use only by SQL Server tools and in upgrade processing.	Version number = Database is open NULL = Database is closed

Backup and Restore the Database using Encryption

Message

Executed as user: Domain\ServiceAccount. Cannot find server certificate with thumbprint '0x62A3B87C122C170DFEEBFC6AFCB14B4E5BB5543B'. [SQLSTATE 42000] (Error 33111) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.

Backup the certificate with master key on the Primary Server

BACKUP CERTIFICATE [EncryptionCertificate]

TO FILE = 'Certificate File path'

WITH PRIVATE KEY (FILE = 'Master Key File path.dat', ENCRYPTION BY PASSWORD = 'password')

Restore the certificate with master key password on the Secondary Server

CREATE CERTIFICATE [EncryptionCertificate]

FROM FILE='Certificate File path'

WITH PRIVATE KEY ( FILE = 'Master Key File path.dat' , DECRYPTION BY PASSWORD = 'password')

This will resolve the issue and can restore the database with out any errors.

Encryption and Decryption

--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

--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 'Rashmi','IT'

Select * from emps

go

--create one Database Master Key

Create master key

Encryption by Password ='$qlhub1234'

--Create One Certificate

Create Certificate AdvCert

With Subject= 'Certificate to encrypt emps table',

Start_date = '2009-09-29',

Expiry_date ='2012-02-07'

GO

--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

--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

--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

--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

Now are done with Encry1 database and going to create Encry2 database and will perform certain operation there.

--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

--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

--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