2004.11.09 04:33 PM

ADSI Query Dialects - LDAP Wins

You can use two different dialects for Active Directory Service Interfaces (ADSI) search strings: SQL and LDAP.  Being familiar with SQL, and somewhat put off by the apparent complexity of LDAP, I started with that.  However, I later discovered that the LDAP dialect offers a distinct advantage over SQL: It provides matching rules in the form of object identifiers (OIDs) with which you can construct in-line bitwise filters.

In so much as my primary search included an evaluation of the AD schema attribute userAccountControl to be sure it did not contain the ADS_USER_FLAG_ENUM value ADS_UF_ACCOUNTDISABLE (2) but did contain ADS_UF_NORMAL_ACCOUNT (512), I was able to simplify my logic by moving this check out of the subsequent results loop and into the query itself.  By performing this filter on the server instead of the client, I reduced the number of rows (and columns) returned, which is important when the server's AD resultset size is really restricted, and (most importantly) improved overall performance by about 20%.

Here's the SQL form of my query:

SELECT
    CN,
    L,
    ST,
    userAccountControl
  FROM
    'LDAP://server/DC=company,DC=com'
  WHERE
    objectCategory='person' AND
    objectClass='user' AND
    CN='partial value being sought*' AND
    CN='*,*' AND
    userPrincipalName='*'

Using this query, the results have to be enumerated and the userAccountControl value explicitly checked as described above.

Note that the CN='*,*' and userPrincipalName='*' criteria are additional checks needed to filter out groups, printers, conference rooms, etc., which are unfortunately indistinguishable from user/persons in my client's AD.

Now, here's the LDAP form of my query:

<LDAP://server/DC=company,DC=com>;
  (&
    (objectCategory=person)
    (objectClass=user)
    (CN=partial value being sought*)
    (CN=*,*)
    (userprincipalname=*)
    (!(userAccountControl:1.2.840.113556.1.4.803:=2))
    (userAccountControl:1.2.840.113556.1.4.803:=512)
  );
  CN, ST, L;
  subTree

No need to return the userAccountControl value, and no need to enumerate the results.  Much better.


Comments

What about, if i wont to search for anybody with "be" in their name or department.

where (name like '*be*') or (department like '*be*')

Torben | 2005.10.25 03:26 PM

Hi Torben,

I believe, as I represented in the sample queries above, that you can use wildcards in the criteria values without utilizing special keywords such as "LIKE". For instance, a SQL-based query can search for CNs (containers, users, contacts, groups, etc.) having "be" in their names like this:

SELECT CN
FROM 'LDAP://server/DC=company,DC=com'
WHERE CN='*be*'

If you find this isn't true, or there's something else you're trying to query besides the CN, please let me know.

ewbi.develops | 2005.11.08 04:00 PM

Your comparison to an SQL query for the LDAP query is exactly what I was looking for!! Thank you!!

BUT...

I don't know enough about LDAP to get complex enough for what I want and am hoping anyone can shed some light.

I'm doing a keyword search for people and want to return matching names (similar to using LIKE) but want to return only those enabled.

In SQL form, this is what I'm wanting:
Say I'm looking for people named "Bob"
---
SELECT
cn,givenname,sn
FROM
server
WHERE
((givenname like '%bob%) OR
(sn like '%bob%')) AND
(useraccountcontrol != disabled)
---

Using your example, I created this filter:
(&(givenname=bob*)(sn=bob*)(!(userAccountControl:1.2.840.113556.1.4.803:=2)))

But it treats the entire statement as an AND query

I need to mix AND and OR inside the LDAP query and not quite sure how.

Thanks for any help.
~Brian
Columbus, OH

Brian | 2005.12.21 12:54 PM

Hi Brian,

Glad this helped. Regarding your question, here's a good summary of the LDAP search filter syntax:

http://msdn.microsoft.com/library/en-us/adsi/adsi/search_filter_syntax.asp

To accomplish your ((this OR that) AND another) query, you should be able to do this (though I can't test it right now):

(&(|(givenname=bob*)(sn=bob*))(userAccountControl:1.2.840.113556.1.4.803:=2))

Good luck!

ewbi.develops | 2005.12.21 02:48 PM

Ahh...that's just what I needed but couldn't find. I found some other things at MSDN that helped but that filter syntax is a big help.

I tried it out and it worked great, but now I'm finding I need to limit where the search is done, that is, excluding OUs.

My first thought was to get matching name results, then use PHP to filter out the entries from the OUs I don't want - but I figured a good LDAP query could do that too.

I first tried something like this:
(!(dn=OU=Banned Users,DC=company,DC=org))

...combined with my name searching

The syntax is correct, but it didn't work.

I did some looking and found the queries do NOT like whitespace.

I went back to that MSDN filter page and also looked up how to escape the whitespace in the query. Every where I went said use \20 to escape the space, so I tried that in the query:

(!(dn=OU=Banned\20Users,DC=company,DC=org))

But that failed too.

To check my own work I tried it with OU=Banned* and it worked fine. So I know the whitespace is the problem.

I even tried some other variations of escaping spaces that I found, like %20 but that didn't work either.

So right now I'm at a loss to figure out how to make valid queries that include whitespace.

If you know or have some links in your bag that could help I would appreciate it a ton.

I can't thank you enough for helping out.
Just lighting the right path helps a lot!

~Brian

Brian | 2005.12.28 08:46 AM

Hi Brian,

Not sure I understand what you want to do by using the syntax "dn=OU=whatever". Do you not just want to use the syntax "OU=whatever" or "dn=whatever"?

Regarding the space, though, I was able to successfully query values from a client's AD using \20 to represent (in hex) ASCII character 32. I ran a couple of tests, one looked like this:

(&(objectCategory=person)(objectClass=user)(CN=Anderson,\20C*))

Gave me the two "Anderson, C*" entries I was expecting. While this:

(&(objectCategory=person)(objectClass=user)(CN=Anderson,\20*))

Gave me all 25 of the "Anderson, " entries. And this:

(&(objectCategory=person)(objectClass=user)(CN=A*))

Gave me all 632 entries beginning with "A".

Let me know if you figure out what's going on at your end. Good luck!

ewbi.develops | 2005.12.28 08:53 PM

Hello,
Is there an LDAP filter to get distinct values? i know sql does.

Jake | 2006.01.05 04:33 PM

Jake,

I don't see that there is one. I looked over RFC2254 "The String Representation of LDAP Search Filters" and didn't see anything obvious:

http://rfc.net/rfc2254.html

A quick Google search shows others sticking with SQL where a distinct resultset is needed. Good luck!

ewbi.develops | 2006.01.05 04:50 PM

I found good ways to get around searching specific OUs - with a blacklist and using some exclusion attributes like (!(objectclass=computer) and so on, but our directory is not very well managed and thus some people don't have first and last names.

And of course when I do a listing of an OU they appear as empty lines instead of a text name. I want to exclude entries that don't have values in the first and last name from the listing.

Since I can't enforce that each profile be complete (although I wish I could) my thought was to exclude the entries with empty attributes in the LDAP query.

So I tried this:
(!(sn=\00))(!(givenname=\00))

But it didn't work.
Looking at the links you mentioned earlier, it says NULL is escaped with \00 - so to me (!(sn=\00)) means:
where 'sn' != ""

Am I wrong in this logic?

I can run a post-query filter with PHP to remove the unwanted, but if I can do this in the LDAP query all the better.

Any thoughts...
Thanks!
~Brian

Brian | 2006.01.09 12:22 PM

Hi Brian,

I don't have time to test this today, but I am not surprised to hear that a null check won't work. I believe that with AD when an attribute doesn't have a value the attribute doesn't exist, so the way to determine whether an attribute exists is to check that it has any value, like this:

(&(sn=*)(givenname=*))

Finding the inverse then would be this:

(!(&(sn=*)(givenname=*)))

Again, I could be wrong, but that's my first thought. Wish I had time to check it out to be sure. Please come back and let us know what you find.

ewbi.develops | 2006.01.09 02:37 PM

That did the trick. Thanks!

I did some of my own tests to confirm that the attribute doesn't exist in AD unless it has a value. Not what I expected, but it doesn't surprise me given the source.

The query I use gets all enabled users that are not also flagged as "computer" and that have complete name information.

My final query looks this:
(&(objectclass=user)(!(objectclass=computer))(!(userAccountControl:1.2.840.113556.1.4.803:=2))(sn=*)(givenname=*))

Works like a charm...using PHP to make the call to Active Directory.

Thanks again for the input. A little backwards thinking was all it took. And when I'm "in the zone" it's difficult to break free!

Cheers
~Brian

Brian | 2006.01.10 01:28 PM

Brian, that's great! Thanks for coming back and letting us know!

ewbi.develops | 2006.01.10 01:44 PM

Hi,

I've been trying to get SELECT DISTINCT to work but with no luck. I keep getting the following error:

'Provider error '80040e14'

One or more errors occurred during processing of command. '

This is my SQL:

adQuery = "SELECT DISTINCT GivenName, employeeid FROM 'LDAP://dc=test,dc=test,dc=test,dc=test,dc=testWHERE employeeid = '*'"

Any ideas as to why this won't work? I think I read somewhere that Active Directory doesn't support DISTINCT.

Thanks very much,

E

Ell | 2006.02.07 12:43 AM

Ell, looks like you're right. Microsoft's ADSI search SQL dialect does not support DISTINCT. It seems like I saw some folks around the net suggesting this approach, but looks like they (or my memory of it) were wrong. That's too bad. Good luck.

ewbi.develops | 2006.02.07 10:40 AM

Hi,

is there a way to insert variables into the LDAP query, i want to use objWshNetwork.Username to populate the sAMACCOUNTNAME in a search im performing to return the users CN Name.

David | 2006.02.16 03:24 AM

Hi David,

Do you mean besides just appending the value (in this case the UserName) into the LDAP query string before submitting it? Are you looking to use ADO/ADO.NET query parameters?

ewbi.develops | 2006.02.21 11:09 PM

I use SQL to query LDAP. I also look for userAccountControl to check or the account is disabled.
Most of the times userAccountControl returns a null value.... How can i solve this???

Sjoerd | 2006.04.11 05:22 AM

Sjoerd - Sorry for the delay, I'm overloaded at the moment. I will try to look closer at your question next week.

ewbi.develops | 2006.04.14 05:11 PM

Hi Sjoerd,

Sorry for the long delay. After looking closer at this, I can't say I've ever seen a null userAccountControl property in any of my client's ADs, at least not for user accounts. However, it seems there are some folks who, like you, have seen it. The following post describes how they deal with it, but doesn't explain why it happens:

http://geekswithblogs.net/mnf/archive/2005/08/10/49754.aspx

I'm afraid that's all I've got on the subject. Good luck.

ewbi.develops | 2006.05.04 11:39 AM

can any one send me some references on "basics of LDAP querying"

suman | 2006.05.12 12:09 AM

Suman, try these:

http://www.microsoft.com/technet/prodtechnol/exchange/2003/insider/ldapquery.mspx
http://msdn.microsoft.com/library/en-us/adsi/adsi/ldap_dialect.asp

ewbi.develops | 2006.05.12 08:23 AM

(&(boolX)(boolY))
it is amaizing how LISP-like this is

Stephan | 2006.06.08 02:20 PM

can we update ldap from sql server, if so how?

Thanks.

Dave | 2006.08.24 07:52 PM

Hi Dave,

Not sure I follow what you're looking to do. LDAP is a query dialect used to process the contents of some data store, like Windows Active Directory (in this case, by way of ADSI). So, when you say "update ldap from sql server", do you mean you have some info in a SQL Server database that you want to write to an ADSI accessible data store (AD, for instance) using the LDAP dialect? Or, do you mean you want to use the LDAP dialect to address information in a SQL Server database?

ewbi.develops | 2006.08.24 08:58 PM

We have a database in SQL Server. When user changes their information lets say password, we need to update the Active Directory. I have a trigger on the table/field and it can now use ADSI through linked server to update the Active directory. Example:

--create linked server
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'

T--hen Provide login info
sp_addlinkedsrvlogin ADSI, FALSE, 'yourusername', 'CN=yourusername,CN=Users,DC=yourdomain,DC=com', 'yourpassword'

--Then run your command
SELECT CN, ADsPath FROM OpenQuery
( ADSI, 'SELECT CN, ADsPath FROM ''LDAP://DC=eprod,DC=COM'' WHERE objectCategory=''person'' AND objectClass=''user'' AND CN = ''a*'' ORDER BY CN')

The problem is
1. I cannot access individual attribute
2. I cannot update!

I explored another option of using Activex in Data Transformation Service.

'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()

dim objUser
msgbox "it started"
Set objUser = GetObject("LDAP://server/cn=idwils,ou=People,o=something")
objUser.GetInfo
msgbox objUser.get("userPassword")
objUser.put "userPassword","this"
objUser.SetInfo
msgbox objUser.get("userPassword")
msgbox "its done"


Main = DTSTaskExecResult_Success
End Function

Now this errors out at setinfo()

I have tried OLE stuff, like sp_OAcreate/method etc but not successfull.

I dont know if it can be done using sql server. I mean I can do it using Java, Coldfusion, even Excel but not SQL Server.

Thanks for your help,

Dave

Dave | 2006.08.25 02:12 PM

Ok I figured it out. Here it goes if some needs it:

Update LDAP (Active Directory) from SQL Server
I am using DTS Activex task
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()

' From the book "Active Directory, Third Edition"
' ISBN: 0-596-10173-2

Const ADS_SECURE_AUTHENTICATION = 1
Const ADS_USE_ENCRYPTION = 2

Dim strPath 'path to authenticate to in the directory service
Dim strUsername 'DN of the username
Dim strPassword 'plain text password
Dim objNamespaceLDAP 'ADSI namespace object
Dim objMyObject 'root object of the directory

strPath = "LDAP://Server/ specifics here like cn=something,ou=People,o=com"
strUsername = "cn=administrator,o=something"
strPassword = "password"

Set objNamespaceLDAP = GetObject("LDAP:")
Set objMyObject = objNamespaceLDAP.OpenDSObject(strPath, strUsername, strPassword, _
ADS_USE_ENCRYPTION AND ADS_SECURE_AUTHENTICATION)

msgbox objMyobject.Name
objMyobject.Name = "somename"
objMyobject.setinfo

msgbox "setinfo successful"

msgbox objMyobject.Name


Main = DTSTaskExecResult_Success
End Function

OR you can use vbs
save the above code (except the first line and last two lines of code) in a file name it, lets say abc.vbs then simply on the dos prompt type abc and it will do the magic.

Dave | 2006.08.28 12:59 PM

Hi Dave,

Thanks for coming back and sharing that. My experience has mostly been as a consumer of AD info rather than an updater of that info. I assumed, though, that any updates would be done using the properties of ADSI objects returned by LDAP- and/or SQL-based queries, as your example illustrates. Thanks again.

ewbi.develops | 2006.08.28 03:49 PM

Hi Friends, I dont know much abt LDAP but my problem is realy fustrating me which is as follows :

I have 3 servers 1. WebServer 2. Database Derver 3. Exchage Server, on Webserver having COM+ objs which calls CDO.Person and one of its parameter is .DataSource.SaveTo LDAPstring
so the Q is LDAPString = "LDAP://"ExchangeServer/CN=Users,DC=something,DC=com" was working fine but recently Exchange was crashed and we have to recover it its fine now and i can see all the mails on web Outlook Access but Application doesnt able to connect with Exchange with the halp of LDAPString so how can i check this string is correct of not, how can i query all the Member present on my LDAP path??? and How and Where do i have to run that..? plz help me...its taken more then a week now of mine..NEED HELP

TheCoolestOne | 2007.03.01 07:49 AM

TheCoolestOne,

I'm not sure whether you're describing a connectivity issue ("Application doesnt able to connect with Exchange with the halp of LDAPString") or an LDAP query issue ("how can i query all the Member present on my LDAP path") or a execution issue ("How and Where do i have to run that"). Maybe you need help with all three?

Regarding the first issue, connectivity, I do software not infrastructure and so can't offer much help. Sorry. On the second and third issues, I recommend you read over Microsoft's ADSI Scripting Primer:

http://www.microsoft.com/technet/scriptcenter/guide/sas_ads_overview.mspx?mfr=true

Note that this TechNet site only navigates correctly in IE.

Good luck.

ewbi.develops | 2007.03.01 09:20 AM

It seems you all have LDAP experience so I feel compelled to ask a related question:

(I understand if you are too busy but I'm hopeful I hear from you).

I have written a web-based form to update specific fields within LDAP. However when I try to add an image in base64 it takes the update but identifies it as a text attribute while all other profiles in ldap show a binary attribute when browsing the users. I've tried reading the file as binary and updating it that way but it errors. I also read somewhere the MS ADAM stores binary as base64 but maybe I'm wrong.

Anyway, is there some sort of switch I need to pass to identify that the attribute I'm updating is binary?

I've spent the last 2 days straight trying to solve this problem.

Any help from you or your community would be greatly helpful.

Vince Collins | 2007.03.22 10:48 AM

Hi Vince,

I wish I could help, but I'm afraid I've only ever consumed, not updated, LDAP info (and never a binary attribute). However, this post still gets a handful of visitors a day, so maybe someone will offer a suggestion.

Good luck.

ewbi.develops | 2007.03.22 11:04 AM

Vince,

I think ad stores binary as utf-16le
maybe base64codec could be used to encode/decode

dan | 2007.09.27 06:13 AM

hey,
i'm trying to query the Active Directory. i have two problems, first - how can i retrieve all users in a group when there are more then 1000 users. second - when using SQL dialect or LDAP dialect, how can i pass a parameter to the query?
i use Reporting services with Provider=ADsDSOObject.

the query is something like this:
SELECT cn,displayname
FROM 'LDAP://DOMAIN/dc=ORG,dc=com'
WHERE memberOf='CN=DVN00102950,OU=Divisions,OU=UnORG,OU=UnMNG,DC=ORG,DC=COM'
AND cn=ParamUserID

thanks a lot

itay | 2008.03.12 03:36 AM

Hi itay,

It's been a long time since I picked up an ADSI query. I do recall the 1,000 row limit. It seems like it was a server-configured limit, but I didn't control the server and probably wouldn't have changed it for our purposes anyway, particularly because any query resulting in more than 250 rows was too slow to consider running anyway. In the end we organized our query around alpha breaks (A's, then B's, then C's, etc.) that users would choose when searching.

There was an ADSDSOObject ADODB.Command object property that seemed to provide control over the row limit:

cmd.Properties("Size Limit") = X

However, it turned out that this property could only be used to reduce the resultset relative to the server-configured limit; it couldn't be used to overcome the server-configured limit.

Producing a report, though, is a different deal, as you'll definitely want to report all the available rows. In this case I suppose you might want to change the server-configured limit, if that's possible. Alternatively, have you considered pulling the AD contents in < 1000 row sections, populating a temporary table (you've got a Reporting Services SQL Server there), then dynamically (re)binding the report to the temporary data store.

In terms of parameterizing the query, I'm afraid I've never tried. All the ADSI queries I ever used were built dynamically. I assume you've tried simple placeholder parameters ("?") and ADODB.Command object Parameters? If that doesn't work, then I've no ideas. Perhaps someone reading this will be able to add more.

Good luck!

ewbi.develops | 2008.03.12 08:19 AM

I have done everything fine with ADSI SQL query, I have more than 3000 rows from AD. But I found that if I used properties of AD as column name I can not exceed more than 12 columns second I can not alias those columns.

How I can query more than 12 columns/properties.

when I query for * it gives me only distinguised name from AD, that is one column only.

Regards,
Ather Ali

Ather Ali | 2008.04.30 10:46 PM

Hi Ather,

I wasn't aware of a column limitation. I'm glad you brought it up, though, as it may influence whether others use SQL or LDAP for their queries. This old post still gets quite a bit of traffic, so perhaps someone with more knowledge of the column limitations (or even how to overcome them) will leave a comment.

Good luck!

ewbi.develops | 2008.05.01 08:51 AM

I am able to query more than 12 AD columns in my environment using LDAP dialect. I am not yet able to use SQL dialect. A work arround to a 12 column select list limit is to join multiple queryies each with the same filter and each with a select list with common join columns but other columms differing.

Here is an example I tested.

SELECT top 100 x.sn, x.cn, x.physicalDeliveryOfficeName code, y.building, y.department, y.edipi
FROM OPENQUERY( ADSI,
';
(&(objectClass=user)((Division=*FLT)));
edipi,
sAMAccountName,
CN,
displayName,
sn,
givenName,
middleName,
mail,
physicalDeliveryOfficeName,
telephoneNumber,
department,
building
,division
,ObjectCategory
,Division
,Company
,InstanceType
,SAMAccountType
,PrimaryGroupID
;subtree'
) as x,
OPENQUERY( ADSI,
';
(&(objectClass=user)((Division=*FLT)));
edipi,
sAMAccountName,
CN,
displayName,
sn,
givenName,
middleName,
mail,
physicalDeliveryOfficeName,
telephoneNumber,
department,
building
,division
,ObjectCategory
,Division
,Company
,InstanceType
,SAMAccountType
,PrimaryGroupID
;subtree' ) as y
WHERE x.edipi = y.edipi

mark | 2009.08.06 06:21 AM

***reposted with corrected HTML greater than and less then signs

I am able to query more than 12 AD columns in my environment using LDAP dialect. I am not yet able to use SQL dialect. A work arround to a 12 column select list limit is to join multiple queryies each with the same filter and each with a select list with common join columns but other columms differing.

Here is an example I tested.

SELECT top 100 x.sn, x.cn, x.physicalDeliveryOfficeName code, y.building, y.department, y.edipi
FROM OPENQUERY( ADSI,
'&ltLDAP://DC=NADSUSEA,DC=nads,DC=navy,DC=mil
(&(objectClass=user)((Division=*FLT)));
edipi,
sAMAccountName,
CN,
displayName,
sn,
givenName,
middleName,
mail,
physicalDeliveryOfficeName,
telephoneNumber,
department,
building
,division
,ObjectCategory
,Division
,Company
,InstanceType
,SAMAccountType
,PrimaryGroupID
;subtree'
) as x,
OPENQUERY( ADSI,
'&ltLDAP://DC=NADSUSEA,DC=nads,DC=navy,DC=mil
(&(objectClass=user)((Division=*FLT)));
edipi,
sAMAccountName,
CN,
displayName,
sn,
givenName,
middleName,
mail,
physicalDeliveryOfficeName,
telephoneNumber,
department,
building
,division
,ObjectCategory
,Division
,Company
,InstanceType
,SAMAccountType
,PrimaryGroupID
;subtree' ) as y
WHERE x.edipi = y.edipi

mark | 2009.08.06 06:28 AM

There shoould be
Less then and greater than signs arround the following LDAP strings in the above two posts

LDAP://DC=NADSUSEA,DC=nads,DC=navy,DC=mil

mark | 2009.08.06 06:31 AM

Thanks, Mark. The no-HTML-in-comments limitation here can be frustrating. I understand what you're suggesting, though, and it's a good workaround - I'm sure it will prove helpful for someone.

ewbi.develops | 2009.08.06 07:16 AM



Post a Comment

 
  (optional)
  (no html)
 
   


TrackBack

TrackBack URL:  http://www.typepad.com/services/trackback/6a00d8341c7bd453ef00d8349fe99869e2

Listed below are links to weblogs that reference ADSI Query Dialects - LDAP Wins: