Active directory provides a number of services relating tot the organised storage of network resources. Through LDAP (Leightweight
Directory Access Protocol) you can access this information.
Warning: When querying AD you will notice
that certain SQL features are not implemented. Select * from does not return all the attributes. The table name in a select statement
is a LDAP path that describes the complete path to reach the object. It therefore partly contains information
you would normally write in a where clause.
To query AD you first need to
connect to Active Directory. See this page for information how to connect
to AD from SQL*XL. The real connection seems to be
done through the LDAP path you are using.
The basic query statements for AD looks like
this: select * from 'LDAP://myserver' select * from
The paths need to be read from right to left. The object in line 3 can
be found in Domain Component (DC) 'nl'. Within 'nl' it can be found in DC
'linker'. Within the domain component 'linker' is a Common Name (CN) called
'Builtin'. When you read further down you will find an entry that describes the
users list with a common name of 'Users'
To get a list of all the users I need to somehow access
the object with common name (CN) 'Users'. I can use the objectClass attribute to
filter all objects of a certain class. First I need to know which class name I need to use. I
am running the following query to find out the objectClass.
Do not expect to get all the attributes of an object from these queries. Executing a select * query will
just return one attributed called ADsPath that contains only the full path to the
object. You can use the path to select just that one object. I can e.g. run the
following query with the adspath in the one but last line in the results
With these results a few things become clear. The
ADsPath is the unique path to the object. The objectClass is user but user is
derived of organisationalPerson which is derived from person which is in turn
derived from the top level class. The class user will
inherit all the attributes of the higher classes and have unique attributes on its own.
We can now use this information to dig deeper. In the
following queries I will return all the objects that have a objectClass of user. Note
that a match is found when user is one of the classes in the list.
Query: select adspath,objectclass from
'LDAP://linker.nl' where objectClass=
These results show a list of users. However the last rows show that the
computer class is a subclass of user as well. Another curious observation is
that the columns are printed in the wrong order. I asked adspath first and
objectclass next, the results are just in the opposite order.
You could not pick a user to investigate further. To just select the user
gjlinker I could
Now to find the attributes of the class user you need to inspect the
reference. Please find it at microsoft's website at this url. Attributes that are specific to the user class
are profilePath, logonHours and profilePath. I must say that these
attributes were not populated in my Active Directory. Then I have not atttempted
to record any information for my user account and just used ADSI Edit to create
my new user.
I used Windows2000 Advanced server as test platform. I performed the above
queries using SQL*XL 4.0.31 at the same server.
Copyright (C) 1995-2014 Linker IT
Software BV. All Rights Reserved. Oracle is a registered trademark of
Oracle corporation. Excel and Office are registered trademarks of
Microsoft corporation. Other names appearing on the site may be trademarks
of their respective owners. Software,
files, documents, articles and other material are provided
"as is" and without warranties as to performance or mechantability or
any other warranties whether expressed or implied. No
warranty of fitness for a particular purpose is offered.