Microsoft SQL Server 2012 Native Client Service Pack 1 (11.1.3000.0)
Forefront Identity Manager Synchronization Service
Forefront Identity Manager Service
Microsoft SQL Server 2016
NOTE
This specific issue can occur on any of the Identity Manager products and/or components because the issue relates to SQL Server connectivity. I just mention the products above because that was the information specific to the support case worked.
PROBLEM SCENARIO DESCRIPTION
The backend SQL Server 2016 server hosting the FIMSynchronizationService database was rebooted.
After this reboot, we were not able to launch the Synchronization Service Manager GUI (miisclient.exe).
After this reboot, we were not able to start the Forefront Identity Manager Synchronization Service through the Services MMC.
After this reboot, we were not able to start the Forefront Identity Manager Synchronization Service through the Services MMC.
NOTE
There is an exception regarding the ability to connect to SQL Server that is produced and dumped into the Application Event Log. I did not capture that for my notes, so reviewing the Application Event Log, would be a really good idea.
SOME TROUBLESHOOTING STEPS FOR TESTING CONNECTIVITY TO SQL
Review the Application Event Log - in this specific case we focused on items associated with the FIM Synchronization Service
You can confirm SQL Connectivity to be the issue by utilizing a UDL file to test connectivity.
Verify / Validate Connectivity to the backend SQL Server Database using a UDL File
A Universal Data Link (UDL) file is a way to test the connectivity to the backend SQL Server. Please find below the outline of steps to create a UDL file and test the connectivity to the backend SQL Server.
NOTE
It is important to note, that for troubleshooting connectivity to the backend SQL Server, we need to utilize the correct account that is associated with issue for which we are troubleshooting. The reason is that FIM/MIM utilizes Windows NT Authentication and not SQL Server Authentication.
Account Examples
Installation Account: Account that is executing the installation of the product and/or the hotfix update
Synchronization Service Account: Account that talks to SQL Server via the Forefront Identity Manager Synchronization Service
FIM MA Account: Account that is utilized in the FIM Service Management Agent
Service Account: Account that talks to SQL Server via the Forefront Identity Manager Service
One can test this via:
Logging into the Synchronization Service machine as the Synchronization Service account and launching the UDL file
Launching a command-prompt by running as a different user.
Right mouse click on the Command Prompt icon and select Run as a different user
Navigate to the Path where the UDL file is located. (For ease of navigation, the recommendation is normally to use something like C:\Temp)
Create a new text file on the Desktop and call it TestSQL.TXT (*NOTE: The filename is not important. I utilize this for the purpose of this documentation.)
Rename the file extension from TXT to UDL.
Double click on the TestSQL.UDL file to launch the GUI
On the Provider Tab, ensure that Microsoft OLE DB Provider for SQL Server is selected
Click the Next Button
On the Connection Tab,
Enter the server name for the SQL Server that is hosting the backend database(s)
Use Windows NT Integrated Security
Click the Test Connection Button
If this works, SQL Connectivity using OLEDB is valid
Validate that TLS 1.0 is enabled and/or that the cumulative update is installed on the machine hosting the Forefront Identity Manager Synchronization Service and/or Forefront Identity Manager Service.
Steps to check TLS
Open the Windows Registry (Start > Run and type: regedit.exe)
Navigate to: HKLM SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols
Under Protocols, you may or may not have keys for TLS 1.0, TLS 1.1 and/or TLS 1.2
Under each TLS item are 2 keys (Client and Server)
Once selected on Client or Server, DWORD values may exist
Enabled
0
Disabled
1
Enabled
DisabledByDefault
0
Disabled
1
Enabled
NOTE
Many companies are moving to more secure environments! In doing so, TLS 1.0 is being disabled. Companies that disable TLS 1.0 may run into SQL Server connectivity issues for many reasons.
Disabling TLS 1.0 occurred this year and previous versions of SQL Server were not configured to work with TLS 1.2, so disabling TLS 1.0 breaks SQL Server connectivity.
If SQL Server connectivity is broken then the Forefront Identity Manager Synchronization Service and the Forefront Identity Manager Service will not start.
If SQL Server connectivity is broken then the Synchronization Service Manager GUI will not launch
CAUSE
TLS v1.0 was disabled
By default, the Forefront Identity Manager Synchronization Service and Forefront Identity Manager Service utilize TLS v1.0, so if TLS v1.0 is disabled, then it will prevent the handshake from occurring via SQL Server.
NOTE
One can validate TLS v1.0 by checking the following registry key:HKLM SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols
Under this registry key, are the keys for TLS 1.0, TLS 1.1 and TLS 1.2.
Under each of the TLS branches are Client and Server
Within each of those, you will find DWORD values
Enabled ( 0 = False // 1 = True )
There is more information on TLS within this Microsoft Documentation.
To resolve the issue, we needed to install SQL Server 2012 Native Client SP3 and the Cumulative Update for SQL Server 2012 SP3.
RESOLUTION STEPS
Identify if SQL Server 2012 Native Client SP3 is installed or not installed on the machine(s) hosting either the Forefront Identity Manager Synchronization Service and/or the Forefront Identity Manager Service.
NOTE
To confirm that SQL Server 2012 Native Client SP3 is installed:
In Control Panel, open Programs and Features to list the applications installed on the machine.
Locate the SQL Server 2012 Native Client
Review the version of the Native Client, which should be the last column
If SQL Server 2012 Native Client SP3 is not installed, then you need to download and install this first, because the Cumulative Update is for SQL Server 2012 SP3.