Accessing Active Directory From SQL Server

Christina recently had the need to send notification e-mails to Business Portal users based on certain transactions that had been entered. To keep things simple, she was looking for a solution that could be implemented in SQL Server. After discussing it briefly, we were pretty confident that we could setup a simple routine to send e-mail from SQL Server. But there was a catch--we only had access to the user's Windows /Domain username in the SQL database, and we did not have the user's e-mail address stored anywhere in the GP database. However, the e-mail addresses were stored in Active Directory. So the search was on to find a way to access Active Directory from SQL Server.

It turns out that it is surprisingly easy to setup a simple SQL Server query that will allow you to access information in Active Directory (AD). There are several methods for accessing AD, each with different pros and cons, but given our simple requirements, we went with the simplest approach.

After searching through a few Google results, I found this sparse blog entry that shared two straightforward commands for querying AD.

The first step is to create a linked server that will allow you to invoke the Active Directory Service Interface (ADSI) interface:


sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource'

Once that is setup, you can query AD directly using Lightweight Directory Access Protocol (LDAP) queries. The syntax is a bit obscure, but once you see a few examples it becomes pretty clear and straightforward. Here is a great site that offers Excel files listing many of the AD attributes that are exposed via LDAP.

After some testing, I came up with the following query which allowed me to retrieve my name and e-mail address from my Active Directory server:

SELECT * FROM OPENQUERY

(ADSI, 'SELECT mail, displayName, sAMAccountName FROM ''LDAP://OU=Consultants,DC=precipio,DC=local'' WHERE objectCategory = ''Person'' AND objectClass = ''user''')

WHERE mail IS NOT NULL AND sAMAccountName = 'steve'
Naturally, you will use your AD domain name for the "DC" parameters. And in my query, I chose to limit my search to the "Consultants" Organizational Unit (OU), but you can remove that filter to view all accounts in all OUs.

With a bit of searching, I found that the Windows username is called "sAMAccountName", and the e-mail address is simply called "mail".

So, this query allowed me to find the user's e-mail address in AD based on their username. Once I had the e-mail, I was able to write some SQL to generate an e-mail.

I'll cover that in my next post...