I was still getting errors and had exhausted all the options I could find for a linked server login. I decided to delete it:


sp_droplinkedsrvlogin'ADSI', NULL;


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

DECLARE@serviceaccount NVARCHAR(100)

EXECUTExp_instance_regread'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\Services\MSSQLSERVER', 'ObjectName',

@ServiceAccount OUTPUT, 'no_output'

SELECT@ServiceAccount;


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

EXECsys.xp_logininfo@acctname = 'kirsten'


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:


SELECTSAMAccountName

FROMOPENROWSET('ADSDSOObject', 'adsdatasource', '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:


EXEC sp_addlinkedsrvlogin 'ADSI', 'True';


To verify the login actually self-maps:

SELECT uses_self_credential FROM sys.linked_loginsASl,sys.servers AS s

WHERE s.server_id=l.server_id AND s.name='ADSI';


The image below shows the security and login settings as viewed from Object Explorer. Second, I admit that after several hours of toying with various SELECT queries I caved and tried all four login options shown in the UI (to no avail!).

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:

Using a Linked Server to Query Active Directory​


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.


sp_configure'show advanced options', 1-- Changing to 1 turns it on

GO

RECONFIGURE WITH OVERRIDE

GO

sp_configure 'Ad Hoc Distributed Queries', 1

GO

RECONFIGURE WITH OVERRIDE

GO


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.


EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services Interfaces', 'ADSDSOObject', 'adsdatasource'

GO


To verify the linked server was successfully created you can query sys.linked_servers (2008) or sys.servers (2012) or browse in Object Explorer:











kSQL