Table of Contents

Symptoms / Error 

Microsoft.ResourceManagement.Service: System.InvalidOperationException: The SQL Server Service Broker must be enabled on the Forefront Identity Manager Service database.  Refer to the documentation of the SQL Server Service Broker, or the Transact-SQL ALTER DATABASE statement, for instructions on how to enable it. 
   at Microsoft.ResourceManagement.Data.DataAccess.ValidateConnectionString(String connectionString, Boolean validateBroker)
   at Microsoft.ResourceManagement.Data.DatabaseConnection.InitializePrimaryStoreConnectionString()
   at Microsoft.ResourceManagement.Data.DatabaseConnection.get_ConnectionString()
   at Microsoft.ResourceManagement.Data.DatabaseConnection.Open(DataStore store)
   at Microsoft.ResourceManagement.Data.TransactionAndConnectionScope..ctor(Boolean createTransaction, IsolationLevel isolationLevel, DataStore dataStore)
   at Microsoft.ResourceManagement.Data.TransactionAndConnectionScope..ctor(Boolean createTransaction)
   at Microsoft.ResourceManagement.Data.DataAccess.GetDatabaseVersion(Int32& databaseVersion, String& databaseBinaryVersion)
   at Microsoft.ResourceManagement.Service.PlatformBasics.CheckDatabaseVersion()
   at Microsoft.ResourceManagement.Service.PlatformBasics.Initialize(Boolean isService)
   at Microsoft.ResourceManagement.Service.Application.CreatePlatformBasics(Boolean initialize, Boolean isService)
   at Microsoft.ResourceManagement.Service.Application.Start()

Solution

1. If the FIM Service is running, stop the service.


1. In the FIMService database, you can enable the SQL Server Service Broker using the following command from the SQL Server Management Studio:
ALTER DATABASE [FIMService] SET ENABLE_BROKER WITH NO_WAIT
or
2. use SQL Server Management Studio to set the option Broker Enabled to True.

noteNote
The Service Broker must be enabled after every restore of the FIM database.

 

noteImportant
The alter query may fail if there are active connections to the database.

 

 You can check active connections by running the following command:

select spid,hostname,loginame,cmd,db_name(dbid) as dbname, status
from master.dbo.sysprocesseswhere datediff(dd,login_time,getdate()) = 0 and db_name(dbid)='FIMService'

Killing active connections

For SqlServer 2008 R2

  1. Open SQL Server Management Studio
  2. Go to the ObjectExplorer and right-click on the FIMService database:, click Activity Monitor
  3. This will open the details in the right side window:
  4. Now right-click on the process to be killed and select Kill Process.

For SQL Server 2005

In SQL Server 2005 you can find the activity monitor in the ObjectExplorer under the Connection > Management > Activity monitor:
Double-click and there will be a new window that will show you the current process on each database. Right-click on the desired item then select kill process   

References: