• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 9482
  • Last Modified:

Linked Server to Active Directory in SQL Server 2005

I have been trying to connect SQL Server 2005 via Linked Server connection to Active Directory and keep getting this error:

Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT personalTitle, manager, name, employeeid, distinguishedName
      FROM 'LDAP://DC=mydomain/DC=net'
      WHERE objectClass = 'user' AND objectCategory = 'Person' " for execution against OLE DB provider "ADsDSOObject" for linked server "ADSI".

I have the linked server connection and it tests out great.  I have also made sure the service SQL Server (MSSQLSERVER) is running on an account within Active Directory which has admin access.  I have also added that user into the linked server properties as a user who is impersonated.  The only thing I have not yet done is added the same login to the other services which have MSSQLSERVER after them.  For instance SQL Server Agent, SQL Server Analysis Services, SQL Server FullText Search, etc.  

I have been trying to find a resolution for this for the past week and a half and it appears Microsoft does not have a good fix for it.  Any help would be great.
0
csg_int_it
Asked:
csg_int_it
  • 7
  • 7
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you show how you actually run the query?

should be something like this:

SELECT * FROM OPENQUERY ( linked_server , '
SELECT personalTitle, manager, name, employeeid, distinguishedName 
      FROM ''LDAP://DC=mydomain/DC=net''
      WHERE objectClass = ''user'' AND objectCategory = ''Person''
 ') ldap

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
actually, you used "/" instead of ",":


SELECT * FROM OPENQUERY ( linked_server , '
SELECT personalTitle, manager, name, employeeid, distinguishedName 
      FROM ''LDAP://DC=mydomain,DC=net''
      WHERE objectClass = ''user'' AND objectCategory = ''Person''
 ') ldap

Open in new window

0
 
csg_int_itAuthor Commented:
Here is my exact query:

SELECT * FROM OPENQUERY( ADSI,
   'SELECT * FROM ''LDAP://servername/OU=Sales Reporting,OU=Test Users,DC=mydomain,DC=net''
   WHERE objectCategory = ''Group'' AND
      objectClass = ''contact''')

The other query was an old one I realized I had the syntax incorrect.  From what I understand the sequence after the statement LDAP should be the server AD is on, then the file structure you want to follow (in my case Sales Reporting/Test users), followed by the domain attributes.  
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>From what I understand the sequence after the statement LDAP should be the server AD is on
no, as that is already defined in the linked server specs...
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
hence:

SELECT * FROM OPENQUERY( ADSI,
   'SELECT * FROM ''LDAP://OU=Sales Reporting,OU=Test Users,DC=mydomain,DC=net''
   WHERE objectCategory = ''Group'' AND objectClass = ''contact''')

Open in new window

0
 
csg_int_itAuthor Commented:
Ok them maybe I have my linked server set up incorrectly.  Here is the statement I used to create my linked server:

EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services',
'ADSDSOObject', 'server.domain.net'
GO
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
not sure... maybe without the domain information?

EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services', 'ADSDSOObject', 'server'
0
 
csg_int_itAuthor Commented:
Tried this also and get the same error.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
then, I don't see what the problem is...
have you already tried the different newsgroups about this problem?


0
 
csg_int_itAuthor Commented:
I have spent the last week searching MSDN, SQL and other forums for the answer and still no solution.  
0
 
csg_int_itAuthor Commented:
When I login to SSMS do I have to use a Windows account or will any sysadmin login within SSMS work to run this query against?  
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>When I login to SSMS do I have to use a Windows account or will any sysadmin login within SSMS work to run this query against?  
that depends actually what security settings you have defined on the linked server...
you might try to connect with a windows account, but I doubt that will change anything, as by the error it is not a permission error, but rather a kind of syntax error...
0
 
csg_int_itAuthor Commented:
Ok, from some of the other items I have read they stated this error is due to permissions.  So if this is a syntax error is there something I am missing from the query above?
0
 
csg_int_itAuthor Commented:
I found a website which generates a stored procedure which queries active directory.  I have run the procedure with great succes and hope this works as well for other as it has for me.  I did not have to change any of the stored procedure code, just in the EXEC statement.

There is one thing you may have to do in order for this stored procedure to execute properly:
Turn on a feature called OLE Automation in the SQL Server 2005 Surface Area Configuration tool.

Here is the site and the code:
http://www.tek-tips.com/viewthread.cfm?qid=1273100&page=1

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 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.'
go

Open in new window

0
 
daviesgroupCommented:
Just to add a bit to this, how can I only return the 'ACTIVE' accounts from AD via SQL script.
This is the script I'm using
SELECT *
FROM OPENQUERY([ADSI], '
 select userAccountControl, distinguishedName, givenName, sn
 from ''LDAP://DC=domain-name,DC=local''  --Exchange the domain-name to your own domain name
 where objectCategory = ''Person''
 and objectClass = ''user''
 order by sn
 ')
WHERE userAccountControl & 2 = 0

Instead of filtering the userAccountControl bitwise AND operation filter, I would like it to appear within the OPENQUERY string between the AND objectClass = ''user'' and ORDER clause.

Any ideas?  Many thanks
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now