Share via

PRODUCTS INVOLVED

  • Microsoft Identity Manager 2016 Service Pack 1
    • 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

  1. Review the Application Event Log - in this specific case we focused on items associated with the FIM Synchronization Service
  2. 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:
  1. Logging into the Synchronization Service machine as the Synchronization Service account and launching the UDL file
  2. Launching a command-prompt by running as a different user.
    1. Right mouse click on the Command Prompt icon and select Run as a different user
    2. Navigate to the Path where the UDL file is located.  (For ease of navigation, the recommendation is normally to use something like C:\Temp)
  1. 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.)
  2. Rename the file extension from TXT to UDL.
  3. Double click on the TestSQL.UDL file to launch the GUI
  4. On the Provider Tab, ensure that Microsoft OLE DB Provider for SQL Server is selected
  5. Click the Next Button
  6. On the Connection Tab,
  7. Enter the server name for the SQL Server that is hosting the backend database(s)
  8. Use Windows NT Integrated Security
  9. Click the Test Connection Button
  10. If this works, SQL Connectivity using OLEDB is valid
 
  1. 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
  1. Open the Windows Registry (Start > Run and type: regedit.exe)
  2. Navigate to: HKLM SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols
  3. Under Protocols, you may or may not have keys for TLS 1.0, TLS 1.1 and/or TLS 1.2
  4. Under each TLS item are 2 keys (Client and Server)
  5. 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.
  1. 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.
    1. If SQL Server connectivity is broken then the Forefront Identity Manager Synchronization Service and the Forefront Identity Manager Service will not start.
    2. 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 
  1. Under this registry key, are the keys for TLS 1.0, TLS 1.1 and TLS 1.2.
  2. Under each of the TLS branches are Client and Server
  3. Within each of those, you will find DWORD values
    1. Enabled ( 0 = False // 1 = True )
 There is more information on TLS within this Microsoft Documentation.  

 

RESOLUTION

  • 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:
  1. In Control Panel, open Programs and Features to list the applications installed on the machine.
  2. Locate the SQL Server 2012 Native Client
  3. Review the version of the Native Client, which should be the last column
  4. Review this blog with build numbers: https://sqlserverbuilds.blogspot.com/
For SQL Server 2012 a high level breakout is:
  1. 11.0 = SQL Server 2012 RTM (No Service Pack)
  2. 11.1 = SQL Server 2012 SP1
  3. 11.2 = SQL Server 2012 SP2
  4. 11.3 = SQL Server 2012 SP3
In the case that it is a different version of SQL Server, the following Microsoft Knowledge Base Article provides information around this topic along with a table identifying each of the Cumulative updates to download based on the version.  https://support.microsoft.com/en-us/help/3135244/tls-1-2-support-for-microsoft-sql-server

ADDITIONAL INFORMATION / RESOURCES

Please find our Support Team Blog here: https://blogs.technet.microsoft.com/iamsupport.

  • Some keywords to assist in locating the blog quickly through searches are: iamsupport

MIM 2016 SP1 (4.4.1749.0): https://blogs.technet.microsoft.com/iamsupport/2017/11/30/support-release-mim2016-microsoft-identity-manager-2016-sp1-hotfix-4-4-1749-0-released/

TLS INFORMATION:

Table / Blog on the SQL Server cumulative updates for the TLS issue: .  https://support.microsoft.com/en-us/help/3135244/tls-1-2-support-for-microsoft-sql-server