TN 1190 How to enable Transparent Data Encryption in SQL Server

Description

This article from InSource shows how to enable TDE for use with j5 SQL Server

• Author: Ron Tanner
• Published: 09/23/2021
• Applies to: j5

Details

SQL Server Transparent Data Encryption (TDE) was first introduced in SQL Server 2008(and above). Purpose was to protect data by encrypting the physical files data (mdf) and log (ldf) files (it does not encrypt the actual data stored within the database).

Steps necessary to enable TDE:

1. Open SQL Server Management studio
2. Create the Master Key:

USE Master;

GO

CREATE MASTER KEY ENCRYPTION

BY PASSWORD='InsertAStrongPasswordHere';

GO

3. Create Certificate which is protected by the master key:

CREATE CERTIFICATE TDECertificate

WITH

SUBJECT='Database_Encryption';

GO

4. Create the Database Encryption Key:

USE <DB>

GO

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER CERTIFICATE TDECertificate;

GO

5. Enable the Encryption:

ALTER DATABASE <DB>

SET ENCRYPTION ON;

GO

6. Backup the Certificate:

BACKUP CERTIFICATE TDECertificate

TO FILE = 'C:\temp\TDECertificate'

WITH PRIVATE KEY (file='C:\temp\TDE_CertKey.pvk',

ENCRYPTION BY PASSWORD='InsertAStrongPasswordHere')

7. How to restore the Certificate

USE Master;

GO

CREATE MASTER KEY ENCRYPTION

BY PASSWORD='InsertAStrongPasswordHere';

GO

USE MASTER

GO

CREATE CERTIFICATE TDECertificate

FROM FILE = 'C:\Temp\TDECertificate'

WITH PRIVATE KEY (FILE = 'C:\TDECert_Key.pvk',

DECRYPTION BY PASSWORD = 'InsertAStrongPasswordHere' );