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 WellKnownSID
s (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
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.