Solved

Linked Server to Active Directory in SQL Server 2005

Posted on 2007-12-03
15
9,461 Views
Last Modified: 2012-06-21
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
Comment
Question by:csg_int_it
  • 7
  • 7
15 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 20395667
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20395676
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
 

Author Comment

by:csg_int_it
ID: 20395852
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
Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20395906
>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
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 500 total points
ID: 20395917
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
 

Author Comment

by:csg_int_it
ID: 20395966
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20396026
not sure... maybe without the domain information?

EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services', 'ADSDSOObject', 'server'
0
 

Author Comment

by:csg_int_it
ID: 20396660
Tried this also and get the same error.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20397046
then, I don't see what the problem is...
have you already tried the different newsgroups about this problem?


0
 

Author Comment

by:csg_int_it
ID: 20397155
I have spent the last week searching MSDN, SQL and other forums for the answer and still no solution.  
0
 

Author Comment

by:csg_int_it
ID: 20397343
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20397472
>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
 

Author Comment

by:csg_int_it
ID: 20397507
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
 

Author Comment

by:csg_int_it
ID: 20401033
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
 

Expert Comment

by:daviesgroup
ID: 33220973
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
This article describes my battle tested process for setting up delegation. I use this process anywhere that I need to setup delegation. In the article I will show how it applies to Active Directory
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles from a Windows Server 2008 domain controller to a Windows Server 2012 domain controlle…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question