Solved

Linked Server to Active Directory in SQL Server 2005

Posted on 2007-12-03
15
9,450 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 142

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 142

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
 
LVL 142

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 142

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 142

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 142

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 142

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

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Synchronize a new Active Directory domain with an existing Office 365 tenant
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…
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 …

759 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now