Click here to Skip to main content
16,016,669 members
Articles / Database Development / SQL Server

Active Directory Query using CLR Stored Procedure

Rate me:
Please Sign up or sign in to vote.
4.80/5 (4 votes)
17 Sep 2016CPOL4 min read 22.4K   909   7   3
A SQL server CLR Stored Procedure that can query the Active Directory directly in T-SQL code

Also on GitHub - https://github.com/snorrikris/GetADobjects and https://github.com/snorrikris/UpdateAD_datawarehouse

Introduction

A CLR stored procedure is a .NET assembly that can be called from within T-SQL code in a SQL query - the assembly can do (almost) everything that C# and .NET can do in a normal Windows Service. In this case, to access data from the AD using Active Directory .NET assemblies. Two stored procedures are provided; clr_GetADobjects and clr_GetADusersPhotos. The [optional] data warehouse database AD_DW code is included also.

For example - to get all users from Active Directory:

DECLARE @ADpath nvarchar(64) = 'LDAP://DC=contoso,DC=com';
DECLARE @ADfilter nvarchar(64) = '(&(objectCategory=person)(objectClass=user))';
DECLARE @Members XML;
EXEC clr_GetADobjects @ADpath, @ADfilter, @Members OUTPUT;

Background

This project started from my need to update a SQL data warehouse containing information from the Active Directory. I've been using that database (AD_DW) to generate various reports. The problem was the rather clumsy Powershell script I was using to update the AD_DW database. I searched for a better solution, after finding none (for free) that I thought were better I decided to create this what I present here in this article.

Installing the CLR Stored Procedures

Installing this on a SQL server (SQL 2012 or later) is rather simple. It should take only a few minutes. Just download the GetADobjects_release.zip file and follow the instructions in the Deploy GetADobjects.pdf document. The SQL server is assumed to already have the required .NET assemblies; System.DirectoryServices and System.DirectoryServices.AccountManagement in C:\Windows\Microsoft.NET\Framework64\v4.0.30319\ folder.

If needed, these will be installed when you install the Active Directory Module for Windows Powershell feature on the server.

Note the instructions assume you will create a database for this - but you can use any of your own databases to contain the assembly if you want.

It's important to note that the SQL server service is assumed to be running as a domain user - this is because when the CLR SP is called, it will run as the SQL service account. A domain user is needed to query the Active Directory in your domain.

After you have installed the code, you can use the test scripts provided to try this out.

TestScriptUsingTempTables.sql will get all objects from the AD into temp tables - very useful to see table structures.

ExportPhotosToFiles.sql can be used to export all photos to files - useful to verify that photo data is usable.

A data warehouse of Active Directory objects database can be created using the UpdateAD_DataWarehouse_Create(modified).sql script, it will create all the tables and other objects needed for that. The Create_SQL_Agent_job.sql script will create a SQL Agent job that you can use to update the AD_DW database. If SQL Agent is not available, you can run the UpdateAD_DW.ps1 Powershell script in a Task Scheduler task to update the AD_DW.

Using the Code

Two Visual Studio 2015 solutions are provided in this article. GetADobjects contain the source code for the CLR stored procedures, UpdateAD_datawarehouse contains the SQL source code for the AD_DW database.

The code boils down to two CLR stored procedures; clr_GetADobjects and clr_GetADusersPhotos. Both take LDAP formatted AD path and AD filter [^] as parameters. The clr_GetADobjects takes one addition parameter @MemberList, this parameter returns group membership data as XML data when querying AD groups.

The clr_GetADusersPhotos SP returns a table for all users specified in the ADfilter parameter. The table format is always the same:

[ObjectGUID] [uniqueidentifier] NOT NULL, -- AD object GUID
[Width] [int] NULL,                       -- photo width in pixels
[Height] [int] NULL,                      -- photo height in pixels
[Format] [nvarchar](6),                   -- photo format - jpg, png etc
[Photo] [varbinary](max) NULL             -- photo binary data

Note that ObjectGUID of users is returned - not username as you would perhaps expect - the GUID is used as primary key of all objects in the AD (keep in mind that the Active Directory is a database). Take a look at the TestScriptUsingTempTables.sql script to see how to join together the users table in a query.

The clr_GetADobjects SP returns a table format dependent on the expected AD object: user, contact, computer, group or WellKnownSIDs (Note - different table format for each AD object type). The table format is generated in ADcolsTable class in ADtableDefinitions.cs code file. For example, the user table type has 69 columns. I used an Excel document to help me keep track of all the tables and columns - if you need to modify the code, the Excel document is in the source files.

It's important to note that clr_GetADobject uses the @ADfilter parameter to determine what type of table is to be returned. It's assumed that only one type of AD object is returned even though the AD filter can specify more than one type. That is not supported.

A lot of work has gone into making the code run as fast as possible and to extract as many AD properties as possible. Of particular interest are the so called Computed properties that proved to be most time consuming and of those the UserCannotChangePassword flag was the most troublesome. It turns out it's not a flag but a permission on the AD object (user), take a look at the IsUserCannotChangePassword function if you are interested.

And yes, I know I could have used the .NET AD assembly to get those properties but that method proved to be very slow - for example, getting 500+ users took more than 2 minutes - but only 18 seconds using my code.

History

  • Version 1 - Sept 2016

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior) Kvikna.com
Iceland Iceland
I started programming back in 1980, briefly in Basic, switched to Assembler (Z80 CPU) running CP/M OS. Started to program for MS-DOS in Assembler (8088 CPU) when the IBM PC made it's apperance. Switched to C when I started to write programs for Windows in 1991. In 2002 switched to C++ and MFC. In 2011 started developing in C# .NET for Sharepoint 2010. In 2016 started developing WPF applications.

Comments and Discussions

 
QuestionUpdate AD Warehouse issue Pin
Member 1474844914-Mar-20 16:11
Member 1474844914-Mar-20 16:11 
Hi Snorri

I have a need to build a data warehouse of all AD components. I decided to try to use your solution. At my first try, i decided to build all users from AD into a Temp table.

SQL
-- Get all users from AD into temp table
SET @ADpath = 'LDAP://DC=xxx,DC=com';
SET @ADfilter = '(&(objectCategory=person)(objectClass=user))';
INSERT INTO [dbo].[##ADusers] EXEC dbo.clr_GetADobjects @ADpath, @ADfilter, @Members OUTPUT;
PRINT @CurrentOp + ' from AD into temp table: ' + CAST(@@ROWCOUNT AS nvarchar(64)) + ' rows.';
SELECT * FROM [dbo].[##ADusers]

--DROP TABLE [dbo].[##ADusers]


When i ran the above, i got the following.

SQL
Exception in ReturnDatasetToSqlServer function: A result set is currently being sent to the pipe. End the current result set before calling Send.
InvalidOperationException in clr_GetADobjects. ItemCounter = 14717
Msg 6522, Level 16, State 1, Procedure clr_GetADobjects, Line 0 [Batch Start Line 0]
A .NET Framework error occurred during execution of user-defined routine or aggregate "clr_GetADobjects": 
System.InvalidOperationException: A result set is currently being sent to the pipe. End the current result set before calling Send.
System.InvalidOperationException: 
   at Microsoft.SqlServer.Server.SqlPipe.Send(String message)
   at DataSetUtilities.SendDataTable(DataTable dt)
   at StoredProcedures.ReturnDatasetToSqlServer(DataTable tbl)
   at StoredProcedures.clr_GetADobjects(SqlString ADpath, SqlString ADfilter, SqlXml& MemberList)
.
 

(0 rows affected)

Completion time: 2020-03-13T22:07:04.2931141-05:00


What could be the reason? And how do i get past this error?

Many Thanks
Venkat
QuestionFiltering on guid Pin
Slippy6628-Jun-17 5:52
Slippy6628-Jun-17 5:52 
AnswerRe: Filtering on guid Pin
Snorri Kristjansson29-Jun-17 5:42
professionalSnorri Kristjansson29-Jun-17 5:42 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.