#MIM2016 Troubleshooting: SQL Connection issues

On TNWiki you’ll find my latest article on MIM 2016 troubleshooting.

MIM 2016 Troubleshooting: SQL Connection issues

This week I got (dragged into/) involved in a MIM 2016 performance troubleshooting, on a test / dev server, facing a large bunch of errors.

The first detection happened on the sync server, but apparently rather it’s twin brother was causing the issues.

It became pretty quickly obvious that MIM was not able to connect to (one of) it’s databases on the SQL server, so the sync engine was unable to pull information from the MIM service.

Also bizar, we could still work on the MIM sync GUI, but almost any MA action in the GUI failed…

Furthermore the Portal did not respond and finally the “MIM Service” service, didn’t behave as expected, not willing to start.

The event viewer contained the obvious amount of errors…

Finally,  the SQL DBA to the rescue.

I’ve added a lot of significant technical event info into the article, to make it easy to search for you, for later reference.

Read the tech details in: MIM 2016 Troubleshooting: SQL Connection issues

Updated: 2020-12-29

Note-To-Self: ICYMI, #MIM2016 Support for SQL Always On Availability groups

Based on a recent customer support experience with MIM (migrating from MIM 2016 RTM to SP1 latest hotfix), I ran into a few issues…

They have been documented here:

• MIM 2016 Service and Portal Troubleshooting: Hotfix installation failure (Unauthorized operation)
• MIM 2016 Service and Portal Troubleshooting: Hotfix installation failure (deployment or retraction is already under way)

And also

• MIM 2016 Troubleshooting: no-start-bad-ma-configuration 

While investigating the MIM Performance, we bumped into some SQL configuration issues, seriously impacting the MIM performance.
Finally ending up with staging the latest hotfix on MIM… (which is in general always a good idea and best practice).

But, talking the SQL performance, in that troubleshooting exercise another question popped up, again: Always on Availability groups.

And while this has been an issues for long time, the good news is : as of MIM 2016 SP1 (4.4.1459.0 or Later), Always On Availability groups are now supported.

You can find the announcement here: https://blogs.technet.microsoft.com/iamsupport/2017/03/22/microsoft-identity-manager-2016-sp14-4-1459-0-or-later-support-for-sql-2016-always-on-availability-groups/

Strangely enough it’s not mentioned in the KB article for the hotfix: SP1 March 2017 Hotfix (4.4.1459.0),

Still, the page on SQL Server availability solutions for Microsoft Identity Manager services databases, is not mentioning AoA, see here: https://support.microsoft.com/en-us/help/3200896/sql-server-availability-solutions-for-microsoft-identity-manager-servi

So, you need to keep that one in your MIM knowledge backpack.

Updated: 2020-12-29

#FIM2010 upgrade/update failure and roll back

Recently I have been working with several customer that experienced a similar situation:

• update FIM with a hotfix fails
• upgrade FIM 2010 to FIM 2010 R2 fails
• during installation of FIM he FIM services won’t start

All of them result in a roll-back of the installation.

Let me spoil the root cause right away (and then explain): using an SQL port number in the installation wizard.

The installation wizard is not able to connect to the database with a port number.

Solution:

use an SQL alias

Background

The FIM Sync Service and/or the FIM servers check the registry for the database server and instance and then connect to SQL and start the service.

The use of a port number seems to break the wizard.
Normally the FIM Services and FIM Sync Services CAN use an SQL port…

Easy fix: set an alias in the SQL Server client network utility

c:\windows\system32\cliconfig.exe

Then change the registry to use the FIM SQL ALIAS (as server), you don’t need the instance and port anymore (as the alias will take care of it).

For the FIM Sync:

Check the server and instance configured for the FIM Sync database

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\FIMSynchronizationService\Parameters\Server (use SQL Alias)

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\FIMSynchronizationService\Parameters\Instance (empty)

for FIM Service

Check the server and instance configured for the FIM Service database

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\FIMService\DatabaseServer

Reference

I’ve updated the Wiki article with more detailed info at http://social.technet.microsoft.com/wiki/contents/articles/14551.fim-2010-r2-troubleshooting-syncservice-installation-or-upgrade-failure-and-roll-back.aspx

See also:

• http://social.technet.microsoft.com/wiki/contents/articles/14734.how-to-install-fim-using-a-sql-server-alias.aspx
• http://konab.com/working-with-sql-aliases/
• https://gavinmckay.wordpress.com/2011/10/03/howto-move-forefront-identity-manager-2010-databases-to-a-different-sql-server/

Last updated: 2020-12-30

Note-to-self: #FIM2010/#MIM2016 Generic SQL Connector technical reference

Please note that Andreas Kjellman published/updated the information on the Generic SQL connector for FIM/MIM

Source:

• http://aka.ms/fimgenericsqlma, or
• http://aka.ms/mimgenericsqlma

As described on the tech page:

“This article describes the Generic SQL Connector. The article applies to the following products:

• Microsoft Identity Manager 2016 (MIM2016)
• Forefront Identity Manager 2010 R2 (FIM2010R2)
  • Must use hotfix 4.1.3461.0 or later KB2870703.

For MIM2016 and FIM2010R2 the Connector is available as a download from the Microsoft Download Center.

/../

The Connector is supported with all 64-bit ODBC drivers. It has been tested with the following:

• Microsoft SQL Server & SQL Azure
• IBM DB2 10.x
• IBM DB2 9.x
• Oracle 10 & 11g
• MySQL 5.x

”

 

You might have some trouble to get to the download link, if so : keep an eye on it and retry later. The download will be activated any time soon…

Microsoft announced additional connectors for FIM 2010 R2 (#FIM2010)

Source: Forefront Identity Manager 2010 group at https://www.facebook.com/groups/155109068156/

The FIM team has announced the availability of some additional Connectors for FIM2010R2.

General Availability of PowerShell Connector

The PowerShell Connector can be used to communicate with a system through PowerShell scripts. This allows an easy and flexible way to communicate with other systems but also to pre-/post-process data and files before handed over to the FIM Synchronization Service. We believe the community will help providing scripts for this Connector for various systems and will open a place where scripts can be published for reuse.

TechNet docs:   http://go.microsoft.com/fwlink/?LinkID=393057
Download:          http://go.microsoft.com/fwlink/?LinkID=393056

Release Candidate of Generic SQL Connector

The Generic SQL Connector will allow you to connect to any database where you have an ODBC driver available. It enables new features compared to the built-in MA such as support for Stored Procedures, running SQL scripts, built-in delta import support, import multiple object types, connect to multiple tables, and much more. This Connector is built on ECMA2.3 which allows schema discoverability to be customized in the Sync Engine UI. A pre-release of the next Sync Engine hotfix is included with the Connector download and is required for the Connector to work.

Download:          https://connect.microsoft.com/site433/Downloads/DownloadDetails.aspx?DownloadID=52652

Release Candidate of SAP Users and Roles/Groups

The updated SAP templates for Users and Roles/Groups allows you to manage Users, Roles, and Groups in SAP. This also include password sync for Users to SAP. The Connector will make sure roles are represented as groups to make it possible to manage these with bhold. This template will require the previously published WebService Connector: http://go.microsoft.com/fwlink/?LinkID=235883.

Download:          https://connect.microsoft.com/site433/Downloads/DownloadDetails.aspx?DownloadID=52651

Participation on Connect

If you have participated in any other Connector preview program you will have access to the Release Candidate downloads. If you have not participated before then to get access to the preview programs on Connect either join the program “Identity and Access Management”, “FIM Synchronization Service Connectors Pre-release” on http://connect.microsoft.com/directory or follow this link http://connect.microsoft.com/site433/SelfNomination.aspx?ProgramID=6709&pageType=1

Generic LDAP Connector (build 4.3.1082.0)

We have also published an update to the Generic LDAP Connector adding support for some additional LDAP directories, see http://support.microsoft.com/kb/2936070/. If you have additional LDAP directories you think we should support, please feel free to contact provide feedback on the Connect Site or via the FIM 2010 forum on technet.