Table of Contents

Symptoms / Errors

EventID 0, Microsoft Resource Management Service (Application log)

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. 

or

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

Stop FIM Service

If the FIM Service is running, stop the service.

Check and kill active connections

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

SQL Query

You can check active connections by running the following command:

select spid,hostname,loginame,cmd,db_name(dbid) as dbname, status

from master.dbo.sysprocesses
where datediff(dd,login_time,getdate()) = 0 and db_name(dbid)='FIMService'

Activity Monitor

For SqlServer 2008 R2

  1. Open SQL Server Management Studio
  2. Go to the ObjectExplorer and right-click on the FIMService database:, then click Activity Monitor (in the default ribbon)
  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   

Enable SQL Service Broker

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.

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

References