Querying Active Directory From SQL Server

A directory lookup looks simple until a report depends on it at six in the morning. Querying active directory from SQL Server is possible, but I decide who owns the lookup before I write the join.

Start With the Directory Question

Before making SQL Server reach into the directory, write down the exact attribute and the business purpose. A request for one account’s display name is different from a nightly inventory of every computer and group. I start by asking whether the application already receives the identity claim it needs. When it does, a second lookup from the database adds latency and another permission boundary without adding truth.

Active Directory is a directory service, not a second relational database. Its object classes, permissions, naming contexts and search limits are part of the answer. A query that returns a few users in a test organizational unit can be incomplete against the full domain. Which attribute would make the result correct, and who will notice if that attribute is empty? Those questions belong in the design before the first SELECT.

What an ADSI Linked Server Actually Does

SQL Server can expose the Active Directory Services Interface OLE DB provider through a linked server using ADSDSOObject. A directory query is then sent through OPENQUERY. That sounds pleasantly familiar to anyone who works with linked servers, until the remote side declines to behave like a SQL table. The query language is LDAP or the provider’s SQL dialect, and the remote provider decides which predicates it can evaluate.

I check the linked-server definition and execution identity before interpreting any returned rows. The security context matters because directory permissions determine visibility. A missing row can mean an absent object, an inaccessible object, or a query that reached a limit. This is not a distinction to leave to a dashboard color. The first script inventories any ADSI linked server on the instance; it does not create one.

SELECT name, provider, data_source
FROM sys.servers
WHERE is_linked = 1
  AND provider = N'ADSDSOObject';

Keep Querying Active Directory Narrow

If the provider is already configured, an OPENQUERY call can request selected attributes from a specific directory root. Replace the domain path and linked-server name with authorized values. Keep the filter selective, and validate the returned attributes with a directory administrator. The example illustrates shape rather than a universal domain query, because a base distinguished name differs across organizations.

The linked server is evaluated by the SQL Server service or mapped security context, not by the human reading the report. That difference explains many mysterious permission results. It also explains why I would not put an ad hoc LDAP query behind an unreviewed application endpoint. A linked server is a crossing between two security systems, and both owners should agree on its purpose.

SELECT sAMAccountName, displayName
FROM OPENQUERY(ADSI, '
    SELECT sAMAccountName, displayName
    FROM ''LDAP://DC=example,DC=com''
    WHERE objectCategory = ''person''
      AND objectClass = ''user''
');

Understand the Limits of Querying Active Directory

An ADSI linked-server result is useful for a bounded lookup, not for pretending the directory is a complete warehouse. Provider behavior around row limits, paging, ordering and supported predicates can surprise a relational query writer. Joining a large local table to an unfiltered OPENQUERY result can multiply network work and still miss objects. The remote search can also see changes while it runs, so it is not a transactionally consistent snapshot of an entire directory.

I test a filter against an object count I know from another source. Then I check the edge cases: disabled accounts, nested groups, blank attributes, and objects in other organizational units. A query returning rows is the beginning of validation. It is not evidence that every eligible object arrived. When I see a report built on SELECT star from a directory root, I ask for coffee and its counting method.

Stage Directory Data for Reporting

For repeated reports, a directory-owned export or authorized integration process is usually easier to audit. It can page through results, record the extraction time and write only approved attributes into a SQL staging table. SQL Server can then join that snapshot to application data with ordinary indexes and predictable query plans. The staging process should define deletion behavior too; a missing account in a new extract should not silently remain active forever.

This query demonstrates the relational side after such a feed exists. It deliberately identifies stale rows rather than assuming yesterday’s identity state is current. The staging table name and freshness threshold are examples to adapt. I prefer a visible extraction timestamp over a report that implies it spoke to Active Directory in real time when it actually spoke to Tuesday.

SELECT account_name, display_name, extracted_at_utc
FROM dbo.DirectoryAccountStage
WHERE extracted_at_utc < DATEADD(HOUR, -24, SYSUTCDATETIME());

Protect Credentials and Scope When Querying Active Directory

A linked server should use the least privilege identity that can read the intended directory attributes. Avoid broad credentials, embedded passwords in scripts and exposing attributes simply because the provider can return them. Review who can execute the SQL that reaches the linked server, and log the export or access path according to local policy. Directory data can include personal information even when the query looks operational.

I also separate read-only lookup from directory modification. Changing group membership is an identity administration operation with its own approval and audit trail. SQL Server should not become a shortcut around that trail. Some workflows start with a table of computer names and end by changing a security group. Leave that directory change to a process the identity team owns. SQL Server can record the result afterward.

Choose the Integration Boundary

The decision is practical. For an occasional targeted lookup on an existing, approved linked server, OPENQUERY can be enough. For scheduled reconciliation, use an export with paging, error handling and a freshness marker. For application sign-in, use the identity system directly and keep the claim boundary clear. Each approach has different failure modes, so define what the application does when the directory is unavailable.

I document the search base, filter, identity, expected row range, refresh interval and owner. That small record prevents the next DBA from interpreting an empty result as proof that no users exist. If the question is operationally important, add an alert for stale or failed extraction. Querying Active Directory should answer a defined question, with a known scope and a way to detect when it has stopped answering.

Related reading on this blog: How to Query Active Directory Data Using ADSI / LDAP Linked Server and Linked Servers and What Goes Wrong With Them.

A directory lookup is not a complete identity inventory, it is a scoped view through a specific permission boundary.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.