[thelist] Friday Freebie

[Scott Dexter]

<tip type="Accessing LDAP with ADO" Author="Scott Dexter"
email="sgd at ti3.com">
You can get at LDAP stores through ADO, and work with items as you would
with a recordset. The fun thing is there is a use for the ADODB.Command
object here (which I've mentioned you can almost always do without).

In order to pull this off, you'll need ADSI installed (the current version
is 2.5, and highly recommended cause it fixes a lot of things; if you're
running Win2K you already have it), at least MDAC 2.1 (the current supported
release is 2.5sp1), and have an existing LDAP directory on a server that you
wanna talk to.

First, let's connect to the LDAP directory:
<%
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Provider="ADSDSOObject"
oConn.Open "ADs Provider","cn=Administrator;ou=members;o=OurOrg","adminpwd"
%>
--That's it, you're ready to connect to the LDAP directory

Next, let's pull some data out:
<wrapwarn>
<%
'sqlstr = "Select cn,lastVisit from
'LDAP://MyServ:1005/o=PerfPlus/ou=Members' where objectClass='member'"

sqlstr =
"<LDAP://MyServ:1005/o=PerfPlus/ou=Members>;(objectClass=member);cn,lastVisi
t;subtree"
%>
</wrapwarn>

These two statements are equivalent. You can use a SQL based Select
statement, though the extent of Where clauses I believe is limited,
basically you use it to specify what kind of objects you're looking for.
Correlated sub queries and such I don't think will work. Try it and find out
=). The fieldnames are the short names for the properties.

The second form is an LDAP query in attributed name format, which looks like
this (taken from MS KB article Q187529, link at bottom of tip):

<LDAP://server/adsidn>;ldapfilter;attributescsv;scope 

where: 
server is the name (or IP address) of the server hosting the directory. 

adsidn is the distinguished name (DN) of the starting point for your query
expressed ADsPath format with "/" separators and the root of the namespace
to the left. You can also use an X.500 style attributed name format with the
relative distinguished names separated by commas and the root of the name
space to the right. 

ldapfilter is the LDAP filter string (see rfc2254). 

attributescsv is a comma separated list of names of the attributes to be
returned for each row in the recordset. 

scope is either: base, onelevel, or subtree. 

So, either way, we're pulling out members' common names (cn) and the last
time they logged in (lastVisit, an attribute I added to the LDAP store
previously):
<%
Set oRS=oConn.Execute(sqlstr)
if (oRS.BOF and oRS.EOF) then
	Response.Write "Nothing to find...?"
else
	oRS.MoveFirst
	Do while not oRS.EOF
		lastv = oRS("lastVisit")
		cn = oRS("cn")
		' do processing in here
		oRS.MoveNext
	Loop
end if
%>

A couple caveats:
1) Accessing the recordset fields using ordinal notation (oRS(0)) for some
reason doesn't work. At least it bombed on me. go figure.
2) String attributes are returned as arrays, even when they only have one
value. The Good Practice is to test the value before working with it:
<%	if isArray(cn) then username=cn(0) else username = cn %>

Now, that's all fine and dandy, except you figure out you're not getting all
the data that's available. I couldn't find it in the registry, but despite
the documentation saying there is no default limit on the data returned,
there seems to be a 500 record limit.

Shit. How do we get all of them? --Our new friend the ADODB.Command object.
We can tweak the connection properties to bring back what we want:
<%
Set oConn = Server.CreateObject("ADODB.Connection")
Set oCmd = Server.CreateObject("ADODB.Command")
oConn.Provider="ADSDSOObject"
oConn.Open "ADs Provider","cn=Administrator;ou=members;o=OurOrg","adminpwd"
Set oCmd.ActiveConnection=oConn
oCmd.Properties("Size Limit") = 10000
oCmd.Properties("Page Size") = 10000
oCmd.CommandText = sqlstr
Set oRS = oCmd.Execute
%>

The Size Limit property governs the overall number of records to return. The
Page Size tweaks the number of records returned in a single call (a page).
LDAP does some pretty sophisticated results caching to make it lightning
fast, so for our use instead of paging through the results we're just gonna
demand everything at once (by specifying an arbitrarily high number).

That's pretty much it. Just make sure if you use a server name in your query
that the machine running the code can resolve the name. Sometimes that gets
me in trouble =)

Links:
<wrapwarn>
LDAP Query syntax:
http://support.microsoft.com/support/kb/articles/Q187/5/29.ASP
LDAP Connection properties (also a howto):
http://msdn.microsoft.com/library/psdk/adsi/ds2prggd_ret_7ycf.htm
ADSI and ADO examples:
http://msdn.microsoft.com/library/default.asp?URL=/library/psdk/exchserv/dir
ectry_6r77.htm
</wrapwarn>
</tip>

sgd
--
work: http://www.ti3.com/
non: http://thinksafely.org/