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
Updated Code From Jonathan Mears
===========================
ALTER procedure [dbo].[pr_QueryAD] ( @LDAP_Query varchar(255)=”, @Verbose bit=0, @Error_Message varchar(250)=null output ) as begin /* from https://sqlserverfaq.net/2010/09/20/how-to-query-active-directory-to-fetch-more-than-1000-rows-using-sql-serverpart-i-2/ */ set @Error_Message = null; --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 1 end --declare variables declare @i int; –-Misc declare @ADOconn INT — ADO Connection object declare @ADOcomm INT — ADO Command object declare @ADOcommprop INT — ADO Command object properties pointer declare @ADOcommpropVal INT — ADO Command object properties value pointer declare @ADOrs INT — ADO RecordSet object declare @OLEreturn INT — OLE return value declare @src varchar(255) — OLE Error Source declare @desc varchar(255) — OLE Error Description declare @PageSize varchar(6) — variable for paging size Setting declare @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; page size is the amount of data returned per “PAGE”, not the total number of 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 goto OA_ERROR 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 goto OA_ERROR 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 goto OA_ERROR 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 goto OA_ERROR 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 goto OA_ERROR 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 goto OA_ERROR 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 goto OA_ERROR END EXEC @OLEreturn = sp_OASETProperty @ADOcommpropVal, ‘Value’,@PageSize IF @OLEreturn 0 BEGIN — Return OLE error EXEC sp_OAGetErrorInfo @ADOcommpropVal , @src OUT, @desc OUT goto OA_ERROR END exec @OLEreturn = sp_OADestroy @ADOcommpropVal; 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 goto OA_ERROR 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 goto OA_ERROR END exec @OLEreturn = sp_OADestroy @ADOcommpropVal; 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 goto OA_ERROR END EXEC @OLEreturn = sp_OASETProperty @ADOcommpropVal, ‘Value’,True IF @OLEreturn 0 BEGIN — Return OLE error EXEC sp_OAGetErrorInfo @ADOcommpropVal , @src OUT, @desc OUT goto OA_ERROR END exec @OLEreturn = sp_OADestroy @ADOcommpropVal; END IF @Verbose=1 Print Space(len(@StatusStr)) + ‘done.’ –Create the ADO Recordset to hold the results of the LDAP query –This was in orignal code but other Web examples indicate that it is not required; Execute will do this; –note that in orignal code @ADOrs is used as an output object twice /* 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 goto OA_ERROR 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 goto OA_ERROR 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 IF @Verbose=1 print ‘Error in Execute clause of SP_OAMethod’ EXEC sp_OAGetErrorInfo @ADOcomm , @src OUT, @desc OUT goto OA_ERROR 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 IF @Verbose = 1 Print ‘Error in Getstring of getproperty’ EXEC sp_OAGetErrorInfo @ADOrs , @src OUT, @desc OUT goto OA_ERROR END IF @Verbose=1 Print Space(len(@StatusStr)) + ‘done.’ DONE: EXEC @i = sp_OAMethod @ADOrs, ‘Close’ if @Verbose = 1 print ‘@ADOrs close result = ‘ + cast(@i as varchar); EXEC @i = sp_OAMethod @ADOconn, ‘Close’ if @Verbose = 1 print ‘@ADOconn close result = ‘ + cast(@i as varchar); exec @i = sp_OADestroy @ADOcommprop; if @Verbose = 1 print ‘@ADOcommprop destroy result = ‘ + cast(@i as varchar); exec @i = sp_OADestroy @ADOrs; if @Verbose = 1 print ‘@ADOrs destroy result = ‘ + cast(@i as varchar); exec @i = sp_OADestroy @ADOcomm; if @Verbose = 1 print ‘@ADOcomm destroy result = ‘ + cast(@i as varchar); exec @i = sp_OADestroy @ADOconn; if @Verbose = 1 print ‘@ADOconn destroy result = ‘ + cast(@i as varchar); return 0 OA_ERROR: SET @Error_Message = ‘Error=’ + isnull(cast(@OLEreturn as varchar),’?') + ‘/Src=’ + isnull(@src,’?') + ‘/Description=’ + isnull(@desc,’?'); if @Verbose=1 Print @Error_Message; EXEC @i = sp_OAMethod @ADOrs, ‘Close’ if @Verbose = 1 print ‘@ADOrs close result = ‘ + cast(@i as varchar); EXEC @i = sp_OAMethod @ADOconn, ‘Close’ if @Verbose = 1 print ‘@ADOconn close result = ‘ + cast(@i as varchar); exec @i = sp_OADestroy @ADOcommprop; if @Verbose = 1 print ‘@ADOcommprop destroy result = ‘ + cast(@i as varchar); exec @i = sp_OADestroy @ADOrs; if @Verbose = 1 print ‘@ADOrs destroy result = ‘ + cast(@i as varchar); exec @i = sp_OADestroy @ADOcomm; if @Verbose = 1 print ‘@ADOcomm destroy result = ‘ + cast(@i as varchar); exec @i = sp_OADestroy @ADOconn; if @Verbose = 1 print ‘@ADOconn destroy result = ‘ + cast(@i as varchar); return @OLEreturn
END;
Parikshit Savjani
Premier Field Engineer,Microsoft Services
Hello excellent info, i have a question, Is possible to create users in active directory since the information of a table’s database mssql?
Hi,
This sp looks great, and I was attempting to get it to work. The one problem I noticed is that it looks like the following line is incomplete from above:
EXEC @OLEreturn = sp_OAMethod @ADOrs, ‘getrows’, —-
I’d love to give this sp a try, but I can’t seem to figure what the missing code is for that line. Could you help?
I have rectified this…you should be able to use it now without errors
Parikshit,
I really appreciated find this code example. I started running it with many calls within a single SQL command execution and started getting failures that I tied back to too many OLE objects. I have added “garbage collection” to it and the errors are not gone. There were two objects obtained using @ADOrs; it seems to work fine without the sp_OACreate ‘ADODB.RecordSET’
ALTER procedure [dbo].[pr_QueryAD] (
@LDAP_Query varchar(255)=”,
@Verbose bit=0,
@Error_Message varchar(250)=null output
)
as
begin
/*
from
https://sqlserverfaq.net/2010/09/20/how-to-query-active-directory-to-fetch-more-than-1000-rows-using-sql-serverpart-i-2/
*/
set @Error_Message = null;
–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 1
end
–declare variables
declare @i int; –Misc
declare @ADOconn INT — ADO Connection object
declare @ADOcomm INT — ADO Command object
declare @ADOcommprop INT — ADO Command object properties pointer
declare @ADOcommpropVal INT — ADO Command object properties value pointer
declare @ADOrs INT — ADO RecordSet object
declare @OLEreturn INT — OLE return value
declare @src varchar(255) — OLE Error Source
declare @desc varchar(255) — OLE Error Description
declare @PageSize varchar(6) — variable for paging size Setting
declare @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; page size is the amount of data returned per “PAGE”, not the total number of 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
goto OA_ERROR
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
goto OA_ERROR
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
goto OA_ERROR
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
goto OA_ERROR
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
goto OA_ERROR
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
goto OA_ERROR
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
goto OA_ERROR
END
EXEC @OLEreturn = sp_OASETProperty @ADOcommpropVal, ‘Value’,@PageSize
IF @OLEreturn 0
BEGIN — Return OLE error
EXEC sp_OAGetErrorInfo @ADOcommpropVal , @src OUT, @desc OUT
goto OA_ERROR
END
exec @OLEreturn = sp_OADestroy @ADOcommpropVal;
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
goto OA_ERROR
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
goto OA_ERROR
END
exec @OLEreturn = sp_OADestroy @ADOcommpropVal;
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
goto OA_ERROR
END
EXEC @OLEreturn = sp_OASETProperty @ADOcommpropVal, ‘Value’,True
IF @OLEreturn 0
BEGIN — Return OLE error
EXEC sp_OAGetErrorInfo @ADOcommpropVal , @src OUT, @desc OUT
goto OA_ERROR
END
exec @OLEreturn = sp_OADestroy @ADOcommpropVal;
END
IF @Verbose=1 Print Space(len(@StatusStr)) + ‘done.’
–Create the ADO Recordset to hold the results of the LDAP query
–This was in orignal code but other Web examples indicate that it is not required; Execute will do this;
–note that in orignal code @ADOrs is used as an output object twice
/*
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
goto OA_ERROR
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
goto OA_ERROR
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
IF @Verbose=1 print ‘Error in Execute clause of SP_OAMethod’
EXEC sp_OAGetErrorInfo @ADOcomm , @src OUT, @desc OUT
goto OA_ERROR
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
IF @Verbose = 1 Print ‘Error in Getstring of getproperty’
EXEC sp_OAGetErrorInfo @ADOrs , @src OUT, @desc OUT
goto OA_ERROR
END
IF @Verbose=1 Print Space(len(@StatusStr)) + ‘done.’
DONE:
EXEC @i = sp_OAMethod @ADOrs, ‘Close’
if @Verbose = 1 print ‘@ADOrs close result = ‘ + cast(@i as varchar);
EXEC @i = sp_OAMethod @ADOconn, ‘Close’
if @Verbose = 1 print ‘@ADOconn close result = ‘ + cast(@i as varchar);
exec @i = sp_OADestroy @ADOcommprop;
if @Verbose = 1 print ‘@ADOcommprop destroy result = ‘ + cast(@i as varchar);
exec @i = sp_OADestroy @ADOrs;
if @Verbose = 1 print ‘@ADOrs destroy result = ‘ + cast(@i as varchar);
exec @i = sp_OADestroy @ADOcomm;
if @Verbose = 1 print ‘@ADOcomm destroy result = ‘ + cast(@i as varchar);
exec @i = sp_OADestroy @ADOconn;
if @Verbose = 1 print ‘@ADOconn destroy result = ‘ + cast(@i as varchar);
return 0
OA_ERROR:
SET @Error_Message = ‘Error=’ + isnull(cast(@OLEreturn as varchar),’?’) +
‘/Src=’ + isnull(@src,’?’) +
‘/Description=’ + isnull(@desc,’?’);
if @Verbose=1 Print @Error_Message;
EXEC @i = sp_OAMethod @ADOrs, ‘Close’
if @Verbose = 1 print ‘@ADOrs close result = ‘ + cast(@i as varchar);
EXEC @i = sp_OAMethod @ADOconn, ‘Close’
if @Verbose = 1 print ‘@ADOconn close result = ‘ + cast(@i as varchar);
exec @i = sp_OADestroy @ADOcommprop;
if @Verbose = 1 print ‘@ADOcommprop destroy result = ‘ + cast(@i as varchar);
exec @i = sp_OADestroy @ADOrs;
if @Verbose = 1 print ‘@ADOrs destroy result = ‘ + cast(@i as varchar);
exec @i = sp_OADestroy @ADOcomm;
if @Verbose = 1 print ‘@ADOcomm destroy result = ‘ + cast(@i as varchar);
exec @i = sp_OADestroy @ADOconn;
if @Verbose = 1 print ‘@ADOconn destroy result = ‘ + cast(@i as varchar);
return @OLEreturn
END;
Parikshit,
…… and the errors are NOW gone…..
Thanks Jonathan, for your efforts to rectify the code…I will update the code in the blog 🙂
I get an error message
“The identifier ‘Select samAccount name FROM “LDAP://……… is too long. Maxium length is 128.
Any ideas on how to get past this?
I get the error “Error=-2147211470/Src=ODSOLE Extended Procedure/Description=Output values of type Object are not allowed in result sets.” when I include the field accountexpires in my query.
Any ideas on what I can do to get past this?
So…is it just me or are all the quotes replaced with “smart quotes” and the double hyphen replaced with the single large hyphen? It’s like the code was auto-corrected with word. Because of this it’s not runnable by copying and pasting as is. Is there a link somewhere to the un-smartquoted SQL code?
Can someone provide the “runnable” code? There is something wrong with the quotes in stored procedure.
thanks
When i tried to retrived objectCategory = group and this group is memberof another group, no data was retrieved
Query like:
‘SELECT samAccountName FROM ”LDAP://DomainName” WHERE objectCategory = ”Group” AND memberof=”CN=ParentGroup Office,OU=X,OU=Y,OU=Z,DC=A,DC=B”’
I was able to create the SPs with no issue. When I execute the sp, (I used the proper LDAP string)
“exec spqueryad ‘SELECT smaaccountname FROM ”LDAP://DC=MyDomain, DC=COM” WHERE objectClass = ”User”’, 1
I get the result, “0x80004005 Provider Unspecified error” and the messages:
Create ADO connection… done.
Set ADO connection to use Active Directory driver… done.
Open the ADO connection… done.
Create ADO command object… done.
Set ADO command object to use Active Directory connection… done.
Retrieve ADO command properties… done.
Set ‘PageSize’ property… done.
Set ‘SearchScope’ property… done.
Set ‘Asynchronous’ property… done.
Create the temporary ADO recordset for query output… done.
Input the LDAP query… done.
Execute the LDAP query… Error in Execute clause of SP_OAMethod
(1 row(s) affected)
Any idea where I am going wrong? Some searching shows the query may be incorrect. I have used the same query, limited to 100 rows, through a linked server, and it worked.
Jonathan and Parikshit, thank you both very much. This has been tremendously helpful. I noticed that the resulting column names are “Column0”, “Column1”, “Column2” and so on, and moreover they appear to be in reverse order as compared to the select list supplied in the LDAP query.
Do you have any guidance on getting the column names (or field names, as it were) into the returned recordset? If not, can we be confident of the consistency of that reversed order of columns as queried?