When you try to install the Forefront Identity Manager (FIM) 2010 R2 SP1 Synchronization Service to use an off-box SQL Server instance you receive the following error "Forefront Identity Manager Synchronization Service Evaluation is having trouble contacting SQL server using the provided information. Please note that Forefront Identity Manager Synchronization requires Microsoft SQL Server 2008 SP1 or better. Verify the version, server and instance names as well as firewall."
In this particular instance FIM 2010 R2 SP1 is running on Windows Server 2008 R2 and the SQL Server is Windows Server 2012 R2 with SQL 2012 Standard. As with all off-box SQL instance I had created a ODBC (64-bit) from the FIM server, to the SQL. When I tested connectivity it all came back as working.
As part of the troubleshooting I tried the following things without success
· Connectivity and Name Resolution
o Ping and Nslookup
· SQL Services
o Ensured SQL Server Browser and SQL Server Agent were set to Automatic and Running
· Disabled the SQL Server Firewall
o To rule out any issues with port 1433, then tried Telnet all OK
Although all of these tests came back positive the issue was not resolved. The issue was with the SQL ODBC connection from FIM to SQL. I had created the ODBC using the standard SQL Server Client build into Windows Server 2008 R2.
The fix was to install the SQL Server 2012 Native Client, which can be downloaded from Microsoft http://microsoft.com/en-gb/download/confirmation.aspx?id=29065
Once I recreated the ODBC connection with the SQL Server 2012 Native Client the setup then allowed me to continue, the next step was to configure the FIM security groups.