Enabling TDE on an EDM installed on a cluster of SQL servers

Last Updated : Apr 04, 2022 |
Prolog information
Metadata
Keywords:
Use this procedure to enable Transparent Data Encryption (TDE) on the Context Store External Data Mart when it is deployed in a database HA configuration using AlwaysOn Availability Groups.
You can enable TDE on EDM when it is deployed:
  • Co-resident with the Avaya Control Manager databases in a cluster of two SQL servers using an AlwaysOn Advanced Availability Group (AAG).
  • Standalone (no ACM DBs installed on the SQL server) in a cluster of two SQL servers using an AlwaysOn Basic Availability Group (BAG).
Before you begin
Ensure that the EDM is added to the Availability Group.
  1. Do the following on the Primary SQL server:
    1. 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.
      ADDITIONAL INFORMATION:
      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.
    2. In the Object Explorer pane, right-click the local SQL server instance and click New Query.
    3. To create the master key for the SQL server, run the following T-SQL commands and click Execute:
      ADDITIONAL INFORMATION: 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.
    4. To create a certificate for the EDM and also a backup certificate and private key, run the following T-SQL commands and click Execute:
      ADDITIONAL INFORMATION:
      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 for the EDM Availability Group>'
      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
    5. Copy <enter file location, for example C:><name of new EDM certificate>.cer and <enter file location, for example C:><name of new EDM certificate>.pvk from the primary to the secondary SQL server.
  2. Do the following on the Secondary SQL server:
    1. Log in to the local instance of the SQL server in SQL Server Management Studio (SSMS) as the domain user configured to use the SQL server service.
      ADDITIONAL INFORMATION:
      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.
    2. In the Object Explorer pane, right-click the local SQL server instance and click New Query.
    3. To create the master key for the SQL server, run the following T-SQL commands and click Execute:
      ADDITIONAL INFORMATION: USE MASTER
      GO
      CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<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.
    4. To create a certificate for the EDM, run the following T-SQL commands and click Execute:
      ADDITIONAL INFORMATION: USE MASTER
      GO
      CREATE CERTIFICATE <name of new EDM certificate as used on the Primary SQL Server>
      FROM FILE = '<enter file location, for example C:><name of new EDM certificate as used on the Primary SQL Server>.cer'
      WITH PRIVATE KEY (FILE = '<enter file location, for example C:><name of new EDM certificate as used on the Primary SQL Server>.pvk',
      DECRYPTION BY PASSWORD = '<strong password (same password used to encrypt the private key on the Primary SQL Server>' );
  3. To enable encryption on the Primary SQL server, run the following commands to create the encryption key of the EDM:
    ADDITIONAL INFORMATION:
    Note:
    Run these commands only on the Primary SQL server.
    USE <name of EDM DB>
    GO
    CREATE DATABASE ENCRYPTION KEY
    WITH ALGORITHM = AES_256
    ENCRYPTION BY SERVER CERTIFICATE <name of new EDM certificate>
    GO
  4. To enable TDE on the EDM, run the following T-SQL commands:
    ADDITIONAL INFORMATION:
    Note:
    Run this command only on the Primary SQL server.
    ALTER DATABASE <name of EDM DB>
    SET ENCRYPTION ON
    Note:
    It takes approximately one hour to encrypt a 300 GB EDM data file.
  5. To check if the encryption was successful, run the following T-SQL commands on the primary and secondary SQL servers:
    ADDITIONAL INFORMATION: 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 enabling 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.