Querying Active Directory (LDAP) from SQL Server

LDAP

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.

Getting started

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.

 

Step 1

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.

Step 2

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.

Important!

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’)

 

5 thoughts on “Querying Active Directory (LDAP) from SQL Server

    1. Yes I can confirm this works in SQL Server 2012, and in 2014. I’m not suprised by that either. You can even just type an LDAP query into an internet browser and it will work. It’s just a protocol with an address and some arguments.

      LDAP querying can be generally painful and finicky. Can you get into AD via MMC or some other tool?
      You might be able to use that to then see what the OU structure is in your shop’s AD.
      Then try some simpler variation on this to see if you can get anything back at all:
      SELECT * FROM OpenQuery(ADSI, 'SELECT cn, distinguishedName FROM ''LDAP://OU=Client Computers,OU=Resources,DC=Domain,DC=com,DC=au'' ')

      You might even be able to browse the linked server’s structure using SSMS. In the object explorer, under server objects, expand the tree for the Linked Server.

  1. Hi there,

    When we run thi directly on server, it works well. But same when excuted via client throws following error message. Any thoughts?

    Msg 7321, Level 16, State 2, Line 2
    An error occurred while preparing the query “SELECT givenName,sn,employeeid,sAMAccountName,mail,type,whenChanged
    FROM ‘LDAP://*****/DC=*******,DC=***,DC=**’
    WHERE objectClass = ‘User’ AND objectCategory = ‘Person’ AND (
    SN = ‘A*’
    )
    ” for execution against OLE DB provider “ADsDSOObject” for linked server “ADSI”.

  2. Hi there,
    can yours help me with Querying Active Directory (LDAP) from SQL Server. I need to retrieve these attributes:

    sAMAccountName
    givenName
    sn
    departmentNumber

    My query is:

    SELECT top 800 * FROM OPENQUERY(ADSI, ‘SELECT departmentNumber,sn, givenName, displayName, sAMAccountName
    FROM ”LDAP://ABCDCOP01.XXX.int/DC=XXX,DC=int”
    WHERE objectClass = ”User” and objectCategory = ”person”
    ‘)

    when I run, I get a message:

    Msg 7341, Level 16, State 2, Line 1
    Cannot get the current row value of column “[ADsDSOObject].departmentNumber” from OLE DB provider “ADsDSOObject” for linked server “ADSI”. Could not convert the data value due to reasons other than sign mismatch or overflow.

    Note: I know that this message is because the attribute departmentNumber. How can I solve this? because if I remove these attribute I get the result.

    1. Hi Claudio,
      sorry for the late reply.

      I think that OPENQUERY is trying to determine the datatypes being returned from LDAP and getting it wrong for the departmentNumber field. Instead of doing a SELECT TOP 800 *, select the fields individually and cast the departmentNumber field to a TEXT datatype, or perhaps a SQL_Variant to accomodate potentially different datatypes in the same column.
      There are some gotchas with LDAP queries, see this

      ‘Select *’ in a query against AD does not return all fields as you might expect that it would. Instead, it returns the ADsPath of the object. There is no way to return all fields. – See more at: http://www.skylinetechnologies.com/Insights/Skyline-Blog/May-2013/Querying-Active-Directory-through-SQL-Server-Using#sthash.0N5MhUHC.dpuf

Leave a Reply

Your email address will not be published. Required fields are marked *