1. TDE encryption in SQL Server, encrypts the data and log files. This enables software developers to encrypt data by using encryption algorithms such as AES and 3DES without changing the existing application. This means when TDE is enabled on the Change Auditor database, the product will continue to work seamlessly (no changes required). TDE can be enabled by executing the mentioned script below in SQL Server directly. There are no settings or changes required from the Change Auditor client application.
For further reading : Transparent Data Encryption (TDE)
2. Change Auditor does not allow addition of parameters in the connection string.
Following are the steps to enable TDE on existing Change Auditor database.
-- Create encryption password and certification. Make sure to backup certification and password.
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '$VERY_STRONG_PASSWORD$';
GO
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'MY CERTIFICATE FOR TDE ENCRYPTION';
GO
--- Create encryption key
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
GO
-- Enable encryption on Change Auditor database.
use ChangeAuditor
GO
ALTER DATABASE ChangeAuditor
SET ENCRYPTION ON;
GO
-- Verify that Change Auditor database is encrypted or in progress by looking at encryption_state column value
select DEK.database_id, SYSDB.name, DEK.encryption_state, DEK.encryptor_type, DEK.key_algorithm, DEK.key_length, DEK.encryptor_thumbprint
from sys.dm_database_encryption_keys DEK
INNER JOIN master.dbo.sysdatabases SYSDB on SYSDB.dbid = DEK.database_id
-- encryption_state
-- 0 = No database encryption key present, no encryption
-- 1 = Unencrypted
-- 2 = Encryption in progress
-- 3 = Encrypted
-- 4 = Key change in progress
-- 5 = Decryption in progress
-- 6 = Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)
