SQL Server – Extract Active Directory Data into a DB Table

We ran into an issue where a third party application running on SQL-Server 2005 could not interface with Active Directory because the application manages its own setup of user security accounts. We needed the existing users in Active Directory to be copied to this third party applications security tables and kept insync with Active directory. There were about a thousand user accounts and hand typing was not an attractive option as you can imagine. Rather than have Human Resources input each employee by hand, we came up with a way to export employee names from active directory and import them into the applications tables.

Our method was to connect to Active Directory using LDAP and a SQL Server database link. We created a stored procedure to perform the extract and scheduled it to run periodically to delete or add new employees, since the applications needed to be insync with the latest account changes in Active Directory.

To extract information from Windows Active Directory into SQL Server 2005 database first you need to create a linked server connection to Active Directory via LDAP.

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

Next enable OpenRowSet and OpenDataSource support (Also known as AD Hoc Queries) if it is not already enabled.

EXEC sp_configure 'show advanced options',1; 

GO; 

reconfigure; 

GO; 

EXEC sp_configure 'Ad Hoc Distributed Queries',1; 

GO; 

reconfigure; 

GO;

Once you have enabled Ad Hoc Queries you can query Active Directory via LDAP, but first you will need to determine the proper ldap string. This is usually your windows active directory domain name and extension. If your domain was mycompany.int then the ldap string would be:

LDAP://DC=mycompany, DC=int

Once you have your ldap string you can use the query below to extract employeeID, displayName and distinguishedName from Active Directory. The query we’re using is commonly available on the internet and can be customized to extract the information you need. It extracts the information from Active Directory and inserts into a temporary table called #ADUsers.

Once you have your ldap string you can use the query below to extract employeeID, displayName and distinguishedName from Active Directory. The query we’re using is commonly available on the internet and can be customized to extract the information you need. It extracts the information from Active Directory and inserts into a temporary table called #ADUsers.

CREATE TABLE #ADUsers 

( email varchar(255) NULL, employeeID varchar(10) NULL, 

displayName varchar(255) NULL, 

distinguishedName varchar(255) NULL);  

GO;  

/* AD is limited to send 1000 records in one batch.*/

/* To work around this limit we loop through the alphabet. */  

DECLARE @cmdstr varchar(255)  

DECLARE @nAsciiValue smallint  

DECLARE @sChar char(1)  

SELECT @nAsciiValue = 65  

WHILE @nAsciiValue < schar="">
CHAR(@nAsciiValue)" dc="mycompany," dc="int''''
WHERE" objectcategory =" ''''Person''''" samaccountname =" ''''%s*''''''" 
nasciivalue =" @nAsciiValue">
If you plan on running the extract as a stored procedure from a scheduled job it makes more sense to use a permanent table rather than temporary table.
CREATE TABLE ADUsers 
( email varchar(255) NULL, 
employeeID varchar(10) NULL, 
displayName varchar(255) NULL, 
distinguishedName varchar(255) NULL);  
GO;
This query imports the data into the ADUsers table we created above. Note that the first step of our extract is to truncate the ADUsers table.
CREATE PROCEDURE [dbo].[sp_extract_AD]  AS  TRUNCATE TABLE ADUsers;  /* AD is limited to send 1000 records in one batch. to work around this limit we loop through the alphabet. */  DECLARE @cmdstr varchar(255)  DECLARE @nAsciiValue smallint  DECLARE @sChar char(1)  SELECT @nAsciiValue = 65  WHILE @nAsciiValue < schar=" CHAR(@nAsciiValue)" dc="mycompany," dc="int''''WHERE" objectcategory =" ''''Person''''" samaccountname =" ''''%s*''''''" nasciivalue =" @nAsciiValue">
Now we can create a scheduled job to execute the sp_extract_AD stored procedure periodically to pickup any changes in Active Directory and export them to our time tracking application database.