Discovery Accelerator EV event log errors 34, 429 or 430 after SQL database migration

Problem

Event ID errors 34, 429 or 430 are thrown into the Application event log on the Accelerator server when a Master Key has created when entering Account Information in the DA client or Custodian Manager and the Accelerator databases are moved from one SQL server to another.

This issue has also been found to occur in Compliance Accelerator 14.4.0 with Event ID 28 and 41553 errors.

Error Message

Event ID:  34 - APP AT - Customer ID: 2 - An error occurred in ProfileSynchroniser::SynchroniseEmployeeProfile while retrieving properties. System.Data.SqlClient.SqlException: Please create a master key in the database or open the master key in the session before performing this operation.
The key 'EVAccelerators_Key' is not open. Please open the key before using it.
V-437-34

Event ID: 429 - APP AT - Customer ID: 2 - Error getting account information. System.Data.SqlClient.SqlException: Please create a master key in the database or open the master key in the session before performing this operation.
The key 'EVAccelerators_Key' is not open. Please open the key before using it.
V-437-429

Event ID:      430 - APP AT - Customer ID: 3 - Error updating account information. System.Data.SqlClient.SqlException (0x80131904): Please create a master key in the database or open the master key in the session before performing this operation.
The key 'EVAccelerators_Key_AES_256' is not open. Please open the key before using it.
   at Symantec.EnterpriseVault.DatabaseAccess.EVSqlBase.Do[T](Func`1 action)
   at Symantec.EnterpriseVault.DatabaseAccess.EVSqlDataAdapter.Fill(DataTable dataTable)
   at KVS.Accelerator.Common.AccountInformation.GetDS(Int32 AccountInformationID)
ClientConnectionId:eb0be524-b0e6-4788-93bb-5c9cdc571fa6
Error Number:15581,State:7,Class:16
V-437-429

Event ID:      28 - APP AT - Customer ID: 1 - An error occured in EmployeeSynchroniser::SynchroniseGroupMembers. System.Exception: An error occurred while trying to access the account information for the Custodian Manager synchronization account. This information has been encrypted by a SQL database master key and cannot be read. To resolve this issue, recreate the database master key and account information by following the instructions in article http://www.veritas.com/docs/TECH230285. 
   at KVS.Accelerator.Server.CentralProfileSynchroniser.SynchroniseGroup(TargetGroupRow synchRow)
V-437-28

Event ID:      41553 - APP AT - Customer ID: 1 - Error:
An error occurred while trying to access the account information for the Custodian Manager synchronization account. This information has been encrypted by a SQL database master key and cannot be read. To resolve this issue, recreate the database master key and account information by following the instructions in article http://www.veritas.com/docs/TECH230285. 
Reason: Please create a master key in the database or open the master key in the session before performing this operation.
The key 'EVAccelerators_Key_AES_256' is not open. Please open the key before using it.
Stack:    at Symantec.EnterpriseVault.DatabaseAccess.EVSqlBase.Do[T](Func`1 action)
   at Symantec.EnterpriseVault.DatabaseAccess.EVSqlDataAdapter.Fill(DataTable dataTable)
   at KVS.Accelerator.Common.AccountInformation.GetDS(Int32 AccountInformationID)
V-41553

Cause

A Master Key is created when entering Account Information in the DA Client or Custodian Manager to synchronize against an Active Directory domain or Lotus Domino directory using an account other than the Vault Service Account (VSA).  There are two types of Accelerator databases using SQL Server encryption: DA customer databases and the Custodian Manager database.  The Master Key is associated with the SQL Server hosting these databases.

If Accelerator databases are moved from one SQL server to another, the key becomes invalid and should be dropped before moving off of original SQL server, then recreated after moving to new SQL server.  If this is not done, event ID errors 34, 429 or 430 are thrown into the Enterprise Vault event log on the Accelerator server. 

Solution

The following steps will need to be performed on each DA customer database and the Custodian Manager database noted by the 'CustomerID' portion of the event log errors.

1) Determine the database name associated with the 'CustomerID' noted in the event log error by running the following SQL query against the DA Configuration database, replacing the # with the number associated with the 'CustomerID' in the error.

SELECT InitialCatalog
FROM tblCustomer
WHERE CustomerID = #

2) Change the focus of the SQL query pane by running the following SQL commands, replacing (Database name) with the InitialCatalog information returned by the SQL query in Step 1.

USE (Database name
Go

3) Check what is in tblAccountInformation in each database by running the following SQL query. Note that the encrypted AccountUserName and AccountPassword columns will contain unreadable characters. 

SELECT * from tblAccountInformation 

4) Using the AccountInformationID value returned by the SQL query in Step 3, delete the encrypted account(s) by running the following SQL script.  

5) Remove old keys and certificates by running the following SQL commands replacing EVKeyName with the key name referenced in the Event Log entry (either EVAccelerators_Key or EVAccelerators_Key_AES_256).

DROP SYMMETRIC KEY EVKeyName
DROP CERTIFICATE EVAccelerators_Certificate
DROP MASTER KEY

For example, if the Event Log error specifies the EVAccelerators_Key_AES_256 key, the commands would be:

DROP SYMMETRIC KEY EVAccelerators_Key_AES_256
DROP CERTIFICATE EVAccelerators_Certificate
DROP MASTER KEY

Note: The following steps are optional for Compliance Accelerator but required for Discovery Accelerator.

6) Recreate the database master key, the certificate and the symmetric key used for encryption by executing the following stored procedure, replacing 'TheMaskerKeyPasswordHere' with the password to be used for the master key.

7) Check that the keys and certificate have been recreated. The key will not open if there is an error in the hierarchy. Use the following SQL queries, replacing EVKeyName with the key name referenced in the Event Log entry (either EVAccelerators_Key or EVAccelerators_Key_AES_256), to test if the keys will open.

OPEN SYMMETRIC KEY EVKeyName
DECRYPTION BY CERTIFICATE EVAccelerators_Certificate

For example, if the Event Log error specifies the EVAccelerators_Key_AES_256 key, the commands would be:

OPEN SYMMETRIC KEY EVAccelerators_Key_AES_256
DECRYPTION BY CERTIFICATE EVAccelerators_Certificate

8) Close the EVAccelerators_Key or EVAccelerators_Key_AES_256 by executing the following SQL command, replacing EVKeyName with the key name referenced in the Event Log entry (either EVAccelerators_Key or EVAccelerators_Key_AES_256).

CLOSE SYMMETRIC KEY EVKeyNaMe

For example, if the Event Log error specifies the EVAccelerators_Key_AES_256 key, the command would be:

CLOSE SYMMETRIC KEY EVAccelerators_Key_AES_256

9) Use the DA client application interface to recreate the account(s) for the customer database if needed:

Configuration tab > Account Information sub-tab > New button

10) Repeat the above steps for each DA Customer database.

11) Use the Enterprise Vault Custodian Manager web site to recreate the account(s) for the Custodian Manager database if needed:

Configure Synchronization Accounts to be used for Custodians link

12) Restart Enterprise Vault Accelerator Manager Service (EVAMS) on the DA server to verify that certificate and key errors are no longer being thrown.