Reporting on Active Directory using Microsoft SQL Server Part 1 -- Enabling CLR

Recently I encountered the need to figure out if all the terminated employees from my organization had been disabled within Active Directory, and if all their priveleges had been revoked. Unfortunately, there is no simple way to do this, so I decided to build a report using MSSQL, SQL Server Integration Services, and the .NET DirectoryServices library using the Common Language Runtime (CLR). This tutorial assumes a basic familiarity with SQL Server Interation Services (SSIS), Visual Studio, and other Microsoft development tools.

The CLR platform allows us to execute .NET code directly from within SQL Server. This is easy enable for "supported" libraries, but (surprise!) DirectoryServices is not officially supported for use with CLR. In order to get this to work, you have to enable some simple trusts to allow SQL Server to execute the assembly. Below is a step-by-step outline of how to do this:

Important:
As of this writing, SQL Server CLR does not support the .NET 4 target framework. Remember to target ALL your builds to .NET 3.5

1. Enable CLR for the SQL Server instance
2. Create the target database and database owner
3. Designate the target database as 'trustworthy'
4. Create the assembly with the 'unsafe' permission set

Enable CLR for the SQL Server Instance

This simple command allows your SQL server instance to execute .NET code.

sp_configure 'clr enabled', 1
go
reconfigure
go

Create the target database and database owner

If you want the database to be owned by 'sa' or an instance admin, some of these steps may not be necessary; giving the database it's own owner is more of a best practice. To make sure that your database owner can execute unsafe assemblies:

1. Create a new account with the right to execute unsafe assemblies
1. In SSMS, connect to the instance, expand the security tree, right click logins, and click 'New Login...'
2. Under 'securables', click 'search'
3. Select "The Server [servername]" and click OK
4. under 'explicit permissions', grant the 'unsafe assembly' permission to the account
2. Create a new database, specifying the new account as the owner of the database

Designate the target database as 'trustworthy'

Execute the below SQL statement to accomplish this.

ALTER DATABASE databasename SET TRUSTWORTHY ON
GO

Create the assembly with the 'unsafe' permission set

You need to make sure that the DLL is actually located at the path given, so it is best to execute this command locally on the SQL server, and not from a remote session.

CREATE ASSEMBLY [System.DirectoryServices]
FROM 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.DirectoryServices.dll'
WITH PERMISSION_SET = UNSAFE
GO

Furthermore, you need to set your project database permission level to unsafe.

1. Open the project properties
2. Click 'Database'
3. Set 'Permission Level' to unsafe

Congratulations! Your SQL Server is now ready to execute .NET code against Active Directory.