Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Querying Active Direct using T-SQL

Posted on 2014-01-14
4
Medium Priority
?
496 Views
Last Modified: 2014-01-15
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*''
Select * 
From OpenQuery(ADSI,'Select distinguishedName
	, title
	, displayName
	, sAMAccountName
	, mail
	, mobile
	, department
	, manager
	, adspath
From  ''LDAP://DC=mydomain,DC=COM''
Where distinguishedName = ''*XYZ*''')

Open in new window

0
Comment
Question by:waverazor
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 39779327
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.
0
 
LVL 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 1200 total points
ID: 39779408
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.

For example:
Select * 
From OpenQuery(ADSI,'Select distinguishedName
	, title
	, displayName
	, sAMAccountName
	, mail
	, mobile
	, department
	, manager
	, adspath
From  ''LDAP://DC=mydomain,DC=COM''
Where objectCategory = ''Person'' And objectClass = ''user''
')
Where distinguishedName Like '%XYZ%'
;

Open in new window

0
 

Author Comment

by:waverazor
ID: 39780434
I have a 1,000 row limit on queries.  Will the OpenQuery stop at the 1,000 row limit before before the second "where" clause filters?
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 1200 total points
ID: 39782364
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.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After seeing many questions for JRNL_WRAP_ERROR for replication failure, I thought it would be useful to write this article.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This tutorial will walk an individual through the process of configuring their Windows Server 2012 domain controller to synchronize its time with a trusted, external resource. Use Google, Bing, or other preferred search engine to locate trusted NTP …
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question