SQL SERVER –Transparent Data Encryption (TDE)

Share via:

Post Views: 0

SQL SERVER –Transparent Data Encryption (TDE)

Dear Readers,

In this article, we will learn about SQL SERVER –Transparent Data Encryption (TDE)

Transparent Data Encryption (TDE) performs real-time I/O encryption and decryption of the data and log files, thereby, protecting data at rest.

When SQL Server 2008 was introduced, Microsoft implemented Transparent Data Encryption (TDE). When TDE is enabled on a database SQL Server will encrypt the database as data is written to the disk. Additionally, when data is read from disk it will be unencrypted. When data is in memory is it is in an unencrypted format.

Encryption of a database file is done at the page level. The pages in an encrypted database are encrypted before they’re written to disk and are decrypted when read into memory. TDE doesn’t increase the size of the encrypted database.

One more advantage with TDE, database backups will also be encrypted. In the event that a backup of the database gets lost or stolen, the culprit will not be able to restore the database without the appropriate certificate, keys and passwords

Below are the sequence order steps to implement TDE

Steps to implement TDE for database

Here I am using DB name as Testing

Step 1: Create Database Master Key

USE Master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='Test@12345';
GO

USE Master;

CREATE MASTER KEY ENCRYPTION

BY PASSWORD='Test@12345';

Step 2: Create a Certificate to support TDE

USE Master;
GO
CREATE CERTIFICATE TDE_Cert
WITH
SUBJECT='DB_encryption';
GO

USE Master;

CREATE CERTIFICATE TDE_Cert

WITH

SUBJECT='DB_encryption';

Step 3: Create Database Encryption Key

USE Testing
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Cert;
GO

USE Testing

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER CERTIFICATE TDE_Cert;

Step 4: Enable TDE on Database

ALTER DATABASE Testing
SET ENCRYPTION ON;
GO

ALTER DATABASE Testing

SET ENCRYPTION ON;

Now TDE implemented on Testing database and using below script we can check on which database TDE is enabled.

–Execute below query and check what databases are encrypted on the SQL Server instance

select DB_NAME(database_id) AS DatabaseName, encryption_state,
encryption_state_desc =
CASE encryption_state
WHEN '0' THEN 'No database encryption key present, no encryption'
WHEN '1' THEN 'Unencrypted'
WHEN '2' THEN 'Encryption in progress'
WHEN '3' THEN 'Encrypted'
WHEN '4' THEN 'Key change in progress'
WHEN '5' THEN 'Decryption in progress'
WHEN '6' THEN 'Protection change in progress (The certificate or asymmetric key that is encrypting the database is encrypting the
database encryption key is being changed.)'
ELSE 'No Status'
END,
percent_complete,encryptor_thumbprint,encryptor_type
from sys.dm_database_encryption_keys

select DB_NAME(database_id) AS DatabaseName, encryption_state,

encryption_state_desc =

CASE encryption_state

WHEN '0' THEN 'No database encryption key present, no encryption'

WHEN '1' THEN 'Unencrypted'

WHEN '2' THEN 'Encryption in progress'

WHEN '3' THEN 'Encrypted'

WHEN '4' THEN 'Key change in progress'

WHEN '5' THEN 'Decryption in progress'

WHEN '6' THEN 'Protection change in progress (The certificate or asymmetric key that is encrypting the database is encrypting the

database encryption key is being changed.)'

ELSE 'No Status'

END,

percent_complete,encryptor_thumbprint,encryptor_type

from sys.dm_database_encryption_keys

You can get the result as below

— Execute below query to find the name of the certificates

select DB_NAME(database_id) AS DatabaseName, encryption_state,
encryption_state_desc =
CASE encryption_state
WHEN '0' THEN 'No database encryption key present, no encryption'
WHEN '1' THEN 'Unencrypted'
WHEN '2' THEN 'Encryption in progress'
WHEN '3' THEN 'Encrypted'
WHEN '4' THEN 'Key change in progress'
WHEN '5' THEN 'Decryption in progress'
WHEN '6' THEN 'Protection change in progress (The certificate or asymmetric key that is encrypting the database is encrypting the 
database encryption key is being changed.)'
ELSE 'No Status'
END,
percent_complete,b.name as Certificate, encryptor_thumbprint,encryptor_type
from sys.dm_database_encryption_keys a INNER JOIN sys.certificates b ON a.encryptor_thumbprint=b.thumbprint

select DB_NAME(database_id) AS DatabaseName, encryption_state,

encryption_state_desc =

CASE encryption_state

WHEN '0' THEN 'No database encryption key present, no encryption'

WHEN '1' THEN 'Unencrypted'

WHEN '2' THEN 'Encryption in progress'

WHEN '3' THEN 'Encrypted'

WHEN '4' THEN 'Key change in progress'

WHEN '5' THEN 'Decryption in progress'

WHEN '6' THEN 'Protection change in progress (The certificate or asymmetric key that is encrypting the database is encrypting the

database encryption key is being changed.)'

ELSE 'No Status'

END,

percent_complete,b.name as Certificate, encryptor_thumbprint,encryptor_type

from sys.dm_database_encryption_keys a INNER JOIN sys.certificates b ON a.encryptor_thumbprint=b.thumbprint

We will get below result

How to restore database backup on other instance which TDE enabled

1.Take backup TDE certificate on instance

BACKUP CERTIFICATE TDE_Cert TO FILE = 'D:\backup\TDE_cert'
WITH PRIVATE KEY (file='D:\backup\TDE_CertKey.pvk',
ENCRYPTION BY PASSWORD='Test@12345')

BACKUP CERTIFICATE TDE_Cert TO FILE = 'D:\backup\TDE_cert'

WITH PRIVATE KEY (file='D:\backup\TDE_CertKey.pvk',

ENCRYPTION BY PASSWORD='Test@12345')

2.Restore TDE Certificate on other instance

— create master key on other instance which we are going to restore the backup

USE Master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='Test@12345';
GO

USE Master;

CREATE MASTER KEY ENCRYPTION

BY PASSWORD='Test@12345';

— Restore certificate

USE MASTER
GO
CREATE CERTIFICATE TDE_Cert
FROM FILE = 'D:\backup\TDE_Cert'
WITH PRIVATE KEY (FILE = 'D:\backup\TDE_CertKey.pvk',
DECRYPTION BY PASSWORD = 'Test@12345' );

USE MASTER

CREATE CERTIFICATE TDE_Cert

FROM FILE = 'D:\backup\TDE_Cert'

WITH PRIVATE KEY (FILE = 'D:\backup\TDE_CertKey.pvk',

DECRYPTION BY PASSWORD = 'Test@12345' );

Now you can restore the database after restoring certificates.

How to Remove TDE

1.Disable TDE on the affected databases

USE master;
GO
ALTER DATABASE testing SET ENCRYPTION OFF;

USE master;

ALTER DATABASE testing SET ENCRYPTION OFF;

2.Remove the encryption key

USE testing;
GO
DROP DATABASE ENCRYPTION KEY;
GO

USE testing;

DROP DATABASE ENCRYPTION KEY;

3.Drop certificates using below query

USE [master]
GO
DROP CERTIFICATE TDE_Cert;

USE [master]

DROP CERTIFICATE TDE_Cert;

4.Drop master key

use master
go
DROP MASTER KEY

use master

DROP MASTER KEY

Thank you for giving your valuable time to read the above information.
Follow us on
Website : www.ktexperts.com
Facebook Page : KTexperts
Linkedin Page : KT EXPERTS

Share via:

Note: Please test scripts in Non Prod before trying in Production.

Sridhar Y

Share this post

Tags

SQL SERVER –Transparent Data Encryption (TDE)

SQL SERVER –Transparent Data Encryption (TDE)

Step 1: Create Database Master Key

Step 2: Create a Certificate to support TDE

Step 3: Create Database Encryption Key

Step 4: Enable TDE on Database

We will get below result

How to restore database backup on other instance which TDE enabled

Add Comment Cancel reply

Sridhar Y

Share this post

Tags

SQL SERVER –Transparent Data Encryption (TDE)

Step 1: Create Database Master Key

Step 2: Create a Certificate to support TDE

Step 3: Create Database Encryption Key

Step 4: Enable TDE on Database

We will get below result

How to restore database backup on other instance which TDE enabled

You might also like...

Backup Details of SQL

Recovery models in SQL Server

SQL Server Database Architecture

Add Comment Cancel reply