Enable Transparent Data Encryption (TDE) on SQL Server databases

Transparent Data Encryption (TDE) is a method used to encrypt data-at-rest within a database, ensuring that stored data remains encrypted on disk and is only decrypted when accessed by authorized users or applications. So you can safely enable this option on your DeMaSy & Licence databases.

In order to avoid any right or authorization issues we recommend to use the SA user to execute this tutorial.

Enabling TDE

To enable TDE for DeMaSy & Licence databases on your SQL Server follow these instructions:

  • Check the encrypted databases
    • Select name, is_encrypted from sys.databases
    • DeMaSy & Licence are supposed to be not encrypted at this point
  • Make the full database backup of DeMaSy & Licence
    • — DeMaSy
    • BACKUP DATABASE [DeMaSy] TO DISK = N’D:\SQLBackups\DeMaSy.bak’ WITH NOFORMAT, NOINIT,
    • NAME = N’DeMaSy-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10
    • GO
    • — Licence
    • BACKUP DATABASE [Licence] TO DISK = N’D:\SQLBackups\Licence.bak’ WITH NOFORMAT, NOINIT,
    • NAME = N’Licence-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10
    • GO
  • Create a database master key
    • USE master;
    • Go
    • CREATE MASTER KEY
    • ENCRYPTION BY PASSWORD = ‘YourEncryptionPassword’;
    • GO
  • Create the certificate
    • USE master;
    • GO
    • CREATE CERTIFICATE TDE_Certificate
    • WITH SUBJECT=’Certificate for TDE’;
    • GO
  • Create the database encryption key for DeMaSy & Licence
    • — DeMaSy
    • USE DeMaSy
    • GO
    • CREATE DATABASE ENCRYPTION KEY
    • WITH ALGORITHM = AES_256
    • ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
    • — Licence
    • USE Licence
    • GO
    • CREATE DATABASE ENCRYPTION KEY
    • WITH ALGORITHM = AES_256
    • ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
  • Back up the certificate and the private key associated with the certificate
    • USE master;
    • GO
    • BACKUP CERTIFICATE [TDE_Certificate]
    • TO FILE = ‘D:\CertificatesBackup\TDE_Certificate_For_DeMaSyAndLicenceDatabases.cer’
    • WITH PRIVATE KEY (file=’D:\CertificatesBackup\Keys\TDE_DeMaSyAndLicence_private_CertKey.pvk’,
    • ENCRYPTION BY PASSWORD=’YourEncryptionPassword’);
  • Turn on encryption on databases
    • — DeMaSy
    • ALTER DATABASE DeMaSy
    • SET ENCRYPTION ON
    • — Licence
    • ALTER DATABASE Licence
    • SET ENCRYPTION ON
  • Check if encryption is enabled
    • Select name, is_encrypted from sys.databases
      • Now you can see 1 instead 0 the DeMaSy & Licence records
    • Select * from sys.certificates

Disable TDE

To disable TDE on DeMaSy & Licence databases follow these instructions:

  • Turn of encryption on databases
    • — DeMaSy
    • Use DeMaSy
    • Go
    • Alter Database DeMaSy Set Encryption off
    • — Licence
    • Use Licence
    • Go
    • Alter Database Licence Set Encryption off
  • Remove the encryption key on databases
    • — DeMaSy
    • Use DeMaSy
    • go
    • Drop database encryption key
    • — Licence
    • Use Licence
    • go
    • Drop database encryption key
  • Remove the certificate
    • Use Master
    • Go
    • Drop certificate TDE_Certificate
  • Optional, remove the master key
    • Use master
    • Go
    • Drop master key