How to Pull User Data from Active Directory Using SQL Server

In enterprise environments, Active Directory (AD) plays a central role in managing users and their credentials. There are many use cases where pulling user details from AD into SQL Server becomes essential—for auditing, reporting, access control, or even user activity dashboards.

This article explains how you can query Active Directory directly from SQL Server using Linked Server and LDAP queries.

Prerequisites

• SQL Server has AD access.
• You have domain credentials or your SQL Server service account has permissions.
• OLE DB Provider for Microsoft Directory Services is installed and enabled

Step 1: Create a Linked Server to Active Directory

EXEC sp_addlinkedserver 
    @server = 'ADSI', 
    @srvproduct = 'Active Directory Services Interface', 
    @provider = 'ADSDSOObject', 
    @datasrc = 'adsdatasource';

EXEC sp_addlinkedsrvlogin 
    @rmtsrvname = 'ADSI', 
    @useself = 'False', 
    @locallogin = NULL, 
    @rmtuser = 'DOMAIN\\YourADUser', 
    @rmtpassword = 'YourPassword';

Replace DOMAIN\\YourADUser and YourPassword with valid AD credentials that have read access.

Step 2: Query Active Directory Using OPENQUERY

SELECT cn, sn, givenName, mail, sAMAccountName
FROM OPENQUERY(ADSI, 
  'SELECT cn, sn, givenName, mail, sAMAccountName 
   FROM ''LDAP://DC=Mymart.com,DC=com'' 
   WHERE objectClass = ''user''');

Adjust the LDAP:// path to reflect your domain structure.

Following are detail of columns that we are fetching with above query

• sn: Surname
• givenName: First Name
• mail: Email Address
• sAMAccountName: Username

Querying Active Directory from SQL Server is a powerful way to centralize user data for reporting and automation. With the right setup, it seamlessly bridges identity management with enterprise insights.