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.

2 Replies to “SQL MA Failed to retrieve the schema”

  1. Thank you so much for the clarification.. saved my day..i was stuck on this cleartext thing for a week now, During a audit in my firm, the secrity team found one id was connecting using logontype 8 and after investigation i found it was our OOB SQL MAs though it was using windows integrated logon.. Don’t know why MS did this, but they should fix this one i would say.

    Thanks
    Maria

  2. Hi Carol! Thanks for the info; glad to see you’re still helping us all out. :^) I just ran into this same problem on some newly provisioned servers, but I didn’t have the option of SQL authentication. Here’s what did the trick for me:
    NLTEST /SC_VERIFY:hcplab.local
    NLTEST /SC_RESET:hcplab.local
    https://social.technet.microsoft.com/Forums/windowsserver/en-US/ae9da10a-b4d2-4eda-ae6d-ad61b7b6ab79/audit-failure-event-id-4625?forum=winserversecurity
    Cheers & happy holidays!
    Joe

Leave a Reply

Your email address will not be published. Required fields are marked *


*