Hohlee! Using USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION') corrected all of the cardinality estimates, and the query which ran for 22 seconds without it, ran for only 5 with it!

dbo.IncidentTypeHierarchy_GetChildNodeIDs(1666) returns a table of 3 IncidentTypeNodeSID values, which is important for selectivity of the results. As for the INNER JOIN IRItemAnswer_Info iiai1, I could have written it as an WHERE EXISTS statement, it would have had the same effect.

Thank you for looking at this query. The modified query produces this plan. Though using a UNION instead of an AND (which is better query management) produces two Seeks instead of a Scan (on a small lookup table) they otherwise produce identical plans, and still doesn't avoid the tempdb spill in Nodes 19 and 20.

Let us continue this discussion in chat.

The goal of using SQL Server to access LDAP is for the purposes of SSO (Single Sign-On) and allowing AD to manage user credentials for our application/website. Once they validate to AD/LDAP, we check to see if a matching user exists in our system. If they do, then the are allowed in, no second password required. If they don't, then LDAP is used to fetch user details so a user can be created in our system that matches. That way, all "User Management" is done via AD/LDAP, not our system.

I've read in places the possibility of using an Extended Stored Procedure that could access IADsOpenDSObject:: OpenDSObject. Does anyone have any code on how that would looks like (I have no Extended Stored Procedure or CLR experience), and know whether that would be able to address the new LDAPS (port 636/3269) requirements?

I have found that sp_addlinkedsrvlogin does no authentication, it only sets up a login with the credentials you provide. I cannot use this for authentication.

Agreed, however, we decided to investigate this because we needed to perform the same functionality from within an old windows application (written in Delphi 5, and not accessible to new libraries) as well as a website. SQL queries are one of the most accessible intersecting points between the two environments.