Transparent Data Encryption

Transparent Data Encryption

• To provide security for data we can use encryption option.
• To provide security for data and Transactional Log files of a database as well as backups, we need

TDE which was introduced in SQL Server 2008.

Steps:

• Create a master key
• Create or obtain a certificate protected by master key.
• Create a database key and protect it by the certificate.
• Set the database you want to protect to use the encryption.
  
  

Step 1:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'p@ssw0rd';

GO

Step 2:

CREATE CERTIFICATE MyTDECert WITH SUBJECT = 'My TDE Certificate'

GO

Step 3:

To check existing certificates

SELECT * FROM sys.certificates where [name] = 'MyTDECert'
GO

Step 4:

Use AdventureWorks
GO
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyTDECert
GO

Step 5:

ALTER DATABASE AdventureWorks SET ENCRYPTION ON
GO

Verifying TDE

Step 1:

R.C on database ---> Properties ---> Options ---> Check Encryption Enabled: True

Step 2:

Find the current path of the files

sp_helpdb AdventureWorks

Step 3:

Detach database

use master
go
sp_detach_db AdventureWorks
go

Step 4:
            Copy data and Transactional Log files of AdventureWorks into
                  d:\AdventureWorks_Files

Step 5:

Connect to another instance of SQL Server

CREATE DATABASE [AdventureWorks] ON
( FILENAME = 'D:\AdventureWorks_Files\AdventureWorks.mdf'),
( FILENAME = 'D:\AdventureWorks_Files\AdventureWorks_log.ldf')
FOR ATTACH
GO

If the above step fails:

Step 6:

Go to first server take backup of certificate

Use Master

GO

BACKUP CERTIFICATE MyTDECert TO FILE = 'D:\MyTDECert.cert'
WITH PRIVATE KEY
 (
 FILE = 'D:\EncryptPrivateKey.key',
 ENCRYPTION BY PASSWORD = 'TryToUseOnlyStrongPassword'
 )
GO

Step 7:

Go to second server where need to attach db

USE [master]

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'p@ssw0rd';

GO

CREATE CERTIFICATE MyTDECert

FROM FILE = 'D:\MyTDECert.cert'

WITH PRIVATE KEY (

FILE = 'D:\EncryptPrivateKey.key'

, DECRYPTION BY PASSWORD = 'TryToUseOnlyStrongPassword'
 )

Step 8:

Run Step5 in second server.

Now the database is attached successfully.