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
