Recently there was a requirement from one of my customers to query Active Directory and load the data into a table in sql server. To be honest I am not developer so I started researching on the topics over the net and took help of some of the internal resources which I had access to. So in this Blog post I am listing the different ways to query AD from sql server.

First and the most easiest way of querying AD is by creating a Linked Server.

Using Linked Server
===============

We create a Linked Server from Sql Server to connect to the Active Directory and then we can use the Linked server to perform OpenQuery. The complete method is described in Books Online and the same can be found at the following link

http://msdn.microsoft.com/en-us/library/aa772380(VS.85).aspx

The above approach is good but it works only when the number of rows returned from AD is less than 1000. If we want to query more than 1000 rows the above approach wont work because of the limitation of pagesize in AD which Is set to 1000 by default.

We can change the PageSize at the AD but there can be performance implications and hence we did not take that approach.

In our case the Linked server approach did not work since we wanted to query more than 1000 rows. So we researched over the internet and found the following Stored Procedure in one of the Forums.

Use COM Objects
===============

I would like to share the Stored Procedure which uses COM objects within sql server using the system sps viz sp_Oacreate, sp_OAMethod, sp_OAgetproperty and sp_OAsetproperty.

I could not find the Author of the Stored Proc so I apologize as I cannot provide credit to right and deserving person.

Note: In order to use the below Stored Procedure we need to enable “Ole Automation Procedures’ using sp_configure as shown

sp_configure ‘Ole Automation Procedures’,1
reconfigure

 

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[spQueryAD]    Script Date: 01/13/2010 09:14:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[spQueryAD] (@LDAP_Query varchar(255)=”, @Verbose bit=0)
as

–verify proper usage and display help if not used properly
if @LDAP_Query =” –argument was not passed
BEGIN
Print ”
Print ‘spQueryAD is a stored procedure to query active directory without the default 1000 record LDAP query limit’
Print ”
Print ‘usage — Exec spQueryAD ”_LDAP_Query_”, Verbose_Output(0 or 1, optional)’
Print ”
Print ‘example: Exec spQueryAD ”SELECT EmployeeID, SamAccountName FROM ””LDAP://dc=domain,dc=com”” WHERE objectCategory=””person”” and objectclass=””user”””, 1′
Print ”
Print ‘spQueryAD returns records corresponding to fields specified in LDAP query.’
Print ‘Use INSERT INTO statement to capture results in temp table.’
Return –’spQueryAD aborted’
END

–declare variables
DECLARE @ADOconn INT — ADO Connection object
, @ADOcomm INT — ADO Command object
, @ADOcommprop INT — ADO Command object properties pointer
, @ADOcommpropVal INT — ADO Command object properties value pointer
, @ADOrs INT — ADO RecordSet object
, @OLEreturn INT — OLE return value
, @src varchar(255) — OLE Error Source
, @desc varchar(255) — OLE Error Description
, @PageSize INT — variable for paging size Setting
, @StatusStr char(255) — variable for current status message for verbose output
SET @PageSize = 1000 — IF not SET LDAP query will return max of 1000 rows

–Create the ADO connection object
IF @Verbose=1
BEGIN
Set @StatusStr = ‘Create ADO connection…’
Print @StatusStr
END
EXEC @OLEreturn = sp_OACreate ‘ADODB.Connection’, @ADOconn OUT
IF @OLEreturn <> 0
BEGIN — Return OLE error
EXEC sp_OAGetErrorInfo @ADOconn , @src OUT, @desc OUT
SELECT Error=CONVERT(varbinary(4),@OLEreturn), Source=@src, Description=@desc
RETURN
END
IF @Verbose=1 Print Space(len(@StatusStr)) + ‘done.’

–SET the provider property to ADsDSOObject to point to Active Directory
IF @Verbose=1
BEGIN
Set @StatusStr = ‘Set ADO connection to use Active Directory driver…’
Print @StatusStr
END
EXEC @OLEreturn = sp_OASETProperty @ADOconn , ‘Provider’, ‘ADsDSOObject’
IF @OLEreturn <> 0
BEGIN — Return OLE error
EXEC sp_OAGetErrorInfo @ADOconn , @src OUT, @desc OUT
SELECT Error=CONVERT(varbinary(4),@OLEreturn), Source=@src, Description=@desc
RETURN
END
IF @Verbose=1 Print Space(len(@StatusStr)) + ‘done.’

–Open the ADO connection
IF @Verbose=1
BEGIN
Set @StatusStr = ‘Open the ADO connection…’
Print @StatusStr
END
EXEC @OLEreturn = sp_OAMethod @ADOconn , ‘Open’
IF @OLEreturn <> 0
BEGIN — Return OLE error
EXEC sp_OAGetErrorInfo @ADOconn , @src OUT, @desc OUT
SELECT Error=CONVERT(varbinary(4),@OLEreturn), Source=@src, Description=@desc
RETURN
END
IF @Verbose=1 Print Space(len(@StatusStr)) + ‘done.’

–Create the ADO command object
IF @Verbose=1
BEGIN
Set @StatusStr = ‘Create ADO command object…’
Print @StatusStr
END
EXEC @OLEreturn = sp_OACreate ‘ADODB.Command’, @ADOcomm OUT
IF @OLEreturn <> 0
BEGIN — Return OLE error
EXEC sp_OAGetErrorInfo @ADOcomm , @src OUT, @desc OUT
SELECT Error=CONVERT(varbinary(4),@OLEreturn), Source=@src, Description=@desc
RETURN
END
IF @Verbose=1 Print Space(len(@StatusStr)) + ‘done.’

–SET the ADO command object to use the connection object created first
IF @Verbose=1
BEGIN
Set @StatusStr = ‘Set ADO command object to use Active Directory connection…’
Print @StatusStr
END
EXEC @OLEreturn = sp_OASETProperty @ADOcomm, ‘ActiveConnection’, ‘Provider=”ADsDSOObject”’
IF @OLEreturn <> 0
BEGIN — Return OLE error
EXEC sp_OAGetErrorInfo @ADOcomm , @src OUT, @desc OUT
SELECT Error=CONVERT(varbinary(4),@OLEreturn), Source=@src, Description=@desc
RETURN
END
IF @Verbose=1 Print Space(len(@StatusStr)) + ‘done.’

–Get a pointer to the properties SET of the ADO Command Object
IF @Verbose=1
BEGIN
Set @StatusStr = ‘Retrieve ADO command properties…’
Print @StatusStr
END
EXEC @OLEreturn = sp_OAGetProperty @ADOcomm, ‘Properties’, @ADOcommprop out
IF @OLEreturn <> 0
BEGIN — Return OLE error
EXEC sp_OAGetErrorInfo @ADOcomm , @src OUT, @desc OUT
SELECT Error=CONVERT(varbinary(4),@OLEreturn), Source=@src, Description=@desc
RETURN
END
IF @Verbose=1 Print Space(len(@StatusStr)) + ‘done.’

–SET the PageSize property
IF @Verbose=1
BEGIN
Set @StatusStr = ‘Set ”PageSize” property…’
Print @StatusStr
END
IF (@PageSize IS NOT null) — If PageSize is SET then SET the value
BEGIN
EXEC @OLEreturn = sp_OAMethod @ADOcommprop, ‘Item’, @ADOcommpropVal out, ‘Page Size’
IF @OLEreturn <> 0
BEGIN — Return OLE error
EXEC sp_OAGetErrorInfo @ADOcommprop , @src OUT, @desc OUT
SELECT Error=CONVERT(varbinary(4),@OLEreturn), Source=@src, Description=@desc
RETURN
END
EXEC @OLEreturn = sp_OASETProperty @ADOcommpropVal, ‘Value’,’1000′
IF @OLEreturn <> 0
BEGIN — Return OLE error
EXEC sp_OAGetErrorInfo @ADOcommpropVal , @src OUT, @desc OUT
SELECT Error=CONVERT(varbinary(4),@OLEreturn), Source=@src, Description=@desc
RETURN
END
END
IF @Verbose=1 Print Space(len(@StatusStr)) + ‘done.’

–SET the SearchScope property to ADS_SCOPE_SUBTREE to search the entire subtree
IF @Verbose=1
BEGIN
Set @StatusStr = ‘Set ”SearchScope” property…’
Print @StatusStr
END
BEGIN
EXEC @OLEreturn = sp_OAMethod @ADOcommprop, ‘Item’, @ADOcommpropVal out, ‘SearchScope’
IF @OLEreturn <> 0
BEGIN — Return OLE error
EXEC sp_OAGetErrorInfo @ADOcommprop , @src OUT, @desc OUT
SELECT Error=CONVERT(varbinary(4),@OLEreturn), Source=@src, Description=@desc
RETURN
END
EXEC @OLEreturn = sp_OASETProperty @ADOcommpropVal, ‘Value’,’2′ –ADS_SCOPE_SUBTREE
IF @OLEreturn <> 0
BEGIN — Return OLE error
EXEC sp_OAGetErrorInfo @ADOcommpropVal , @src OUT, @desc OUT
SELECT Error=CONVERT(varbinary(4),@OLEreturn), Source=@src, Description=@desc
RETURN
END
END
IF @Verbose=1 Print Space(len(@StatusStr)) + ‘done.’

–SET the Asynchronous property to True
IF @Verbose=1
BEGIN
Set @StatusStr = ‘Set ”Asynchronous” property…’
Print @StatusStr
END
BEGIN
EXEC @OLEreturn = sp_OAMethod @ADOcommprop, ‘Item’, @ADOcommpropVal out, ‘Asynchronous’
IF @OLEreturn <> 0
BEGIN — Return OLE error
EXEC sp_OAGetErrorInfo @ADOcommprop , @src OUT, @desc OUT
SELECT Error=CONVERT(varbinary(4),@OLEreturn), Source=@src, Description=@desc
RETURN
END
EXEC @OLEreturn = sp_OASETProperty @ADOcommpropVal, ‘Value’,True
IF @OLEreturn <> 0
BEGIN — Return OLE error
EXEC sp_OAGetErrorInfo @ADOcommpropVal , @src OUT, @desc OUT
SELECT Error=CONVERT(varbinary(4),@OLEreturn), Source=@src, Description=@desc
RETURN
END
END
IF @Verbose=1 Print Space(len(@StatusStr)) + ‘done.’

–Create the ADO Recordset to hold the results of the LDAP query
IF @Verbose=1
BEGIN
Set @StatusStr = ‘Create the temporary ADO recordset for query output…’
Print @StatusStr
END
EXEC @OLEreturn = sp_OACreate ‘ADODB.RecordSET’,@ADOrs out
IF @OLEreturn <> 0
BEGIN — Return OLE error
EXEC sp_OAGetErrorInfo @ADOrs , @src OUT, @desc OUT
SELECT Error=CONVERT(varbinary(4),@OLEreturn), Source=@src, Description=@desc
RETURN
END
IF @Verbose=1 Print Space(len(@StatusStr)) + ‘done.’

–Pass the LDAP query to the ADO command object
IF @Verbose=1
BEGIN
Set @StatusStr = ‘Input the LDAP query…’
Print @StatusStr
END
EXEC @OLEreturn = sp_OASETProperty @ADOcomm, ‘CommandText’, @LDAP_Query
IF @OLEreturn <> 0
BEGIN — Return OLE error
EXEC sp_OAGetErrorInfo @ADOcomm , @src OUT, @desc OUT
SELECT Error=CONVERT(varbinary(4),@OLEreturn), Source=@src, Description=@desc
RETURN
END
IF @Verbose=1 Print Space(len(@StatusStr)) + ‘done.’

–Run the LDAP query and output the results to the ADO Recordset
IF @Verbose=1
BEGIN
Set @StatusStr = ‘Execute the LDAP query…’
Print @StatusStr
END
Exec @OLEreturn = sp_OAMethod @ADOcomm, ‘Execute’ ,@ADOrs OUT
IF @OLEreturn <> 0
BEGIN — Return OLE error
print ‘Error in Execute clause of SP_OAMethod’
EXEC sp_OAGetErrorInfo @ADOcomm , @src OUT, @desc OUT
SELECT Error=CONVERT(varbinary(4),@OLEreturn), Source=@src, Description=@desc
RETURN
END

–Return the rows found
IF @Verbose=1
BEGIN
Set @StatusStr = ‘Retrieve the LDAP query results…’
Print @StatusStr
END

DECLARE @pwdlastset varchar(8)

EXEC @OLEreturn = sp_OAMethod @ADOrs, ‘getrows’
IF @OLEreturn <> 0
BEGIN — Return OLE error
Print ‘Error in Getstring of getproperty’
EXEC sp_OAGetErrorInfo @ADOrs , @src OUT, @desc OUT
SELECT Error=CONVERT(varbinary(4),@OLEreturn), Source=@src, Description=@desc
RETURN
END

IF @Verbose=1 Print Space(len(@StatusStr)) + ‘done.’

 

 

Using we create the above Stored Procedure. We can use the above stored procedure to query AD using the following query

exec spqueryad ‘SELECT networkAddress,OperatingSystemServicePack,OperatingSystem,DNSHostName,CN FROM ”LDAP://DC=CONTOSO, DC=com”WHERE objectClass=”computer”’,1

You can substitute your own query while calling the above stored procedure and it should return the desired output. However if try to query pwdlastset using the above Stored Procedure the above query will fail with following error

exec spqueryad ‘SELECT pwdlastset,networkAddress,OperatingSystemServicePack,OperatingSystem,DNSHostName,CN FROM ”LDAP://DC=CONTOSO, DC=com”WHERE objectClass=”computer”’,1

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

The above error occurs because sql server is unable to represent pwdlastset in the output as it is returned by AD in Integer8Date format which is a 64bit long integer. So we cannot use the above stored procedure when we query from AD filed like pwdlastset which are of Interger8Date format. For all the field the above stored proc works fine

So if your requirement is to query AD with more than 1000 rows and pwdlastset is one of the fields you wish to query from AD please follow my next post which is

http://www.sqlserverfaq.net/2010/09/19/how-to-query-active-directory-to-fetch-more-than-1000-rows-using-sql-serverpart-i/

 

Parikshit Savjani
Premier Field Engineer,Microsoft Services