locked
Sync AD user credentials with a SQL database RRS feed

  • Question

  • Hi folks!

    I need some help to how Sync the user and password from my Active Directory, to a SQL Database.

    Actualy, my enviroment have a database with users and password added, my custom applications uses it like a passport, but now I want to use Active Directory to control these users, but I can't use windows authentication in my old apps. I was reading about Forefront Identity Manager to do this, but I need a free solution.
    The Sharepoint database sync user credentials with AD? Any ideas how I can do this?

    Thanks in advance!


    MCTS Exchange 2010. @pedrongjr


    • Edited by Pedro Nunes Friday, March 14, 2014 5:02 PM
    • Moved by Sofiya Li Monday, March 17, 2014 9:56 AM the better forums
    Friday, March 14, 2014 4:55 PM

Answers

  • Looks like you need a linked SERVER to AD

    create table #t (email varchar(100),sAMAccountName varchar(100),EmployeeID varchar(100))
    insert into  #t Exec master..spQueryAD 'SELECT EmployeeID, SamAccountName, mail
     FROM ''LDAP://dc=companyname,dc=com'' WHERE objectCategory=''person'' and objectclass=''user''', 0

    USE [master]
    GO
    /****** Object:  StoredProcedure [dbo].[spQueryAD]    Script Date: 17/03/2014 13:56:45 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER 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
              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
    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.'



    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    • Edited by Uri DimantMVP Monday, March 17, 2014 11:56 AM
    • Marked as answer by Pedro Nunes Monday, March 17, 2014 6:55 PM
    Monday, March 17, 2014 11:55 AM

All replies

  • Looks like you need a linked SERVER to AD

    create table #t (email varchar(100),sAMAccountName varchar(100),EmployeeID varchar(100))
    insert into  #t Exec master..spQueryAD 'SELECT EmployeeID, SamAccountName, mail
     FROM ''LDAP://dc=companyname,dc=com'' WHERE objectCategory=''person'' and objectclass=''user''', 0

    USE [master]
    GO
    /****** Object:  StoredProcedure [dbo].[spQueryAD]    Script Date: 17/03/2014 13:56:45 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER 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
              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
    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.'



    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    • Edited by Uri DimantMVP Monday, March 17, 2014 11:56 AM
    • Marked as answer by Pedro Nunes Monday, March 17, 2014 6:55 PM
    Monday, March 17, 2014 11:55 AM
  • It looks exactly what I was looking for! Thanks a lot Uri!!!

    MCTS Exchange 2010. @pedrongjr


    Monday, March 17, 2014 6:56 PM
  • Hi Uri,

    I understand this post is about a year old, but can you please help me? I am able to create the procedure but when I try to execute it, i am getting the following output. I am not able to see any results from the AD.

    exec spqueryad 'SELECT EmployeeID, SamAccountName, mail
    from "LDAP://XXXX/DC=XXXX,DC=XXXX,DC=XXXX,DC=XXXX" WHERE objectCategory="person"',0

    0x80040E14 Provider  One or more errors occurred during processing of command.

    (1 row(s) affected)

    Can you please help.

    Wednesday, June 17, 2015 2:52 PM