Querying Active Directory Through SQL Server

One great thing about fishing is being out in nature. It’s great seeing bald eagles cruising around looking for food, hearing the eerie call of a loon, and smelling the air that seems so much fresher than the air on the land. Observing the tranquility of nature can be very relaxing and very fulfilling.
SQL Server is great because it’s a Microsoft product and that helps it to easily “observe” the data in other Microsoft projects. I was recently asked to import some data from Active Directory into a SQL Server table. This data was going to be used as a feed for our company directory. A quick Google search showed I had two options. Both involve using OPENQUERY(), but the first has you create a linked server whereas the second queries the data source with the definition included. The second method requires Ad Hoc Distributed Queries to be enabled on the server, and my server does not have that enabled, so I went ahead and created a linked server:

USE [master]
GO

EXEC MASTER.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Service Interfaces', @provider=N'ADSDSOObject', @datasrc=N'adsdatasource'
EXEC MASTER.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ADSI',@useself=N'False',@locallogin=NULL,@rmtuser=N'DomainName\ServiceAccount',@rmtpassword='########'

GO

EXEC MASTER.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC MASTER.dbo.sp_serveroption @server=N'ADSI', @optname=N'data access', @optvalue=N'true'
GO

EXEC MASTER.dbo.sp_serveroption @server=N'ADSI', @optname=N'dist', @optvalue=N'false'
GO

EXEC MASTER.dbo.sp_serveroption @server=N'ADSI', @optname=N'pub', @optvalue=N'false'
GO

EXEC MASTER.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc', @optvalue=N'false'
GO

EXEC MASTER.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc out', @optvalue=N'false'
GO

EXEC MASTER.dbo.sp_serveroption @server=N'ADSI', @optname=N'sub', @optvalue=N'false'
GO

EXEC MASTER.dbo.sp_serveroption @server=N'ADSI', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC MASTER.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation name', @optvalue=NULL
GO

EXEC MASTER.dbo.sp_serveroption @server=N'ADSI', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC MASTER.dbo.sp_serveroption @server=N'ADSI', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC MASTER.dbo.sp_serveroption @server=N'ADSI', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC MASTER.dbo.sp_serveroption @server=N'ADSI', @optname=N'remote proc transaction promotion', @optvalue=N'true'

My next step was to create a table to hold the information I wanted. There are a TON of fields from Active Directory that can be captured, but I was only interested in a few. I also added a ‘Type’ field at the end so I could put in some other data and differentiate it with the employee data.

CREATE TABLE [dbo].[LW_ActiveDirectory](
   [DisplayName] [VARCHAR](255) NOT NULL,
   [Email] [VARCHAR](355) NULL,
   [Extension] [VARCHAR](10) NULL,
   [DirectDial] [VARCHAR](25) NULL,
   [Mobile] [VARCHAR](25) NULL,
   [Type] [CHAR](1) NOT NULL
) ON [PRIMARY]

Next I created a stored procedure that captures the data using the Linked Server, and inserts it into my new table:

CREATE PROCEDURE [dbo].[RefreshADTable]

AS
BEGIN

   SET NOCOUNT ON;

   --Clear the table first
   TRUNCATE TABLE DatabaseName.dbo.LW_ActiveDirectory

   --Latest employee information
   INSERT  INTO DatabaseName.dbo.LW_ActiveDirectory
           ( DisplayName ,
             Email ,
             Extension ,
             DirectDial ,
             Mobile ,
             TYPE
           )
           SELECT  displayname ,
                   mail ,
                   homephone ,
                   ipphone ,
                   mobile ,
                   'E' AS TYPE
           FROM    OPENQUERY(ADSI,
                             'SELECT displayName, mail, mobile, homephone,ipphone
	  FROM  ''LDAP://ldapurl.com/OU=Users,OU=MyOU,DC=DC1,DC=com'' 
	  WHERE objectClass =  ''User''
	  AND useraccountcontrol <> 514
	  AND physicaldeliveryofficename <> ''no phone''
	  ') AS tblADSI

   --Delete non-person users
   DELETE
   FROM DatabaseName.dbo.LW_ActiveDirectory
   WHERE DisplayName IN('Laptop','Report User','time test','manager')

   
END

A few notes on this code:

  1. The LDAP tree is navigated from right to left.  This means I was looking for ldaprul.com/DC1/MyOU/Users.
  2. I excluded a useraccountcontrol value to get rid of inactive employees.  This value could be different for you.
  3. There were several employees with no phone information.  I had to go into Active Directory and put “no phone” into an unused field [Office] to exclude these from my extract.
  4. I also had to delete a few legacy users that couldn’t be deleted from Active Directory, such as Laptop and manager.

Next I create a SQL Agent job to run this stored procedure once a day.  We don’t need data more up-to-date than that for our purposes.

The last step was to go into Active Directory and add any missing information to each user.  This was a pain, but worth the effort.  When new employees are added the HR person here will enter this information.  Now we have a employee directory that will have the latest information in the database each day.  We used a reporting piece to create a printable directory sheet.

An upcoming blog post will show how to do this same thing in Oracle.  Stay tuned!

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s