Set up Transparent Data Encryption

Ensure that no other user is running any Process Runner product until the TDE implementation process is completed.

To enable Transparent Data Encryption (TDE) for SQL Database,

1. Open SQL Server Management Studio and connect to your SQL Server instance.
2. Right-click your server instance in Object Explorer, and then select New Query.
3. In the query window, perform the following:
   1. To create the master key with the password provided, enter the following query:

      USE master

      CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<YOURPASSWORD>’

   2. To create the certificate protected by master key, enter the following query:

   USE master

   CREATE CERTIFICATE <CertificateName> WITH SUBJECT = 'SQL Server TDE Certificate', EXPIRY_DATE = '<DATE>'

   Note: Date format should be in YYYYMMDD format.

   3. To create the database encryption key, enter the following query:

   USE [Database Name]

   CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE <Certificate Name>

   4. To enable encryption for the database that you want to encrypt, enter the following query:

   ALTER DATABASE [Database Name] SET ENCRYPTION ON;

   5. To back up the certificate or key, enter the following query:

   USE Master

   BACKUP CERTIFICATE <Certificate Name> TO FILE = 'E:\TDE1\Sample.cert' WITH PRIVATE KEY ( FILE = 'E:\TDE1\sample.key', ENCRYPTION BY PASSWORD = 'Password')

4. To run on the server where the database must be restored, perform the following:
   1. Create the same master key that is created on SQL Server using the following query:

   Use master CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'same password’

   2. Copy the certificate from the backup server using the following query:

   USE [Database Name]

   CREATE CERTIFICATE <CertificateName> FROM FILE ='Path\CertificateName.cert' WITH PRIVATE KEY (FILE ='Path\CertificateName.key', DECRYPTION BY PASSWORD = 'YourPassword');