Running Active Directory Services Queries Using MS SQL Server T-SQL OPENQUERY Command

Using Microsoft SQL Server, you can connect to Microsoft Active Directory Services applications in your network and query users or objects in the Active Directory Services.

Connecting to an Active Directory domain can be useful in many ways and gives environmental informations about users in an intranet or local network for sql server programmers and also .NET developers
For instance, as a database programmer you can get emails of your network users and send them emails when specific events are triggered in your SQL Server database. Or as a .NET developer you can create an application like MS Outlook mail client, where you can find and send email to people by querying them according to their names.

What is LDAP (Lightweight Directory Access Protocol)?

LDAP is a standart internet protocol used to get information from a directory service.
We will use LDAP to connect to an Active Directory Service and query the directory information.

Create a Linked Server to the Active Directory Services

First of all define a Linked Server to the Active Directory Services using the below t-sql command.

EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'

Enable QPENQUERY in SQL Server 2005 and run QPENQUERY T_SQL Statements

Then try running the below LDAP SELECT queries using t-sql OPENQUERY command.

Be aware that, Microsoft SQL Server 2005 by default prevents the usage of "OpenQuery", in a way OpenQuery is disabled by default.
To enable "OpenQuery" you can refer to article titled "How to enable the use of 'Ad Hoc Distributed Queries' by using sp_configure" at Kodyaz.com.
You might have to run the below t-sql script batch in order to enable OpenQuery in a MS SQL Server instance.

sp_configure 'show advanced options', 1
reconfigure with override

sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure


SELECT * FROM OpenQuery(ADSI, 'SELECT * FROM ''LDAP://DC=kodyaz,DC=com'' WHERE objectCategory=''user'' ')

SELECT * FROM OpenQuery(ADSI, 'SELECT mail, displayName, userPrincipalName FROM ''LDAP://DC=kodyaz,DC=com'' WHERE objectCategory=''user'' ')

You can see that you should use the attribute names properly in the t-sql OPENQUERY statements.

Common LDAP Attributes for Active Directory Entries

Here is a list of common attributes with names and short descriptions:

sAMAccountName	NT account or logon name
displayName
description
CN	Common Name
DN (distinguishedName)
givenName	Firstname of a user
SN	Surname
name	Common name
objectCategory	Active Directory Schema category
objectClass	Defines if is a user, computer, organizational unit or a container
userPrincipalName (UPN)
mail
c	Country/Region
company	Company/Organization name
department	Department
manager	Manager name

You can use these common attributes in the LDAP queries or in your vbs scripts/VBScript according to your needs.