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].
1: namespace SpittingCAML.SQLCLR.DatabaseAssemblies
3: using System;
4: using System.Collections.Generic;
5: using System.Data.SqlTypes;
6: using System.DirectoryServices.AccountManagement;
7: using System.Linq;
8: using System.Text;
9: using Microsoft.SqlServer.Server;
10: using System.Data;
11: /// <summary>
12: /// this class contains the AD Integration methods (Stored Procedures)
13: /// </summary>
14: public class ADIntegration
16: /// <summary>
17: /// this method determines if a user is a member of the supplied
18: /// active directory group
19: /// </summary>
20: /// <param name="user">the user to search for</param>
21: /// <param name="domain">the domain to search</param>
22: /// <param name="groupName">the group to determine</param>
23: /// <returns>true if access granted, false if not granted</returns>
25: public static bool IsGroupMember(string user, string domain, string groupName)
27: bool isGroupMember = false;
29: // can't proceed with lookup if supplied parameters are null
30: if(string.IsNullOrEmpty(user) || string.IsNullOrEmpty(domain) || string.IsNullOrEmpty(groupName))
32: return isGroupMember;
34: using (var ctx = new PrincipalContext(ContextType.Domain, domain))
36: using (var grp = GroupPrincipal.FindByIdentity(ctx, IdentityType.Name, groupName))
38: isGroupMember = grp != null &&
41: .Any(m => m.SamAccountName.ToLower() == user.ToLower());
45: return isGroupMember;
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
1: -- must enable CLR on the database
3: EXEC sp_configure 'show advanced options' , '1';
7: EXEC sp_configure 'clr enabled' , '1'
11: DECLARE @dbName NVARCHAR(255)
12: SET @dbName = '<database name here>'
13: DECLARE @sql NVARCHAR(4000)
15: SET @sql = 'USE ' + @dbName;
17: -- must make database trustworthy
19: EXEC sp_executesql @sql
20: EXEC sp_changedbowner 'sa'
21: SET @sql = 'ALTER DATABASE ' + @dbName + ' SET TRUSTWORTHY ON;'
22: EXEC sp_executesql @sql
26: DECLARE @DotNet2Folder NVARCHAR(1000)
27: DECLARE @DotNet35Folder NVARCHAR(1000)
28: DECLARE @path NVARCHAR(4000)
30: SET @DotNet2Folder = 'C:\Windows\Microsoft.NET\Framework\v2.0.50727'
31: SET @DotNet35Folder = 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.5'
34: --if the [System.Core] assembly exists then drop it
35: IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'System.Core')
36: DROP ASSEMBLY [System.Core]
38: --if the [System.DirectoryServices.AccountManagement] assembly exists then drop it
39: IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'System.DirectoryServices.AccountManagement')
40: DROP ASSEMBLY [System.DirectoryServices.AccountManagement]
42: --if the [System.DirectoryServices.Protocols] assembly exists then drop it
43: IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'System.DirectoryServices.Protocols')
44: DROP ASSEMBLY [System.DirectoryServices.Protocols]
46: --if the [System.DirectoryServices] assembly exists then drop it
47: IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'System.DirectoryServices')
48: DROP ASSEMBLY [System.DirectoryServices]
50: CREATE ASSEMBLY [System.Core]
51: FROM @DotNet35Folder + '\system.core.dll'
52: WITH permission_set = UNSAFE
54: CREATE ASSEMBLY [System.DirectoryServices.Protocols]
55: FROM @DotNet2Folder + '\System.DirectoryServices.Protocols.dll'
56: WITH PERMISSION_SET = UNSAFE
58: CREATE ASSEMBLY [System.DirectoryServices.AccountManagement]
59: FROM @DotNet35Folder + '\System.DirectoryServices.AccountManagement.dll'
60: WITH permission_set = UNSAFE
62: CREATE ASSEMBLY [System.DirectoryServices]
63: FROM @DotNet2Folder + '\System.DirectoryServices.dll'
64: WITH PERMISSION_SET = UNSAFE
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.
1: DECLARE @dbName NVARCHAR(255)
2: SET @dbName = '<database name here>'
4: DECLARE @assemFolder NVARCHAR(1000)
5: SET @assemFolder = '<assembly folder here>'
7: -- have to drop the function first because you can't delete the assembly
8: -- when the function is dependant on it
9: IF EXISTS (SELECT *
10: FROM sysobjects
11: WHERE name = N'udf_IsGroupMember')
12: DROP FUNCTION dbo.[udf_IsGroupMember]
14: --if the [SpittingCAML.SQLCLR.DatabaseAssemblies] assembly exists then drop it
15: IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'SpittingCAML.SQLCLR.DatabaseAssemblies')
16: DROP ASSEMBLY [SpittingCAML.SQLCLR.DatabaseAssemblies]
18: CREATE ASSEMBLY [SpittingCAML.SQLCLR.DatabaseAssemblies]
19: FROM @assemFolder + '\SpittingCAML.SQLCLR.DatabaseAssemblies.dll'
20: WITH permission_set = unsafe
26: * Name: [udf_IsGroupMember]
29: * Description:
31: * Function determines if the supplied user is in an active directory group
33: * NB: this is a MANAGED CODE function utilising the
34: * SpittingCAML.SQLCLR.DatabaseAssemblies assembly class
36: * Revision History:
38: * Version Date Author(s) Description
39: * 1.0 08 Dec 2009 Martin Allen Intial Release
42: CREATE FUNCTION [udf_IsGroupMember]
44: @user nvarchar(1000),
45: @domain nvarchar(1000),
46: @groupName nvarchar(1000)
48: RETURNS BIT
49: AS EXTERNAL NAME [SpittingCAML.SQLCLR.DatabaseAssemblies].[SpittingCAML.SQLCLR.DatabaseAssemblies.ADIntegration].[IsGroupMember]
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.
1: DECLARE @user nvarchar(1000)
2: DECLARE @domain nvarchar(1000)
3: DECLARE @groupName nvarchar(1000)
5: -- TODO: Set parameter values here.
7: set @user = 'testuser'
8: set @domain = 'mydomain'
9: set @groupName = 'mygroup'
11: SELECT [dbo].[udf_IsGroupMember](@user, @domain, @groupName)
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