Enabling TDE on an EDM installed on a single SQL server

Last Updated : Apr 04, 2022 |

About this task

Use this procedure to enable Transparent Data Encryption (TDE) on the Context Store External Data Mart when it is it is deployed standalone on a single SQL server.

This can be either one of the following configurations:

  • Standalone EDM non-DB HA: EDM is deployed alone on one SQL server (within an Oceana deployment only).

  • Co-resident with ACM: EDM is deployed on only one SQL server (lab deployment only, not supported in production).

Follow this procedure for enabling TDE on the EDM when it is installed on only one SQL server.

Procedure

  1. Establish a remote desktop connection to the SQL server.
  2. Log in to the local instance of SQL server in SQL Server Management Studio (SSMS) as the domain user configured to use the SQL server service.

    The domain user can be found in the Log On As column under SQL Server Services. For more information on finding the domain user, see Finding the Avaya Control Manager SQL Server domain user.

  3. In the Object Explorer pane, right-click the local SQL server instance and click New Query.
  4. To create the master key for the SQL Server, run the following T-SQL commands and click Execute: 
    USE MASTER
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<enter strong password>'
GO
    Note:

    If a master key is already created on the SQL server, an error is displayed. If you know the password for the existing master key, continue to the next step. If you do not know the password, see Creating a new master key with a known password.

  5. To create a new certificate for the EDM and also a backup certificate and private key, run the following T-SQL commands and click Execute:
    Note:

    It is recommended to save the backup certificate and the private key off the SQL server.

    For example,

    CREATE CERTIFICATE <name of new EDM certificate>
WITH SUBJECT = '<enter any subject e.g. CSEDM TDE Certificate>'
GO
USE MASTER
GO
BACKUP CERTIFICATE <name of new EDM certificate>
TO FILE = '<enter file location, for example C:><name of new EDM certificate>_File.cer'
WITH PRIVATE KEY (FILE = '<enter file location, for example C:><name of new EDM certificate>_Key.pvk' ,
ENCRYPTION BY PASSWORD = '<enter strong password>' )
GO
  6. To enable encryption on the SQL server, run the following commands to create the encryption key of the EDM: 
    USE <name of EDM DB>
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE <name of new EDM certificate>
GO
  7. To enable TDE on the EDM, run the following T-SQL commands: 
    ALTER DATABASE <name of EDM DB>
SET ENCRYPTION ON
GO
  8. To check if the encryption was successful, run the following T-SQL commands on the SQL server: 
    SELECT
db.name,
db.is_encrypted,
dm.encryption_state,
dm.percent_complete,
dm.key_algorithm,
dm.key_length
FROM sys.databases db
LEFT OUTER JOIN sys.dm_database_encryption_keys dm
ON db.database_id = dm.database_id;

    The preceding commands return a number that represents the encryption_state for the EDM (0-6). For more information about the encryption state codes, see https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-database-encryption-keys-transact-sql?view=sql-server-ver15.

    For more information on how to enable TDE on ACM databases, see Maintaining and Troubleshooting Avaya Control Manager.

    Note:

    Keep valid backups of the following files in case of disaster:

    • The certificate that encrypts the DEK

    • The password to the private key of the TDE certificate

    • The password to decrypt the master key in the master database

    • A healthy backup of the system and user databases

    For information on recovering a TDE enabled database when the user does not have various combinations of the master key password, a backup of the master database, backups of the certificate and private key, password for the private key, or password to decrypt master database, see: https://techcommunity.microsoft.com/t5/sql-server-support-blog/recover-tde-databases-in-disaster-recover-steps-for-3-common/ba-p/2963600.