Share via

FIM Troubleshooting: Synchronization Service setup is having trouble contacting SQL server

  • Article

Case description

When you install the FIM Synchronization service (FIM R2) , it fails with an error that it cannot connect to the SQL server.

You can run into this error even when SQL is hosted on SQL 2008 R2 SP2

Error Message

"Forefront Identity Manager Synchronization Service is having trouble contacting SQL server using the provided information. Please note that Forefront Identity Manager Synchronization Service requires Microsoft SQL Server 2008 SP1 or better. Verify the version, server and instance names as well as firewall settings and try again.”

Troubleshooting Steps 

Verify SQL Connectivity

  1. Verify an ODBC connection can be made from FIM to SQL.
  2. Verify Firewall rules.

Tips:

  • To verify that you can access the server via an ODBC connection

    • Go to: Administrator Tools --> Data Sources --> User DSN tab --> Add --> Fill in the server information --> Click "Test Connection"

  • use the telnet client to connect to the SQL server on the SQL port (default 1433)

    • telnet XX.XX.XX.XX 1433

  • Use the SQL management studio to connect to the SQL server

Verify SQL security

The user that runs the installation needs to have sa permissions in the instance of SQL hosting the FIM Sync service.

This is not necessarily the service account, the account running the sync service. It is the account performing the install.

SQL Server configuration

  1. Verify Full Text Search is installed on SQL.
  2. If using a non-default SQL instance, verify the SQL Browser is running.
  3. Verify SQL Agent is running.

FIM Server side

Configuration

Disable IESC for Administrators.

Setup

  1. Check if you have entered the right server name, server instance in the SQL configuration during the setup.

  2. Enable verbose logging on the installation:

    msiexec /i [File location] /lv*x [Log file location--file must already exist]

    See: http://msdn.microsoft.com/en-us/library/windows/desktop/aa367988(v=vs.85).aspx

    Search for return value 3 to find the error.

SQL Client

Verify the SQL Native Client is installed on FIM.

Background info

From FIM2010 Update 1 and forward FIM relies on the native SQL client, which is installed together with SQL server.

Having a local SQL server will make sure this always works.

FIM2010 RTM uses OLEDB, which comes with the operating system.

Downloads

You can install the SQL native client from the SQL server CD.

There is also a separate download from download center to install this component.

Microsoft SQL Server 2008 Service Pack 2 Feature Pack

http://www.microsoft.com/en-us/download/details.aspx?id=6375

Microsoft SQL Server 2008 Native Client

Microsoft® SQL Server® 2008 R2 Feature Pack

http://www.microsoft.com/en-us/download/details.aspx?id=16978

Microsoft® SQL Server® 2008 R2 Native Client

See also