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:
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
- Enable CLR for the SQL Server instance
- Create the target database and database owner
- Designate the target database as 'trustworthy'
- 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:
- Create a new account with the right to execute unsafe assemblies
- In SSMS, connect to the instance, expand the security tree, right click logins, and click 'New Login...'
- Under 'securables', click 'search'
- Select "The Server [servername]" and click OK
- under 'explicit permissions', grant the 'unsafe assembly' permission to the account
- 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.
- Open the project properties
- Click 'Database'
- Set 'Permission Level' to unsafe
Congratulations! Your SQL Server is now ready to execute .NET code against Active Directory.