Home » Protecting your database using Transparent Data Encryption in SQL Server 2008

Protecting your database using Transparent Data Encryption in SQL Server 2008

sql server DBA 999

Encryption:
Encryption is the process of transforming information in plain text using a cipher, or algorithm, to make it unreadable to everyone other than the person who has the key. There are two types of keys; symmetric and asymmetric. When the same value is used to encrypt and decrypt, then it is known as a symmetric key. An asymmetric key has two parts: one is a private key and the other is a public key. The private key is used to encrypt the data and the public key is used to decrypt the data.

TDE: is a full database level encryption that protects the data files and log files.
As per Microsoft documentation for TDE

“Transparent data encryption (TDE) performs real-time I/O encryption and decryption of the data and log files. The encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery. The DEK is a symmetric key secured by using a certificate stored in the master database of the server or an asymmetric key protected by an EKM module.”

One main advantage of TDE is that it does not require any changes to your existing application. This enables DBAs to encrypt data using AES and 3DES encryption algorithms without having to change the applications that will be connecting to the database.
When TDE is enabled, the Server starts a background thread that scans all the database files and encrypts them. This thread actually creates a shared lock on the database. Even when a DDL statement is executed and an Update lock is taken on the database, the encryption thread will run asynchronously using a shared lock.

Steps in Enabling TDE
•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.
Note: Do not forget to backup the certificate as shown below

Demo:

--Creating Master key
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssW0rd1!';
Go
-- create a certificate protected by this Master Key.
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate';
Go
-- create an encryption key for your database
USE AdventureWorks
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
GO

--Results:
--Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate
--and the private key or you will not be able to open the database.

--Backup the certificate by executing a Backup Certificate command…..to command.

Use master
go
BACKUP CERTIFICATE MyServerCert TO FILE = 'C:My CertficatesMyServerCert.cert'
WITH PRIVATE KEY ( FILE = 'C:My CertficatesEncryptPrivateKey.key',
ENCRYPTION BY PASSWORD = 'P@ssW0rd1!123')
GO


--alter the database to set the encryption ON.
ALTER DATABASE AdventureWorks
SET ENCRYPTION ON;
GO

--Verify Database Encryption Status

--You can verify the database encryption status by executing the below mentioned TSQL code.

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

Now to test TDE and if the backups are encrypted
–if we try to restore/attach a TDE Encrypted database onto another server with the Certificate then it will fail.

Now to make a database restore/attach work, follow the steps below:

--We can also use different master key password below
USE [master]
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssW0rd1!';
GO

--Restore the certificate by executing a Create Certificate command…..from command.

CREATE CERTIFICATE MyTDECert
FROM FILE = 'C:My CertficatesMyServerCert.cert'
WITH PRIVATE KEY (
FILE = 'C:My CertficatesEncryptPrivateKey.key'
, DECRYPTION BY PASSWORD = 'P@ssW0rd1!123'
)

--to verify results

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
--Now restore/attach database works as normal.

Hope this post is helpful. 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *