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' );