After all the reading and research I've done, this seems like the most logical place to post this question:
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')
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".
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)
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!
'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