LDAP, the “lightweight directory access protocol” is a strange beast. It’s technically a protocol for accessing network directories (directories as in phone directories, not folders), but it sort of qualifies as a no-SQL database because its distributed addressing mechanism doesn’t consist of relations like a RDBMS, it has entries with attributes. These are somewhat like objects with properties if your thinking is object-oriented.
Querying LDAP It’s quite difficult to find information about LDAP queries, most of it relates to querying AD directly through AD management tools like the MMC snap-in. The data is structured but not easily queried or analysed so as with my thinking on other forms of NO-SQL data storage, it is not an end in itself, you are always going to have to structure it, or at least get it into a language you like to work with. If that happens to be Java or some other language you like then fine, but I like to think in sets and prefer SQL so that’s my first step; get the data into a database so you can do useful things with it like make it dance.
Here’s a basic way to start querying your AD system via SQL Server. My specific need was to help the senior network manager access their infrastructure specifically relating to a Microsoft Office upgrade to determine how many licenses they require and how many PC’s should fall into the appropriate automatic software install groups. The installs are managed automatically by placing people in the appropriate groups.
Create a linked server, this should work as is, and you don’t need to change it for different servers.
This is assuming the SQL Server machine has access to the LDAP server(s) i.e. if you can run MMC with the AD snap-in on the machine then you should be able to set up this linked server.
sp_addlinkedserver ‘ADSI’, ‘Active Directory Service Interfaces’,’ADSDSOObject’, ‘adsdatasource’
Once the linked server is set up, you can run queries using the OpenQuery() function in SQL Server.
Running a query
SELECT * FROM OpenQuery(ADSI, ‘SELECT cn, distinguishedName FROM ”LDAP://OU=Client Computers,OU=Resources,DC=Domain,DC=com,DC=au” ‘)
–Note the LDAP address is DC=Domain,DC=com or in my case DC=Domain,DC=com,DC=au where the domain is domain.com.au — This query assumes you have OU’s set up with the structure “Resources\Client Computers” The LDAP addresses tend to read backwards like that, somewhat like URI’s where the top level domain is on the far right and the sub-domains get deepers as you read to the left. –OU organisation units are as per the hierarchy set up within AD.
There is one Caveat to this in an AD environment. The default MaxPageSize setting is 1000. That setting controls most tools in terms of how many records you can see on screen at once. The downside is any query you run with this method will be limited to 1000 records, and there is no way in the Linked Server setup to override that setting due to a limitation in the LDAP provider. It is possible to override this in other tools however.
If you need to return more than 1000 records there are two options. Preferably increase the MaxPageSize setting (requires some command line options to be run) or if you don’t have access to do that then do some ugly unions based on filtered sets of data. Each data set returned should be less than 1000 records for each chunk of data between the UNIONs. Heres an example of using Union to do just that. You might have to adjust the attributes to those that you require.
SELECT sAMAccountName,CN, ADsPath FROM OpenQuery(ADSI, ‘<LDAP://DC=Domain,DC=com,DC=au>;(&(objectClass=User)(|(sAMAccountName=A*)(sAMAccountName=B*)(sAMAccountName=C*)(sAMAccountName=D*)) );sAMAccountName,CN, ADsPath, sn;subtree’) UNION ALL SELECT sAMAccountName,CN, ADsPath FROM OpenQuery(ADSI, ‘<LDAP://DC=Domain,DC=com,DC=au>;(&(objectClass=User)(|(sAMAccountName=E*)(sAMAccountName=F*)(sAMAccountName=G*)(sAMAccountName=H*)) );sAMAccountName,CN, ADsPath;subtree’)