none
SQL 2005 LDAP Query Error: Msg 7321, Level 16

    Question

  • Hello I am trying to run a query via tsql against ad. Below is the error I am getting. I have read the http://msdn2.microsoft.com/en-US/library/ms190803.aspx and changed the domain but still having issues. Any help would be appreciated.

     

    EXEC sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces',

    'ADSDSOObject', 'adsdatasource'

    GO

    SELECT *

    FROM OPENQUERY( ADSI,

    'SELECT Name, SN, ST

    FROM ''LDAP://ADSISrv/ OU=Users,DC=XXXXX,DC=LOCAL''

    WHERE objectCategory = ''Person'' AND

    objectClass = ''user''')

    Msg 7321, Level 16, State 2, Line 1

    An error occurred while preparing the query "SELECT Name, SN, ST

    FROM 'LDAP://ADSISrv/ OU=Users,DC=XXXXX,DC=LOCAL'

    WHERE objectCategory = 'Person' AND

    objectClass = 'user'" for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI".

    Friday, August 04, 2006 7:47 PM

Answers

  • I had the same issue like you, if you run in your personal computer is probably that you not experiment the same error but if you move to any other server that need the right permission to any user into the Active Directory.

    first execute your query:

    EXEC sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces',

    'ADSDSOObject'

    , 'adsdatasource'

    GO

    Like daw21 explain before follow the detail steps:

    Second step go to the SqlServer management Studio and edit manually the Linkserver name ADSI:

    go to Server Objects/Linked Servers/  and right click on ADSI  select the option Properties and then go to Security.

    Select the option :         Be made using this security context:

    and there fill the user  and Password about the valid user with permission into Active Directory, not forget before the domain\user.

     

     

     

    • Proposed as answer by G'B Monday, August 08, 2011 9:18 PM
    • Marked as answer by Kalman TothModerator Tuesday, August 16, 2011 10:54 AM
    Wednesday, March 09, 2011 3:11 PM
  • Took me forever to get this working ...

    First you have to make sure that Ad Hoc Distributed Queries is enabled ...

    sp_configure 'Ad Hoc Distributed Queries', 1
    reconfigure
    Second you have to make sure that you specify a security context that has access to AD INCLUDING the DOMAIN
    Third you should put a row limit (e.g. SELECT TOP 100) in to avoid the issue where you hit the 1000 row limit so

    SELECT

     

    TOP 100 *

    FROM

     

    OpenQuery(ADSI, 'SELECT sAMAccountName FROM ''LDAP://DC=core,DC=acllab,DC=net'' WHERE objectCategory=''user'' ')

    Wednesday, March 23, 2011 1:37 PM

All replies

  • Check if the linked server (ADSI) allow remote accessing.
    Friday, August 04, 2006 7:58 PM
  • I got it to work on the local database server. It didn't seem to like the OU. Once I removed it the query completed. However I still get the message if I try and run the view from a remote computer using Mgmt Studio is there something I have to setup on the clients?

    SELECT * FROM vcnet.dbo.viewADContacts--gives errors from workstations but not if run from the server itself

    Server: Msg 7321, Level 16, State 2, Line 1
    An error occurred while preparing the query "SELECT sAMAccountname,givenname,sn,displayname,useraccountcontrol,CN, manager, mobile , mail, objectCategory,
      department,
      telephoneNumber,
      facsimileTelephoneNumber
    FROM 'LDAP://DC=XXXXX,DC=local'
    WHERE objectCategory='person' AND objectClass='user'" for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI".

     

    Friday, August 04, 2006 9:16 PM
  • Did you ever get a resolution to this? I am now having the same issue - query runs fine on the local SQL2k5 server but not on a remote server running Visual Dev.
    Friday, August 18, 2006 11:39 AM
  • I'm having the same issue - query runs in management studio, but not elsewhere.  Any idea why?
    Wednesday, August 23, 2006 10:11 PM
  • The domain name is required in the Security settings for the remote login - I should have known that.  So in the Security settings I selected Be made using this security context, entered Remote login: domain\user; With password: password.

    Thursday, August 24, 2006 6:54 PM
  • Actually, you don't need to run sp_addlinkedserver.

    What you should do is

    1) Make sure your SQL Server Service is running under the account which has access right to Active Directory.

    2) You must log in to Active Directory to run this query.

     

    SELECT * FROM OPENROWSET('ADSDSOObject', 'adsdatasource;',

    'SELECT mail, displayName,givenname,distinguishedName, SAMAccountName, CN

    FROM ''LDAP://ServerName/cn=users, DC=DomainName, DC=com'' ')

    Thursday, February 28, 2008 6:50 AM
  • I had the same issue like you, if you run in your personal computer is probably that you not experiment the same error but if you move to any other server that need the right permission to any user into the Active Directory.

    first execute your query:

    EXEC sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces',

    'ADSDSOObject'

    , 'adsdatasource'

    GO

    Like daw21 explain before follow the detail steps:

    Second step go to the SqlServer management Studio and edit manually the Linkserver name ADSI:

    go to Server Objects/Linked Servers/  and right click on ADSI  select the option Properties and then go to Security.

    Select the option :         Be made using this security context:

    and there fill the user  and Password about the valid user with permission into Active Directory, not forget before the domain\user.

     

     

     

    • Proposed as answer by G'B Monday, August 08, 2011 9:18 PM
    • Marked as answer by Kalman TothModerator Tuesday, August 16, 2011 10:54 AM
    Wednesday, March 09, 2011 3:11 PM
  • Took me forever to get this working ...

    First you have to make sure that Ad Hoc Distributed Queries is enabled ...

    sp_configure 'Ad Hoc Distributed Queries', 1
    reconfigure
    Second you have to make sure that you specify a security context that has access to AD INCLUDING the DOMAIN
    Third you should put a row limit (e.g. SELECT TOP 100) in to avoid the issue where you hit the 1000 row limit so

    SELECT

     

    TOP 100 *

    FROM

     

    OpenQuery(ADSI, 'SELECT sAMAccountName FROM ''LDAP://DC=core,DC=acllab,DC=net'' WHERE objectCategory=''user'' ')

    Wednesday, March 23, 2011 1:37 PM
  • If you excute the query in remote computer, you can try Security for linked Server: right click on the linked Server (ADSI) select properites, select Security tab, choose "Be made using this security context" and enter your remote login and password.
    Tuesday, August 16, 2011 10:32 AM
  • Enabled adhoc distributed query, added security context to ADSI (with domain name). The ADSI connection test is showing as successful but still getting the same error in trying to run the following:

    SELECT

     

     TOP 100 * FROM

    O

    penQuery

    (ADSI, 'SELECT sAMAccountName FROM ''LDAP://OU=XX,OU=XX,DC=XX,DC=XX'' WHERE objectClass=''person'' ')

    Msg 7321, Level 16, State 2, Line 1

    An error occurred while preparing the query "SELECT sAMAccountName FROM 'LDAP://OU=Flight Services,OU=FS21users,DC=emulator,DC=com' WHERE objectClass='person' " for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI".

    Any help will be appreciated, running on SQL server 2008.

     
    Monday, August 29, 2011 4:44 PM