Querying Active Directory via a Linked Server using ADSI: SQL Server 2008 R2

How to quickly set up a linked server for the purposes of querying Active directory:

sp_configure 'show advanced options', 1
reconfigure
GO

sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure
GO

sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADsDSOObject', 'adsdatasource'
GO 

Next check the properties of the newly created Linked Server. By default, the new Linked Server Definition will connect to Active Directory using the security context of the current user.

If you are using SQL Authentication, it may be necessary to hard-code a suitable account here that has access to the AD.

Here is a sample query to select some users from the AD. Be sure to fill in your domain name in the LDAP section of the query string.

SELECT TOP 50
 samAccountName AS AccountName,
 displayName AS DisplayName
FROM OPENQUERY(ADSI, 
    'SELECT samAccountName, 
    displayName 
    FROM ''LDAP://DC=au,DC=xyz,DC=local'' 
    WHERE objectClass=''user''') 

Installing SQL 2008 R2 – in just 25 Screenshots

I’ve been playing with SQL 2008 R2 for a while now, and just recently I installed the RTM version for a customer. The following screenshots give an indication of what is involved. There are a few more steps required than for SQL 2005 or 2008, but the process is very intuitive. Kudos to the guys who wrote the installer!

Note that this was for a dev environment, so some shortcuts were taken! (File placement, service accounts, etc….)