I was still getting errors and had exhausted all the options I could find for a linked server login. I decided to delete it:
Searching more on Google suggests that perhaps the service account SQL Server is running under does not have permissions to query AD. To dig into this I executed the following:
-- Find the service account running SQL Server
The service account had sufficient permissions. Perhaps it's an issue with my own user account which I'm using to connect with SSMS?
-- Find details about my own user account
This wasn't the issue either, and my query was still broken. Here is an example of a query and error message I was receiving during my testing:
'SELECT SAMAccountName FROM ''LDAP://OU=SQL Developers,DC=kSQL,DC=net'' WHERE samAccountName=''kirsten'' AND objectClass=''User'' AND objectCategory=''Person'''
Msg 7321, Level 16, State 2, Line 1 An error occurred while preparing the query "SELECT SAMAccountName FROM 'LDAP://OU=SQL Developers,DC=kSQL,DC=net' WHERE samAccountName = 'kirsten' AND objectClass='User' AND objectCategory='Person'" for execution against OLE DB provider "ADSDSOObject" for linked server "(null)".
I returned yet again to Google and found this interesting article that outlined the concept of a network double-hop versus single-hop. I realized that the issue was not permissions based, but was likely caused by the hop from my personal PC where I was using SSMS, to SQL Server, and then to Active Directory. The solution? Remote into the server itself, open SSMS, and query Active Directory. I did so, and my query (example above) worked! Note that the query is is surrounded by single quotes and case sensitive: DC=kSQL would work whereas DC=ksql would not.
Another way to test this would be to place the OPENROWSET query code into a stored procedure and run it with a one-time use SQL Job. This would eliminate the step from SSMS to SQL Server and would validate that the query is functional.
Unfortunately I was unable to use the linked server to meet my needs. I needed to return an array of attributes from an object and this is simply not possible.
" ... there is a limitation on querying the Active Directory which is that multivalued properties cannot be returned in the result set to SQL Server. ADSI will read schema information from the LDAP server that defines the structure and syntax of the classes and attributes used by the server. If the attribute that is requested from the LDAP server is defined in the schema as being multi-valued it cannot be returned in an OPENQUERY statement." -- sqlmonster.com
My report needed the list of users from the "memberOf" attribute for a group, but a linked server is only able to return a singular attribute using OPENROWSET or OPENQUERY e.g. samAccountName or Common Name. In the end I solved this problem by using a powershell script to query AD, which is another post entirely.
In summary: creating the linked server was sufficient to query AD with no additional changes to the login needed. The AD objects in OPENROWSET queries are case sensitive, and you should seriously consider installing a LDAP browser to verify object names as well as the accuracy of query results.
Here is another very useful link on this topic.
A bit of research uncovered that any user that exists within AD by definition has permissions to query it, but a Google search confirmed that yes, quite often developers specify a linked server login. I decided to try some options with this route regardless of knowing that it should have worked as-is.
First, I tried explicitly configuring the login to use self-mapping:
Msg 7416, Level 16, State 1, Line 1 Access to the remote server is denied because no login-mapping exists.
Msg 7321, Level 16, State 2, Line 1 An error occurred while preparing the query "SELECT sAMAccountName FROM 'LDAP://OU=Computers,DC=kSQL,DC=net' for execution against OLE DB provider "ADSDSOBject" for linked server "ADSI".
Msg 7321, Level 16, State 2, Line 1 An error occurred while preparing the query "<LDAP://OU=Computers,DC=kSQL,DC=net>;(&(objectCategory=Group));cn" for execution against OLE DB provider "ADSDSOBject" for linked server "ADSI".
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "ADSDSObject" for linked server "ADSI".
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "ADSDSObject" for linked server "(null)".
It should be noted that this was my first time dealing with Active Directory. By this point the esoteric nature of linked servers and virtual directories was problematic; I'm a visual type and do well either seeing what I'm working with or having a concrete analogy as a substitute. One of my coworkers recommended installing a free LDAP Browser. This allowed me to see and click through the hierarchy and objects in AD. You'll need to contact someone who works with the domain controllers in your network to get the correct settings.
By this point I had written and executed several queries against the new linked server, all of which threw various errors hinting that it might be a connection issue, a problem with the login, or my query syntax. Here are some examples:
Recently I built a report that involved inserting Active Directory information to a temp table from within a stored procedure. Creating a linked server, testing connectivity, crafting the actual query, and tracking down the information I needed from Active Directory took quite a it of time; this post documents the steps I took and the issues I encountered. My end goal was to be able to use OPENROWSET or OPENQUERY within my stored procedure.
The first step is is to verify ad hoc distributed queries are enabled.
'show advanced options'
-- Changing to 1 turns it on
RECONFIGURE WITH OVERRIDE
'Ad Hoc Distributed Queries'
RECONFIGURE WITH OVERRIDE
Second, create the linked server (click the link to visit the MSDN website and learn about the arguments passed to sp_addlinkedserver). You can alternately add the linked server using the Object Explorer interface in SSMS, but my personal preference has always been to use code when possible. I've used the following code on both 2008 and 2012.
'Active Directory Services Interfaces'
To verify the linked server was successfully created you can query sys.linked_servers (2008) or sys.servers (2012) or browse in Object Explorer: