Why can I query Active Directory using VBA, but not a Linked Server in SQL Management Studio 2012?

First, I have been able to do this before, but many moons ago with SQL Server 2005.

Here is the query I am attempting:

SELECT * FROM OpenQuery(
ADSI,
'SELECT displayName, title, department, employeeID, userAccountControl
FROM ''LDAP://dc=testdomain''
WHERE objectCategory = ''Person'' AND
      objectClass = ''user'' AND
      userAccountControl=512')

Open in new window


Here is the error I'm getting:

Msg 7321, Level 16, State 2, Line 1
An error occured while preparing the query "SELECT displayName, title, department, employeeID, userAccountControl
            FROM 'LDAP://dc=testdomain'
            WHERE objectCategory = 'Person' AND
                  objectClass = 'user' AND
            userAccountControl=512" for execution against OLE DB provider "ADsDSOObject" for linked server "ADSI".

Open in new window

The problem with this error message is that it's very generic and seemingly doesn't yield anything useful. Everything I've read appears to be a permission issue or the syntax of the query, and I assume it's meant in the context to my SQL instance login and how the security is set up in the Linked Server. The VBA code below works and is using a query that's very similar and not even the simplest of queries have worked in the Linked Server. I also have access to the Active Directory I am trying to link to and this is proven by the snippet of VBA code I have at the bottom (only there for reference). Thing is, I believe I have all the right privileges in place to for this to be working.

However, a lot of what's being suggested is on different sites involves doing modifications to the SQL instance that are not readily obvious of what the impacts are long term as this server is still being built. I have temporary elevated privileges to build it out.

Here's the details and parameters in which I've set up the linked server:
SQL Server 11.0.5058
Linked Server: ADSI
Provider: OLE DB Provider for Microsoft Directory Services
Product name: Active Directory Services 2.5
Data source: adsdatasource
Provider string: ADsDSOObject

Be made using the login's current security context

Ole DB Provider  Options:
Allow in process

However, the only thing I've found that might set myself apart from all the other blogs about this error is when I attempt to drill down to the linked servers tables and view (Server Objects > Linked Servers > ADSI > Catalogs > default > Tables). Once I click to expand the Tables level I get the following error:

Failed to retreive data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

Additional information:
    An exception occured while executing a Transact-SQL statement or batch.
    (Microsoft.SqlServer.ConnectionInfo)
        Cannot obtain the required interface ("IID_IDBSchemaRowset") from OLE DB provider "ADsDSOObject"
        for linked server "ADSI". (Microsoft SQL Server, Error: 7301)

Open in new window


This IID_IDBSchemaRowset seems to be my only lead, but that looks to be a deep and dark rabbit hole to down and not sure if that's where I need to go. Help!


For Reference

'References: Microsoft ActiveX Data Objects 2.8 Library
Public Sub testADSI()
    On Error Resume Next

    Dim cn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim rs as ADODB.Recordset
    Dim MySql as String
    Dim n as Integer

    Set cn = New ADODB.Connection
    Set cmd = New ADODB.Command
    Set rs = New ADODB.Recordset

    cn.Provider = "ADsDSOObject"
    cn.Open "Active Directory Provider"

    Set cmd.ActiveConnection = cn

    cmd.Properties("Page Size")= 1000

    MySql = "SELECT displayName, title, department, employeedID, userAccountControl " & _
            "FROM 'LDAP://dc=testdomain' " _ &
            "WHERE objectCategory = 'Person' AND " & _
                  "objectClass = 'user' AND " & _
                  "userAccountControl=512" 

    rs.Open MySql, cn, 1

    If rs.RecordCount > 0 Then
        MsgBox "Sucess! " & rs.RecordCount & " records found!"
    Else
        MsgBox "No records"
    End IF
End Sub

Open in new window

Jorge RiojasAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Is AdHoq queriys allowed?

Turn it on if its not on by executing in a Query window:

sp_configure 'show advanced options', 1
 reconfigure with override
GO
 sp_configure 'Ad Hoc Distributed Queries', 1
 reconfigure
Go

Now try again. I Think this might be your problem.

Regards Marten
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
If this does not do the trick. Check out the account running SQL Server Services.

It needs to be a domain account, otherwise you are not authorized to Query the AD.

Regards Marten
Ryan McCauleyEnterprise Analytics ManagerCommented:
Martenrune is right - your SQL Agent account (running the job) needs to be a domain user to query AD by default. You can modify AD to allow anonymous queries (from users who aren't authenticated), but there are some security risks that go along with that (the linked article begins with the phrase "I strongly recommend against this"!):

http://windowsitpro.com/active-directory/q-how-do-i-enable-anonymous-ldap-binds-windows-server-2008-active-directory-ad

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Active Directory

From novice to tech pro — start learning today.