How can i query Active Directory from my SQL Server

[smichaels1234]

This thread has been posted before on here but I am a little lost. I can query my results just fine but what I really wanna do is within the stored procedure I wanna capture my results in a table. So, the big thing is inserting it into a table. How do I do this?

Here is my stored procedure:

use master
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spQueryAD]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spQueryAD]
go
create procedure 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 userAccountControl, givenName, sn, mail FROM ''''LDAP://something.com'''' WHERE objectCategory=''''person'''' and objectclass=''''user'''' and userAccountControl <> 546 and userAccountControl <> 514 and userAccountControl <> 66050 ORDER BY mail'', 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
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.'

--Return the rows found
IF @Verbose=1
BEGIN
Set @StatusStr = 'Retrieve the LDAP query results...'
Print @StatusStr
END

--INSERT INTO Active_Directory_Email_Addresses (sn, givenName, mail)
--VALUES ()

EXEC @OLEreturn = sp_OAGetproperty @ADOrs, 'getrows'
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.'
go

This stored procedure is the spQueryAD that allows more than 1000 records. I just wanna capture my results in a table. Please help!!!!!!!!!!

 

[VDavid]

Hi,

I'm going to guess that the stored procedure returns a recordset in the query analyzer. If that's true you can use

insert into [YOURTABLE]
EXEC spQueryAD @LDAP_Query, @Verbose

Hope this helps.
David