Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Linked Server to Active Directory in SQL Server 2005

Posted on 2007-12-03
15
Medium Priority
?
9,479 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
15 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1500 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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 1500 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
Group policies can be applied selectively to specific devices with the help of groups. Utilising this, it is possible to phase-in group policies, over a period of time, by randomly adding non-members user or computers at a set interval, to a group f…
This tutorial will walk an individual through the process of configuring their Windows Server 2012 domain controller to synchronize its time with a trusted, external resource. Use Google, Bing, or other preferred search engine to locate trusted NTP …
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…
Suggested Courses

618 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