Event ID 41596 Failed to refresh the Compliance Sampling configuration from the Compliance Accelerator configuration database

Problem

After upgrading Enterprise Vault (EV) and Compliance Accelerator (CA) to 14.3.0 or higher, warning Event ID 41596 appears periodically in the EV server's EV Event Log and two instances of Event ID 1000 for the .NET Runtime source in the Application Event Log of the CA server are thrown every hour by default. For more information about the .NET Runtime Event ID 1000 errors - see Related Articles below.

Error Message

Log Name:      Veritas Enterprise Vault
Source:        Enterprise Vault 
Event ID:      41596
Task Category: Compliance Sampler
Level:         Warning
Keywords:      Classic
Description:
Failed to refresh the Compliance Sampling configuration from the Compliance Accelerator configuration database '<Accelerator_Configuration_database_name>'.  Compliance Sampling will continue to use its existing configuration for a set period, if available (6 hours by default).  After this period Enterprise Vault will stop the Storage service if any attempt is made to archive an item to ensure that no items are precluded from sampling.

Ensure that the database is correctly configured in the Accelerator Manager website and is online and accessible to the Vault Service account.

SQL Instance: <SQL_server_name\Instance:Port> or <SQL_server_listener_name:Port>
Database: <Accelerator_Configuration_database_name>
Error: Error in getting response from [https://<Accelerator_server_name>.<Domain_name>.<Domain_suffix>:449/api/learning-enabled-departments/customer/X]. Status code: [Forbidden] : [Forbidden] 

V-437-41596

Cause

The warning is a Kerberos double hop error logged by the Intelligent Review Application Programming Interface (IRAPI) when the Kerberos constrained trusted delegation is not set correctly between the CA Server and the SQL server servicing the CA databases. The issue is also mentioned in the version-specific CA Installation Guide under the Troubleshooting Appendix - see Related Articles below.

Note: The error above can also occur when the password for the Vault Service Account is changed and can no longer access the CA Configuration database.

Solution

First confirm if Intelligent Review on the CA Server is using the Fully Qualified Domain Name (FQDN) of the SQL Server, and not the IP Address or hostname/NetBIOS name.

1. For the Configuration database:

1.1. Navigate to the \Veritas Intelligent Review\IR.APIEndPointCA folder in the CA installation folder, typically at C:\Program Files (x86)\Enterprise Vault Business Accelerator.

1.2. Open the appsettings.json file in a simple text editor such as Notepad.

1.3. Check the ConfigDBConnection key. If the connection string lists the IP Address or hostname/NetBIOS name, edit to list the SQL Server's FQDN. Note the FQDN can list the SQL instance and/or custom port if so configured. Once edited, save the file and restart the CA Services: Enterprise Vault Accelerator Manager Service, Enterprise Vault IR Classifier Service, Enterprise Vault IR Model Builder Service.
 

2. For the Customer database(s):

2.1. Use SQL Server Management Studio to connect to the database server servicing the Customer databases with an account having rights to edit database table entries.

2.2. Expand the Configuration database, then expand Tables.

2.3. Right-click tblCustomer and click on Edit Top 200 Rows.

2.4. Check the entries in the Server column for the affected Customer database(s). If the entry lists the IP Address or hostname/NetBIOS name, edit to list the SQL Server's FQDN. Note the FQDN can list the SQL instance and/or custom port if so configured. Once edited, click on the asterisk at the bottom of the first (empty) column to save the changes.

Then correctly set the Kerberos constrained trusted delegation on the SQL Server service account if not the local Network Service Account, and on the CA server properties in Active Directory. Service Principal Names (SPNs) are required to be properly set for the SQL Service. When a SQL Server Always On Availability Group (AOAG) is used to host the CA Configuration and Customer databases, the SQL AOAG Listener's SPNs must be properly created to allow delegation of them to the SQL Service account and CA server in Active Directory Users and Computers (ADUC).

1. Open the Services MMC on the SQL server(s) and determine the Log On account for the service named SQL Server. This service name will be followed by the instance name or MSSQLSERVER in parentheses if using the default SQL instance. Example using the default instance: SQL Server (MSSQLSERVER)
 

2. Set the SPNs for the SQL servers or SQL AOAG Listener:

2.1.Download and install the Microsoft Kerberos Configuration Manager for SQL Server on the SQL server or SQL servers if using AOAG. The utility can be downloaded from:
Main URL: https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/connect/using-kerberosmngr-sqlserver
Direct download link: https://www.microsoft.com/en-us/download/details.aspx?id=39046

2.2. Run the KerberosConfigMgr.exe utility from the installation folder (default location: C:\Program Files\Microsoft\Kerberos Configuration Manager for SQL Server\).

2.3. Click Connect | Connect to connect to the local SQL Server using the logged-on user account. The Server name: field can also be populated with the AOAG Listener name if SQL AOAG is in use for the CA databases. If the logged in user is the same as the Log On account used for the SQL Server service, the User name: and Password: fields do not need to be populated.

2.4. Go to the SPN tab. The entries in this tab will include 2 entries for each SQL product, with one entry having the TCP/IP Port used by the SQL Service and the other entry without the port in the Required SPN column.  Check any entries with Status Missing. Click the Generate button under SPN Script to generate the fix command if needed for historical purposes, or click Generate All to script all fix actions. Click the Fix button under Action for each Status Missing entry to add the SPN. Repeat for all entries with Status Missing or click the Fix All button.  Note: Only the AOAG Listener entries require the SPN to be present when the CA databases are hosted on SQL AOAG.

2.5. If using AOAG, the above steps should have added the SPNs for the Listener and active replicas. To add the SPNs for the inactive replicas, click Connect, enter the SQL server information for the inactive replica and repeat the steps for all inactive replicas.

2.6. If not using AOAG, to add the SPNs for any required SQL servers that were not previously listed, click Connect, enter the SQL server information for the required SQL servers and repeat the steps for all required SQL servers.

2.7. Review the Delegation tab and verify the Delegation Type is None (for local accounts such as Network Service) or Constrained (for domain accounts), and the Details shows No obvious delegation issues.

3. Open the Active Directory Users and Computers MMC on a domain controller and go to Computers | Double-click the CA server to bring up the Properties | Delegation.

3.1 Select:
- Trust this computer for delegation to specified services only.
- Use any authentication protocol.

3.2. Click Add | In Add Services click Users or Computers | In Select Users or Computers click Advanced | Find Now to list all available objects.

3.3. Select the Log On account for the SQL Server service | OK | OK. If the Log On account is Network Service, this account cannot be listed for Delegation - select the SQL server(s) from the list instead.

3.4. In the Add Services pane, review the MSSQLSvc entries. If SQL AOAG is in use to host the CA databases, only select the AOAG Listener entries in the list. If the CA databases are not referenced by an AOAG Listener or AOAG is not configured, select all MSSQLSvc entries before clicking OK.

3.5. In the CA server Properties pane, click Apply | OK.

4. If the Log On account for the SQL Server service is not a local account, such as Network Service, and is a domain account, go to Active Directory Users and Computers | Users | Double-click the Log On account for the SQL Server service to bring up the Properties | Delegation.

4.1. Select:
- Trust this user for delegation to specified services only.
- Use any authentication protocol.

4.2. Click Add | In Add Services click Users or Computers | In Select Users or Computers click Advanced | Find Now to list all available objects.

4.3. Select the Log On account for the SQL Server service | OK | OK.

4.4. In the Add Services pane, review the MSSQLSvc entries. If SQL AOAG is in use to host the CA databases, only select the AOAG Listener entries in the list. If the CA databases are not referenced by an AOAG Listener or AOAG is not configured, select all MSSQLSvc entries. Click OK.

4.5. In the user Properties pane, click Apply | OK.

5. Restart the required services and verify the warning no longer occurs:

5.1. Domain Controller: Restart the Active Directory Domain Services service on the domain controller (acknowledge any prompts to restart any dependent services).

5.2. CA server: Stop all CA services, restart IIS, then start all CA services.

5.3. All EV servers: Restart the EV Storage Service.

5.4. Check the EV Event Logs on the EV Storage servers to verify Warning Event ID 41596 is no longer seen.  These steps should also resolve the .NET Runtime Event ID 1000 errors in the CA server's Application Event Log.

5.5. Browse to the URL listed in the Warning Event ID 41596 from the EV server. A successful connection should display an white screen with {} or other characters, and should not display any errors. The white screen and lack of errors indicates a successful connection to the IRAPI endpoint.

If the Event Log Warning is still seen and AOAG is in use and the CA databases are referenced by an AOAG Listener, check the Service Principal Names (SPN) associated to the SQL Server service Log On account and remove any non-AOAG entries:

1. Open an administrative/elevated command prompt on the domain controller: Start | right-click Command Prompt | Run as administrator.

2. Check the existing SPNs associated to the SQL Server service Log On account by editing and executing the following command for the Log On account:

setspn -L <domain>\<username>

3. Review the output and identify any non-AOAG Listener SQL server entries.

Example output from a test environment lab:

Registered ServicePrincipalNames for CN=admin,CN=Users,DC=domain,DC=com:
        MSSQLSvc/aoag1_listener.domain.com
        MSSQLSvc/aoag1_listener.domain.com:1433
        MSSQLSvc/SQL2.domain.com:1433
        MSSQLSvc/SQL2.domain.com
        MSSQLSvc/SQL1.domain.com:1433
        MSSQLSvc/SQL1.domain.com

4. Remove any non-AOAG Listener SQL server entries by editing and executing the following command for the non-AOAG Listener SQL server entries and the Log On account, including those listing a port:

setspn -D <non-AOAG Listener SQL server entry> <domain>\<username>

setspn -D <non-AOAG Listener SQL server entry>:<port> <domain>\<username>

Example output:

C:\Windows\system32>setspn -D MSSQLSvc/SQL1.domain.com domain\admin
Unregistering ServicePrincipalNames for CN=admin,CN=Users,DC=domain,DC=com
        MSSQLSvc/SQL1.domain.com
Updated object

C:\Windows\system32>setspn -D MSSQLSvc/SQL1.domain.com:1433 domain\admin
Unregistering ServicePrincipalNames for CN=admin,CN=Users,DC=domain,DC=com
        MSSQLSvc/SQL1.domain.com:1433
Updated object

5. Re-run the setspn -L <domain>\<username> command and review the output to verify no non-AOAG Listener SQL server entries are listed. If listed, repeat the above steps to remove them. If not listed, close the command prompt and repeat the services restart as above.