After restoring the ECAT database from a backup and trying to enable the MS SQL Server Broker service, it returns the error, "Cannot enable the Service Broker in database "{database_name}" because the Service Broker GUID in the database ({GUID_value1}) does not match the one in sys.databases ({GUI_Value2})."
Image description
This error will occur if the MS SQL Server services are stopped without first detaching the ECAT database.
The GUID value in the restored ECAT database files will not match the old GUID value stored in the sys.databases table.
Alternative 1: Create a new Broker instance
1. In services.msc stop the following services:
RSA ECAT Server
RSA ECAT API Server
SQL Server Agent (MSSQLSERVER)
2. In SQL Server Management Studio run the following SQL commands:
USE master
GO
ALTER DATABASE {Database_name} SET NEW_BROKER WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE {Database_name} SET ENABLE_BROKER;
GO
SELECT name, is_broker_enabled FROM sys.databases WHERE name LIKE 'ECAT%'
GO
Substitute
{Database_name} with the correct ECAT database name (ECAT$PRIMARY or ECAT$SECONDARY).
Confirm the Broker Service is now enabled - the is_broker_enabled column should show the value "1" for the ECAT database.
Image description3. In services.msc start the following services:
SQL Server Agent (MSSQLSERVER)
RSA ECAT API Server
RSA ECAT Server
Alternative 2: Detach and Re-attach the ECAT database
Or detach the ECAT database - this will clear the old GUI value from the sys.databases table - then re-attach the ECAT database to save the new GUID value in the sys.databases table.
1. In services.msc stop the following services:
RSA ECAT Server
RSA ECAT API Server
SQL Server Agent (MSSQLSERVER)
2. In SQL Server Management Studio
Right-click the ECAT database and select, Tasks -> Detach, OK
Right-click Databases and select, Attach, Add
Browse to the location of the ECAT Database's .mdf file, OK
3. Now the Broker service can be enabled, see KB#
SQL Server Service Broker for the current database is not enabled in RSA ECAT 4.1.x.
4. In services.msc start the following services:
SQL Server Agent (MSSQLSERVER)
RSA ECAT API Server
RSA ECAT Server