Querying Active Direct using T-SQL

I'm querying active directory using the syntax below. The query returns no rows even though distinguishedName contains the characters "XYZ". I'm attempting to do a "Like" comparison. Records are returned when I use this "where" clause: Where sn = ''*lber*''

Hi. The issue is it likely is not going down the subtree. In other words, is the object you are searching on directly in the LDAP path you have in the FROM? If not, you will need to tell it to search child containers. I post an example shortly.

EDIT: hmm, it is not the subtree issue. It is doing that properly. I search on sAMAccountName and works fine. I know I have used distinguishedName before, so testing before posting again.

I do not recall having any issues with distinguishedName, but it appears it is not allowing wildcard searches just on that field. If I type in the full distinguishedName, it finds the object just fine.

A workaround is to bring back everything (you can limit by object type or something else), then filter on the SQL side.

Most likely. Therefore, another workaround is to use sAMAccountName, or other field, to get the match you need. For example, if XYZ is the CN, the wildcard filter works on sAMAccountName. I tried LDAP syntax, and wildcard search still did not work on distinguishedName. Therefore, it appears it is specific to that column because it contains a path as adspath also fails. There possibly is something on the LDAP settings that can help, but it has been too long.

You can click Request Attention and see if the Moderators can attract more Experts, especially in the Active Directory/LDAP topic areas, to see if they can offer more assistance.

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.