000030413 - SQL Server Service Broker for the current database is not enabled in RSA ECAT 4.x

Document created by RSA Customer Support Employee on Oct 3, 2016
Version 1Show Document
  • View in full screen mode

Article Content

Article Number000030413
Applies ToRSA Product Set: ECAT
RSA Version/Condition: 4.x
Platform: Windows, MS SQL Server
 
IssueAfter moving the ECAT database files (for example ECAT$PRIMARY.mdf and ECAT$PRIMARY_log.ldf) and re-attaching to the ECAT SQL Server database, the RSA ECAT 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ᝌ.ᜃ()


 
CauseThe 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 ECAT database or when restoring the ECAT database from a backup.
Resolution

Alternative 1: Enable the Broker service via the GUI


Enable the SQL Server Broker service on the ECAT 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 the Broker Enabled to True
  6. OK
User-added image
 

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.
NotesIf enabling the Broker service fails due to a "GUID does not match" error then see KB# RSA ECAT SQL Server is unable to enable SQL Server Broker service, GUID does not match

Attachments

    Outcomes