{"id":3048,"date":"2017-03-24T04:44:29","date_gmt":"2017-03-24T04:44:29","guid":{"rendered":"https:\/\/www.wapshere.com\/missmiis\/?p=3048"},"modified":"2017-03-24T04:44:29","modified_gmt":"2017-03-24T04:44:29","slug":"sql-ma-failed-to-retrieve-the-schema","status":"publish","type":"post","link":"https:\/\/www.wapshere.com\/missmiis\/sql-ma-failed-to-retrieve-the-schema","title":{"rendered":"SQL MA Failed to retrieve the schema"},"content":{"rendered":"<p>This week I battled with an error from the OOB SQL MA for MIM 2016 (which I don&#8217;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:<\/p>\n<pre>Failed to retrieve the schema.\r\nException from HRESULT: 0x80231100<\/pre>\n<p>For those that just want to know the answer &#8211; 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.<\/p>\n<p>For those that want more details&#8230;<!--more--><\/p>\n<p>The following tests were run on the MIM Sync server:<\/p>\n<ul>\n<li>Able to telnet the SQL server on the appropriate port,<\/li>\n<li>Able to query the table using the target domain account via <a href=\"https:\/\/www.wapshere.com\/missmiis\/test-non-trusting-cross-domain-windows-authentication-to-sql-using-powershell\">PowerShell<\/a>,<\/li>\n<li>Wireshark showed the MA wasn&#8217;t even hitting\u00c2\u00a0the SQL server ip address at all.<\/li>\n<\/ul>\n<p>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):<\/p>\n<pre>Log Name:      Security\r\nSource:        Microsoft-Windows-Security-Auditing\r\nDate:          22\/03\/2017 2:28:59 PM\r\nEvent ID:      4625\r\nTask Category: Logon\r\nLevel:         Information\r\nKeywords:      Audit Failure\r\nUser:          N\/A\r\nComputer:      Sync_Server.thisdomain.com\r\nDescription:\r\nAn account failed to log on.\r\n\r\nSubject:\r\n                Security ID:             THISDOMAIN\\Sync_Service_Account\r\n                Account Name:            Sync_Service_Account\r\n                Account Domain:          THISDOMAIN\r\n                Logon ID:                0x279F6\r\n\r\nLogon Type:                              8\r\n\r\nAccount For Which Logon Failed:\r\n                Security ID:             NULL SID\r\n                Account Name:            Connection_Account\r\n                Account Domain:          OTHERDOMAIN\r\n\r\nFailure Information:\r\n                Failure Reason:          Unknown user name or bad password.\r\n                Status:                  0xC000006D\r\n                Sub Status:              0xC0000064\r\n\r\nProcess Information:\r\n                Caller Process ID:       0x66c\r\n                Caller Process Name:     C:\\Program Files\\Microsoft Forefront Identity Manager\\2010\\Synchronization Service\\Bin\\miiserver.exe\r\n\r\nNetwork Information:\r\n                Workstation Name:        Sync_Server\r\n                Source Network Address:  -\r\n                Source Port:             -\r\n\r\nDetailed Authentication Information:\r\n                Logon Process:           Advapi  \r\n                Authentication Package:  Negotiate\r\n                Transited Services:      -\r\n                Package Name (NTLM only):-\r\n                Key Length:              0\r\n<\/pre>\n<p>When connecting successfully from PowerShell the Success Audit event was somewhat different &#8211; the key factor being the Logon Type, specified in this event as type 8 &#8211; which turns out to mean the credentials are being sent in clear text. Something\u00c2\u00a0I did not actually realise the OOB SQL MA\u00c2\u00a0did.<\/p>\n<p>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.<\/p>\n<p>The workaround has been to use a SQL account in the MA settings, and that&#8217;s got us back in business for now. I think a preferable solution would be to change the MA type &#8211; the <a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/active-directory\/connect\/active-directory-aadconnectsync-connector-genericsql\">Generic SQL Connector<\/a> may be better, or I could use a PowerShell MA. But that&#8217;s a job for another day.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>This week I battled with an error from the OOB SQL MA for MIM 2016 (which I don&#8217;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&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"footnotes":"","jetpack_publicize_message":"","jetpack_is_tweetstorm":false,"jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":[]},"categories":[58,73,5,64],"tags":[],"class_list":["post-3048","post","type-post","status-publish","format-standard","hentry","category-fim-sync-service","category-mim-2016-sp1","category-sql","category-troubleshooting"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/pkp1o-Na","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/posts\/3048","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/comments?post=3048"}],"version-history":[{"count":6,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/posts\/3048\/revisions"}],"predecessor-version":[{"id":3054,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/posts\/3048\/revisions\/3054"}],"wp:attachment":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/media?parent=3048"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/categories?post=3048"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/tags?post=3048"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}