SQL*XL online help

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 'LDAP://mydomain.com'
select*from'LDAP://DC=mydomain,DC= com'

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 above:

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.

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 write:
select*from'LDAP://linker.nl/CN=gjlinker,CN=Users,DC=linker,DC= nl'

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.

See also:

• SQL dialog
• Resultset Options dialog
• Query: Get data into Excel
• Dialogs
• Menu and Toolbar Items
• SQL*XL Home