Have you ever needed to determine an authenticated user’s Active Directory (AD) group memberships from within SQL Server?
It makes sense if you are able to utilise AD to decide a user’s role or permissions in an application. This reduces the development time required to develop a custom permissions model, and allows the helpdesk, maintenance and operations team keep track of user privileges from one place.
It seems SQL Server 2005 does not have an out of box solution to this. Searching the various support forums and search engines lead me down the Managed .NET assembly route. Microsoft calls this building a database object with common language runtime (CLR) integration.
You may be asking yourselves… why not go down the SQL Server Integration Services route to gather an export from Active Directory… why would you query LDAP each time a query in SQL Server is executed? These are important design decisions that are really down to the required functionality.
You can find out another way to achieve this here: How To Get Active Directory Users and Groups Using SQL Server Integration Services SSIS 2005
Ok, so I am now going to build a CLR assembly in Visual Studio 2008 with the target framework set to .NET 3.5. SQL Server 2005 is actually .NET 2.0 targeted, but 3.5 uses the same CLR engine, so we are able to make use of things like LINQ and additional Active Directory components. [more on this later].
Code Snippet 1 – the assembly that will be built into a DLL in Visual Studio 2008 (download a copy here: ADIntegration.cs)
The code snippet creates a class ‘ADIntegration’ in the ‘SpittingCAML.SQLCLR.DatabaseAssemblies’ namespace. This is important to remember, as it is required later when registering the assembly.
The functionality of this method is as follows:
- User calls the method with the user name, domain and user group as parameters
- System determines if supplied user is a member of the supplied user group for the supplied domain
- System returns boolean true if user is a member and boolean false if not a member.
- The method code is shamelessly stolen from StackOverflow, user huseyint. The rest is down to the style conventions of my organisation.
I have tagged the method ‘IsGroupMember’ with the ‘[Microsoft.SqlServer.Server.SqlFunction] tag to let SQL server know this is intended to be used in place of a typical SQL Server function.
One your assembly is built, it is almost ready to be put into a database of your choice, as an object. You’ll notice we make use of LINQ and Directory Services. However, since SQL Server 2005 is targeting .NET 2.0 it is necessary for you to install some pre-requisites in order to gain access to .NET 3.5 features. It should also be noted that not all .NET 2.0 features are available in SQL Server 2005, as the code has to meet certain criteria to be included in the ‘safe’ category.
Read more about safe, unsafe and external access assemblies: here.
The pre-requisites required are:
CLR enabled must be set on your database server – this enables CLR assemblies
The target database must be set to TRUSTWORTHY to allow UNSAFE assemblies to be run against it.
System.Core [.NET 3.5 core components]
System.DirectoryServices.AccountManagement [.NET 3.5 Active Directory specific methods]
System.DirectoryServices.Protocols [.NET 2.0 dependancy of System.DirectoryServices.AccountManagement]
System.DirectoryServices [.NET 2.0 dependancy of System.DirectoryServices.AccountManagement]
The pre-requisites are typically found in the install folders for .NET 2.0 and .NET 3.5
Code Snippet 2: running the above T-SQL script will enable CLR on the database server, ensure the database you are going to be using assemblies in is trustworthy and registers the pre-requisite components. (download a copy here: ActiveDirectory_PreReqs.sql)
If you make use of the code snippet you’ll need to supply your database name and the locations of the .NET 2.0 and .NET 3.5 DLLs. The pre-requisites are installed in UNSAFE mode since they contain code that is not certified safe. If you try to register the assemblies as SAFE you will be presented with an error showing the areas of the code that are not certified safe.
We are now ready to deploy our assembly that contains the code in Code Snippet 1.
Code Snippet 3: Deploying our assembly and creating a stub that will allow standard SQL stored procedures and functions call our assembly method as if it was a standard SQL function. (download a copy here: Deploy_Assembly.sql)
You will need to edit code snippet 3 with the database name and the folder that you have built the assembly [created by Code Snippet 1] to.
Once you have run this script, you are in the position to test it.
Code Snippet 4: testing the deployment was successful. (download a copy here: Test_Assembly.sql)
Set the parameters to something more meaningful to your Active Directory, and supply a valid domain and give it a go.
Now we have a useful function to utilise alongside standard T-SQL functions. I use it to replace legacy function calls to proprietary permission models to reduce maintenance costs!
Hope this helps you out! Enjoy
Further reading on SQL Server Assemblies
- Using .Net 3.5 assemblies SQL 2005 CLR?
- Introducing SQL Server 2005’s CLR Integration
- How to: Create a Project for Database Objects that Use SQL Server Common Language Run-time Integration