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
- Select name, is_encrypted from sys.databases
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