This week I battled with an error from the OOB SQL MA for MIM 2016 (which I don’t think has changed at all from FIM 2010, and probably not earlier versions as well). The MA was working with a SQL database table on a server in another, non-trusting AD forest, and using Windows authentication. The table then got moved to yet another non-trusting forest and, when I tried to update the MA settings, I got:
Failed to retrieve the schema. Exception from HRESULT: 0x80231100
For those that just want to know the answer – it had something to do with clear text AD credentials being blocked, and the workaround was to create a SQL account and connect with that instead.
For those that want more details…
The following tests were run on the MIM Sync server:
- Able to telnet the SQL server on the appropriate port,
- Able to query the table using the target domain account via PowerShell,
- Wireshark showed the MA wasn’t even hitting the SQL server ip address at all.
Here I have to credit the sharp eyes of one of my customer techs as I spent a looong time trawling logs and running traces to try and figure this out. He noticed we had this authentication failure in the Security Event Log on the MIM Sync server (domain and account names changed):
Log Name: Security Source: Microsoft-Windows-Security-Auditing Date: 22/03/2017 2:28:59 PM Event ID: 4625 Task Category: Logon Level: Information Keywords: Audit Failure User: N/A Computer: Sync_Server.thisdomain.com Description: An account failed to log on. Subject: Security ID: THISDOMAIN\Sync_Service_Account Account Name: Sync_Service_Account Account Domain: THISDOMAIN Logon ID: 0x279F6 Logon Type: 8 Account For Which Logon Failed: Security ID: NULL SID Account Name: Connection_Account Account Domain: OTHERDOMAIN Failure Information: Failure Reason: Unknown user name or bad password. Status: 0xC000006D Sub Status: 0xC0000064 Process Information: Caller Process ID: 0x66c Caller Process Name: C:\Program Files\Microsoft Forefront Identity Manager\2010\Synchronization Service\Bin\miiserver.exe Network Information: Workstation Name: Sync_Server Source Network Address: - Source Port: - Detailed Authentication Information: Logon Process: Advapi Authentication Package: Negotiate Transited Services: - Package Name (NTLM only):- Key Length: 0
When connecting successfully from PowerShell the Success Audit event was somewhat different – the key factor being the Logon Type, specified in this event as type 8 – which turns out to mean the credentials are being sent in clear text. Something I did not actually realise the OOB SQL MA did.
The current working theory is something in the other AD environment is blocking the clear text authentication, though exactly what is not clear at this point. When running my PowerShell-based test it must have used a more secure logon type.
The workaround has been to use a SQL account in the MA settings, and that’s got us back in business for now. I think a preferable solution would be to change the MA type – the Generic SQL Connector may be better, or I could use a PowerShell MA. But that’s a job for another day.