Tag: tde

Backup/Restore a SQL TDE certificate

Database backups are critical to any organization. Similarly, we need to ensure the TDE certificates are backed up in case of a DR scenario. TDE cert is present on any database that has encryption at rest enabled on it.

Below script takes backup of the master key, TDE cert and Private key attached to the cert:

BACKUP MASTER KEY
TO FILE = '\\NETWORKSHARE\CertBackup\TDE_SERVER01_MK'
ENCRYPTION BY PASSWORD = 'StrongPassword';
GO

USE [master];
GO
BACKUP CERTIFICATE Livongo_Ascend_Cert TO FILE = '\\NETWORKSHARE\CertBackup\TDE_SERVER01_Cert.cert'
WITH PRIVATE KEY
(
    FILE = '\\NETWORKSHARE\CertBackup\TDE_SERVER01_Key.pk',
    ENCRYPTION BY PASSWORD = 'StrongPassword'
);
GO

Below is the script to use in a DR scenario or when you need to restore an encrypted backup to a new server:

/* This creates the service master key*/ 
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'StrongPassword'
GO

/* This creates the TDE cert from backup */
Use [master]
GO
CREATE CERTIFICATE TDE_databasename
FROM FILE='\\NETWORKSHARE\CertBackup\TDE_SERVER01_Cert.cert'
WITH PRIVATE KEY (
FILE='\\NETWORKSHARE\CertBackup\TDE_SERVER01_Key.pk',
DECRYPTION BY PASSWORD='StrongPassword')
GO

/* This creates the encryption key using the cert we created in the above step*/
Use [databasename]
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_databasename
GO

/* This step enables the encrption on the database*/
ALTER DATABASE [databasename]
SET ENCRYPTION ON
GO

Use the following script to view all the encrypted DBs and their Cert name, encryption key used.

SELECT db_name(database_id) AS [Database Name],
    dek.key_length as [Key Length],
    case encryption_state when '0' then 'No database encryption key present, no encryption'
    when '1' then 'Not Encrypted'
    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'
    end as [Encryption Status] ,
    key_algorithm as [Key Algorithm],
    Name as [Cert Name],
    pvt_key_encryption_type_desc as [Pvt Key Desc],
    [subject] as [Subject],
    [expiry_date] as [Expiry Date],
    [start_date] as [Start Date]
FROM sys.certificates c
INNER JOIN sys.dm_database_encryption_keys dek ON c.thumbprint = dek.encryptor_thumbprint

How to configure Transparent Data Encryption (TDE) in SQL Server

Introduction

Transparent Data Encryption (TD) was introduced in SQL Server 2008. Its main purpose was to protect data by encrypting the physical files, both the data (mdf) and log (ldf) files (as opposed to the actual data stored within the database). TDE encrypts SQL Server, Azure SQL Databases, and Azure SQL Data Warehouse data files.

Instructions

step-by-step guide:

  1. Create a master key
  2. Create or obtain a certificate protected by the master key
  3. Create a database encryption key and protect it by using the certificate.
  4. Set the database to use encryption.

The following example shows the encryption and decryption of the StackOverflow database using a certificate named MyServerCert that’s installed on the server.

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>';
go
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate';
go
USE StackOverflow;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256 
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
GO
ALTER DATABASE StackOverflow
SET ENCRYPTION ON;
GO

The encryption and decryption operations are scheduled on background threads by SQL server. You can use the DMVs to query the cert and key information.

What happens when you enable TDE?

To enable TDE on a database, SQL server must do an encryption scan. The scan reads each page from the data files into the buffer pool and then writes the encrypted pages back out to disk.

Is Tempdb encrypted too when you enable TDE on any database in a SQL instance?

Yes, the tempdb system database is encrypted if any other database on the SQL server instance is encrypted by using TDE. This encryption might have a performance effect for unencrypted databases on the same SQL instance.

Are you able to add an encrypted database to an AlwaysOn availability group?

Yes, you can. To encrypt databases that are part of an availability group, create the master key and certificates on all secondary replicas before creating the database encryption key on the primary replica.

If a certificate is used to protect the database encryption key, backup the certificate created on the primary replicate, and then create the certificate from a file on all secondary replicas before creating the database encryption key on the primary replica.

Here is the script to view all the encrypted DBs and their certificate names on a SQL server instance:

SELECT db_name(database_id) AS [Database Name],
	dek.key_length as [Key Length],
	case encryption_state when '0' then 'No database encryption key present, no encryption'
	when '1' then 'Not Encrypted' 
	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'
	end as [Encryption Status] ,
	key_algorithm as [Key Algorithm],
	Name as [Cert Name],
	pvt_key_encryption_type_desc as [Pvt Key Desc],
	[subject] as [Subject],
	[expiry_date] as [Expiry Date],
	[start_date] as [Start Date]
FROM sys.certificates c
INNER JOIN sys.dm_database_encryption_keys dek ON c.thumbprint = dek.encryptor_thumbprint
%d bloggers like this: