SQL Server Service Broker for the current database is not enabled in RSA RSA NetWitness Endpoint 4.x

[#1]

Article Number

000030413

Applies To

RSA Product Set:  RSA NetWitness Endpoint
RSA Version/Condition: 4.x
Platform: Windows, MS SQL Server
 

Issue

After moving the RSA NetWitness Endpoint database files (for example ECAT$PRIMARY.mdf and ECAT$PRIMARY_log.ldf) and re-attaching to the RSA NetWitness Endpoint SQL Server database, the RSA RSA NetWitness Endpoint Console Server service now stops immediately when trying to start it.

The ConsoleServer-Error.log file shows an error message like:
 

4/06/2015 10:13:49 AM
[6] System.InvalidOperationException:
The SQL Server Service Broker for the current database is not enabled, and as a result query notifications are not supported.  Please enable the Service Broker for this database if you wish to use notifications.
   at SqlDependencyProcessDispatcher.SqlConnectionContainer..ctor(SqlConnectionContainerHashHelper hashHelper, String appDomainKey, Boolean useDefaults)
   at SqlDependencyProcessDispatcher.Start(String connectionString, String& server, DbConnectionPoolIdentity& identity, String& user, String& database, String& queueService, String appDomainKey, SqlDependencyPerAppDomainDispatcher dispatcher, Boolean& errorOccurred, Boolean& appDomainStart, Boolean useDefaults)
   at SqlDependencyProcessDispatcher.StartWithDefault(String connectionString, String& server, DbConnectionPoolIdentity& identity, String& user, String& database, String& service, String appDomainKey, SqlDependencyPerAppDomainDispatcher dispatcher, Boolean& errorOccurred, Boolean& appDomainStart)
   at System.Data.SqlClient.SqlDependency.Start(String connectionString, String queue, Boolean useDefaults)
   at a.b.X᜕..ctor(String A_0, String A_1, String A_2, String A_3)
   at a.b.Xᝌ.ᜂ()
   at a.b.Xᝌ.ᜃ()

 

Cause

The Microsoft SQL Server Broker Service provides messaging and queuing functions between instances.

This functionality helps in sending messages to remote databases on different servers, and in processing messages within a single database. In order to send messages between instances, the Service Broker uses TCP/IP.

The Microsoft SQL Server Broker Service is required in the ECAT$PRIMARY and ECAT$SECONDARY databases.

The Microsoft SQL Server Broker Service is automatically disabled when moving the RSA NetWitness Endpoint database or when restoring the RSA NetWitness Endpoint database from a backup.

Resolution

Alternative 1: Enable the Broker service via the GUI

Enable the SQL Server Broker service on the RSA NetWitness Endpoint primary server for the database ECAT$PRIMARY, and if it exists, the ECAT Secondary Server database ECAT$SECONDARY.

1. Start the SQL Server Management Studio program
2. Right-click the ECAT$PRIMARY, or ECAT$SECONDARY database
3. Go to Properties
4. Select Options, and scroll down to the Service Broker.
5. Set Broker Enabled to True
6. Click OK.

Image description

 

Alternative 2: Enable the Broker service via SQL

Another option is to create a SQL query to enable the Broker Service with the following SQL command,
 

ALTER DATABASE [DBNAME] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE

Where [DBNAME] = ECAT$PRIMARY, or ECAT$SECONDARY.

Notes

If enabling the Broker service fails due to a "GUID does not match" error, see article RSA ECAT 4.x SQL Server is unable to enable SQL Server Broker service, GUID does not match.