FIM – Forefront Identity Manager Failed to Connect to SQL Server

FIM – Forefront Identity Manager Failed to Connect to SQL Server

ISSUE
=====
SharePoint 2010 connects to a SQL Instance
In SharePoint 2010 FIM is used for importing and synchronising user profiles by the User Profile Service Application. Starting the User Profile Synchronising Service (from Central Admin – System Settings – Mange Service on Server) Fail’s. Reviewing the Event log you receive the following error message:

Log Name:      Application
Source:        FIMSynchronizationService
Date:          13/07/2010 15:19:37
Event ID:      6324
Task Category: Server
Level:         Error
Keywords:      Classic
User:          N/A
Computer:      <SP2010NAMEE>.<DOMAINNAME>
Description:
The server encountered an unexpected error and stopped.
�
 “ERR: MMS(1804): sql.cpp(5580): Query (CSession::Open()) performed with error
ERR: MMS(1804): sql.cpp(5601): Error retrieving error
BAIL: MMS(1804): sql.cpp(568): 0x80230406 (An error has occurred at the store)
BAIL: MMS(1804): sql.cpp(384): 0x80230406 (An error has occurred at the store)
BAIL: MMS(1804): sql.cpp(256): 0x80230406 (An error has occurred at the store)
BAIL: MMS(1804): storeimp.cpp(272): 0x80230406 (An error has occurred at the store)
ERR: MMS(1804): server.cpp(294): Failed to connect to the database User Profile Service Application_SyncDB_2404860889364cf2a021eccb509327fd on <SQLSERVERNAME>
BAIL: MMS(1804): server.cpp(295): 0x80230406 (An error has occurred at the store)
BAIL: MMS(1804): server.cpp(3518): 0x80230406 (An error has occurred at the store)
BAIL: MMS(1804): service.cpp(1528): 0x80230406 (An error has occurred at the store)
ERR: MMS(1804): service.cpp(977): Error creating com objects. Error code: -2145188858. This is retry number 0.
ERR: MMS(1804): sql.cpp(5580): Query (CSession::Open()) performed with error
ERR: MMS(1804): sql.cpp(5601): Error retrieving error
BAIL: MMS(1804): sql.cpp(568): 0x80230406 (An error has occurred at the store)
BAIL: MMS(1804): sql.cpp(384): 0x80230406 (An error has occurred at the store)
BAIL: MMS(1804): sql.cpp(256): 0x80230406 (An error has occurred at the store)
BAIL: MMS(1804): storeimp.cpp(272): 0x80230406 (An error has occurred at the store)
ERR: MMS(1804): server.cpp(294): Failed to connect to the database User Profile Service Application_SyncDB_2404860889364cf2a021eccb509327fd on <SQLSERVERNAME>
BAIL: MMS(1804): server.cpp(295): 0x80230406 (An error has occurred at the store)
BAIL: MMS(1804): server.cpp(3518): 0x80230406 (An error has occurred at the store)
BAIL: MMS(1804): service.cpp(1528): 0x80230406 (An error has occurred at the store)
ERR: MMS(1804): service.cpp(977): Error creating com objects. Error code: -2145188858. This is retry number 1.
ERR: MMS(1804): sql.cpp(5580): Query (CSession::Open()) performed with error
ERR: MMS(1804): sql.cpp(5601): Error retrieving error
BAIL: MMS(1804): sql.cpp(568): 0x80230406 (An error has occurred at the store)
BAIL: MMS(1804): sql.cpp(384): 0x80230406 (An error has occurred at the store)
BAIL: MMS(1804): sql.cpp(256): 0x80230406 (An error has occurred at the store)
BAIL: MMS(1804): storeimp.cpp(272): 0x80230406 (An error has occurred at the store)
ERR: MMS(1804): server.cpp(294): Failed to connect to the database User Profile Service Application_SyncDB_2404860889364cf2a021eccb509327fd on <SQLSERVERNAME>
BAIL: MMS(1804): server.cpp(295): 0x80230406 (An error has occurred at the store)
BAIL: MMS(1804): server.cpp(3518): 0x80230406 (An error has occurred at the store)
BAIL: MMS(1804): service.cpp(1528): 0x80230406 (An error has occurred at the store)
ERR: MMS(1804): service.cpp(977): Error creating com objects. Error code: -2145188858. This is retry number 2.
ERR: MMS(1804): sql.cpp(5580): Query (CSession::Open()) performed with error
ERR: MMS(1804): sql.cpp(5601): Error retrieving error
BAIL: MMS(1804): sql.cpp(568): 0x80230406 (An error has occurred at the store)
BAIL: MMS(1804): sql.cpp(384): 0x80230406 (An error has occurred at the store)
BAIL: MMS(1804): sql.cpp(256): 0x80230406 (An error has occurred at the store)
BAIL: MMS(1804): storeimp.cpp(272): 0x80230406 (An error has occurred at the store)
ERR: MMS(1804): server.cpp(294): Failed to connect to the database User Profile Service Application_SyncDB_2404860889364cf2a021eccb509327fd on <SQLSERVERNAME>
BAIL: MMS(1804): server.cpp(295): 0x80230406 (An error has occurred at the store)
BAIL: MMS(1804): server.cpp(3518): 0x80230406 (An error has occurred at the store)
BAIL: MMS(1804): service.cpp(1528): 0x80230406 (An error has occurred at the store)
ERR: MMS(1804): service.cpp(977): Error creating com objects. Error code: -2145188858. This is retry number 3.
BAIL: MMS(1804): service.cpp(991): 0x80230406 (An error has occurred at the store)
Forefront Identity Manager 4.0.2450.5”

Resolution
=========

Somehow the SQL Instance information for the FIM Synchronising Service is missing

To resolve the issue add the SQLInstance name to the following Reg key:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINESYSTEMControlSet001servicesFIMSynchronizationServiceParameters]
"SQLInstance"="<InstanceName>"