SQL MA Failed to retrieve the schema

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.