Querying Active Directory from SQL Server Using a Linked Server and T‑SQL Stored Procedure

In a recent project the need arose to analyze TFS users based on their Active Directory (AD) group memberships, but TFS does not store group information. The solution is to retrieve the data directly from AD and load it into a SQL Server table.

After researching online, several useful references were found, including guides on creating a SQL linked server to ADSI and querying AD data from SQL Server.

The first step is to create a linked server named ADSI that points to the AD service. This is done with the following T‑SQL commands:

-- create a linked server
EXEC sp_addlinkedserver @server = 'ADSI', @srvproduct = 'Active Directory Services 2.5', @provider = 'ADSDSOObject', @datasrc = 'adsdatasource'
-- add AD accessible account
EXEC sp_addlinkedsrvlogin @rmtsrvname = 'ADSI', @useself = 'False', @locallogin = 'sa', @rmtuser = '{domain}\{user}', @rmtpassword = '{password}'

Replace the placeholder domain, user, and password with credentials that have AD read access. After execution, the ADSI node appears under Server Objects → Linked Servers.

With the linked server in place, AD user properties can be queried using OPENQUERY :

-- Query AD user properties
SELECT * FROM OpenQuery(ADSI, 'SELECT displayName, sAMAccountName, givenName, sn, userAccountControl
FROM ''LDAP://devopshub.local/DC=devopshub,DC=local'' WHERE objectClass = ''User''')
WHERE (sn IS NOT NULL) AND (givenName IS NOT NULL)

The result set can be inserted into a SQL Server table and later joined with TFS Data Warehouse tables on fields such as displayName to enrich the user data.

Retrieving a user's group memberships is more complex because the memberOf attribute is multivalued. A practical approach is to encapsulate the logic in a stored procedure that first resolves the user's distinguished name and then queries all groups that contain that DN. The procedure is defined as follows:

CREATE PROCEDURE dbo.Get_ADGroups_ForUser
(
    @Username NVARCHAR(256)
)
AS
BEGIN
    DECLARE @Query NVARCHAR(1024), @Path NVARCHAR(1024)

    -- Resolve the user's distinguished name
    SET @Query = '
    SELECT @Path = distinguishedName
    FROM OPENQUERY(ADSI, ''
        SELECT distinguishedName
        FROM ''''LDAP://DC=devopshub,DC=local''''
        WHERE objectClass = ''''user'''' AND sAMAccountName = ''''' + @Username + '''''
    '')
    '
    EXEC SP_EXECUTESQL @Query, N'@Path NVARCHAR(1024) OUTPUT', @Path = @Path OUTPUT

    -- Retrieve all groups for the user
    SET @Query = '
    SELECT cn, AdsPath
    FROM OPENQUERY(ADSI, ''
;(objectClass=group)(member:1.2.840.113556.1.4.1941:=' + @Path + ');cn,adspath;subtree'')
    '
    EXEC SP_EXECUTESQL @Query
END
GO

-- Example of usage
EXEC dbo.Get_ADGroups_ForUser ''leixu'' -- AccountName

Running the procedure returns a list of groups (CN and ADS path) that the specified user belongs to, which can then be stored in a table for further analysis.

For a complete list of AD attribute names, refer to the provided external link.

Finally, the article includes a brief promotional note encouraging readers to follow the "devopshub" WeChat public account for more DevOps and R&D operations content.