Thursday 27 November 2014

Forefront Identity Manager 2010 R2 SP1 (FIM): Connection to SQL Server 2008 R2 for FIM Sync Service "Connection Failed: SQL State: '08001' SQL Server Error: 5 Could Not open a Connection to SQL Server [5]. Connection Failed: SQLState: 'HYT00' SQL Server Error: 0 [Microsoft][SQL Server Native Client 10.0]Login Timeout Expired"

Recently I have been building a FIM 2010 R2 SP1 Sync Service Server using an external SQL instance running on SQL Server 2008 R2. When I was creating the ODBC Data Source using the SQL Server Native Client 10.0 I receieved the following error "Connection Failed: SQL State: '08001' SQL Server Error: 5 Could Not open a Connection to SQL Server [5]. Connection Failed: SQLState: 'HYT00' SQL Server Error: 0 [Microsoft][SQL Server Native Client 10.0]Login Timeout Expired".
 
 
The solution here was to enable TCP/IP from the SQL Server Configuration Manager to do this launch the SQL Server Configuration Manager MMC expand SQL Server Network Configuration and click Protocols for MSSQLSERVER right click and select Properties on the TCP/IP protocol. From the Properties window click IP Addresses using the Active drop downs select Yes for IP1 and IP2. Click OK.
 
For the changes to apply you must restart the SQL instance to do this click on SQL Server Services from the left hand list, right click SQL Server (MSSQLSERVER) and select Restart.
 
To finish off open the SQL Management Studio and login using Windows Authentication.

 
Right click on the top (local) database icon and select Properties.

 
Click on the Security tab and change the Server Authentication to SQL Server and Windows Authentication Mode. Click OK.
 
Again the SQL Server must be restarted. In this instance I rebooted the entire server.
 
Once this has been done you can continue with the ODBC Data Source Connector.