Getting Data From Active Directory and who is Troy?

With integration ever on the increase, leveraging data stored in the Active Directory is proving to be an increasingly common requirement. Typical scenarios may include efforts to build a configuration database through to building a schema to support row level security for reporting.

Access to active directory data can be retrieved as simply as a linked server configuration and data access using OpenQuery. Brendan Tompkins provides a simple guide to achieving this but keep in mind the importance of getting the adsdatasource and port correct.

Brendan Tompkins A.D. Query Example

Some Ldap query examples are provided here – see Examples.

If linked servers are not your thing or you have problems accessing through the linked server then there is a tool that is installed with the Lightweight Directory Services Role for Windows 2008 called “csvde.exe” see CSVDE. This tool provides a command line interface to the active directory server to extract data in csv format using various object queries.

If you are using a non Microsoft Ldap v3 compliant directory server keep in mind the csvde.exe tool will still work, however you may run into problems. For a start rows may be paged in 500 row lots and csvde cannot page through the data automatically. Secondly, the columns may return to the csv file out of order when returning from the LDAP v3 compliant LDAP server (note this is unrelated to the –l behaviour as design which can return columns out of order if you specify specific attributes in your query against a Microsoft Active Directory Server). As a workaround you can get a Powershell guru like Troy to write a script to rearrange the data in the csv file.

There are third party tools available for a fee that allow you to interrogate and create command line extracts for LDAP v3 directory servers however be sure to test these tools thoroughly to handle the issues around data paging and out of order columns.

For non Microsoft LDAP servers that utilise single source of the truth for the directory in an  ODBC or OLDEB database, you also have the option of bypassing all of the above and extracting that data directly from the LDAP database using SQL Server Integration Services.

Happy A.D.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s