?
Solved

Linked Server to Active Directory in SQL Server 2005

Posted on 2007-12-03
15
Medium Priority
?
9,474 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
Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
A bad practice commonly found during an account life cycle is to set its password to an initial, insecure password. The Password Reset Tool was developed to make the password reset process easier and more secure.
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …
Suggested Courses

765 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