SELECT * FROM OpenQuery( ADSI, 'SELECT displayName, title, department, employeeID, userAccountControl FROM ''LDAP://dc=testdomain'' WHERE objectCategory = ''Person'' AND objectClass = ''user'' AND userAccountControl=512')
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.
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".
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)
'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
From novice to tech pro — start learning today.