Querying Active Directory from .NET code is pretty easy and straightforward. Doing the same from SQL Server is not too difficult but there is one glaring issue in my opinion - your result set is limited to 1,000 records. For a small company this may be fine but for larger corporations this creates a problem. In .NET code, you can set the page size for your DirectorySearcher object. Unfortunately, there is no way to do that in SQL Server. At least none that I was able to find. So while you can query AD directly when you know you'll have a small recordset, there are times when you will need more than 1,000 records. To get around this issue, I did the next best thing: I created a table to store the AD data and then created a job to refresh that data about 6 times a day. So while not real-time, it provides timely-enough data for most purposes.
Step 1: You need to create a linked server that points to AD. Use the following the SQL to do that. In addition, AD will require authentication to query it so in my case I had our network admin create a domain account that has read-only rights to AD.
EXEC
sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Services', @provider=N'ADsDSOObject', @datasrc=N'your.fully.qualified.ldap.server;'
Step 2: I created a table to store the data and then used the following SQL (thanks Google) to populate the table. After confirming that the stored procedure worked, I simply setup a SQL job to run this stored procedure several times a day.
CREATE
PROCEDURE [dbo].[uspCreateActiveDirectoryTable]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
---------------------
-- Declare Variables
---------------------
DECLARE @chvAlphaChars VARCHAR(60)
,@chvSearch VARCHAR(10)
,@chvSearchLevel1 VARCHAR(1)
,@chvSearchLevel2 VARCHAR(1)
,@chvSearchLevel3 VARCHAR(1)
,@intcountLevel1 INT
,@intcountLevel2 INT
,@intcountLevel3 INT
,@intRowCount INT,@strSQL NVARCHAR(4000) ,@strADSISQL NVARCHAR(4000)
------------------------------------
-- Delete existing data from table
------------------------------------DELETE dbo.tblActiveDirectoryUsers
-------------------------------------------------
-- Search letters to cycle through
-- any chars, but the first char must be a space
-------------------------------------------------SET @chvAlphaChars = ' ABCDEFGHIJKLMNOPQRSTUVWXYZ'
---------------------------
-- Start on non space char SET @intcountLevel1 = 2
---------------------------
--------------------
-- First level loop
--------------------WHILE @intcountLevel1 <= LEN(@chvAlphaChars) BEGIN
-- Get first level char SET @chvSearchLevel1 = SUBSTRING(@chvAlphaChars,@intcountLevel1,1)
-- Reset start on space SET @intcountLevel2 = 1
-- Second level loop WHILE @intcountLevel2 <= LEN(@chvAlphaChars) BEGIN
-- Reset start on space SET @intcountLevel3 = 1
-- Third level loop WHILE @intcountLevel3 <= LEN(@chvAlphaChars) BEGIN
-------------------------------------------------------------------------------------------------------------
-- Setup the string to search for. By using the trim function we can form each level depending on no records
-- eg A 99, B 1000 > BA 9, BB 20 etc
-- Trim the spaces forming just A, B, C ; AA, AB for search etc
-------------------------------------------------------------------------------------------------------------SET @chvSearchLevel1 = SUBSTRING(@chvAlphaChars,@intcountLevel1,1)
SET @chvSearchLevel2 = RTRIM(SUBSTRING(@chvAlphaChars,@intcountLevel2,1)) SET @chvSearchLevel3 = RTRIM(SUBSTRING(@chvAlphaChars,@intcountLevel3,1))
SET @chvSearch = @chvSearchLevel1 + @chvSearchLevel2 + @chvSearchLevel3 SET @strADSISQL = 'SELECT samaccountname,sn,givenname,mail,mobile,telephonenumber,homephone,facsimiletelephonenumber,physicaldeliveryofficename '
+ CHAR(13) + 'FROM ''''LDAP://your.fully.qualified.ldap.server/DC=aaaaa,DC=bbbbb,DC=ccccc'''' ' + CHAR(13) + 'WHERE objectCategory = ''''Person'''' '
+ CHAR(13) + 'AND objectClass = ''''user'''' ' + CHAR(13) + 'AND sAMAccountName = ''''' + @chvSearch + '*'''' '
SET @strSQL = 'INSERT INTO dbo.tblActiveDirectoryUsers (user_name,last_name,first_name,email_address,mobile_phone,work_phone,home_phone,fax_number,office_location) '
+ CHAR(13) + 'SELECT samaccountname AS user_name,sn AS last_name,givenname AS first_name,mail AS email_address,mobile AS mobile_phone,telephonenumber AS work_phone,homephone AS home_phone,facsimiletelephonenumber AS fax_number,physicaldeliveryofficename AS office_location ' + CHAR(13) + 'FROM OPENQUERY(ADSI,''' + @strADSISQL + ''' ) '
EXEC SP_EXECUTESQL @strSQL
SET @intRowCount = @@ROWCOUNT
-- Prints what string is being searched for : no of inserts. DEBUG CODE
-- PRINT @chvSearch + ' : ' + CONVERT(VARCHAR,@intRowCount)
-- If searched on @chvSearchLevel1 and under 1000 then everything is fine so skip search2 to next search1 eg A > B IF @intRowCount < 1000
AND @chvSearchLevel2 = '' SET @intcountLevel2 = @intcountLevel2 + 100
-- If searched on @chvSearchLevel2 and under 1000 then everything is fine so skip to next search2 eg AA > AB IF @intRowCount < 1000
AND @chvSearchLevel3 = '' SET @intcountLevel3 = @intcountLevel3 + 100
-- Else over 1000 so increment third level SET @intcountLevel3 = @intcountLevel3 + 1 END
------------------------------------
-- Increment next second level char
------------------------------------ SET @intcountLevel2 = @intcountLevel2 + 1 END
------------------------------------
-- Increment next first level char
------------------------------------ SET @intcountLevel1 = @intcountLevel1 + 1
END
END