Determining Active Directory group membership from SQL Server 2005

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 

   2: { 

   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 

  15:     { 

  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> 

  24:         [Microsoft.SqlServer.Server.SqlFunction] 

  25:         public static bool IsGroupMember(string user, string domain, string groupName) 

  26:         { 

  27:             bool isGroupMember = false; 

  28:  

  29:             // can't proceed with lookup if supplied parameters are null 

  30:             if(string.IsNullOrEmpty(user) || string.IsNullOrEmpty(domain) || string.IsNullOrEmpty(groupName)) 

  31:             { 

  32:                 return isGroupMember; 

  33:             }           

  34:             using (var ctx = new PrincipalContext(ContextType.Domain, domain)) 

  35:             { 

  36:                 using (var grp = GroupPrincipal.FindByIdentity(ctx, IdentityType.Name, groupName)) 

  37:                 { 

  38:                     isGroupMember = grp != null && 

  39:                         grp 

  40:                         .GetMembers(true) 

  41:                         .Any(m => m.SamAccountName.ToLower() == user.ToLower()); 

  42:                 } 

  43:             } 

  44:             

  45:             return isGroupMember; 

  46:         } 

  47:     } 

  48: } 

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

   2:  

   3: EXEC sp_configure 'show advanced options' , '1';

   4: go

   5: reconfigure;

   6: go

   7: EXEC sp_configure 'clr enabled' , '1'

   8: go

   9: reconfigure;

  10:  

  11: DECLARE @dbName NVARCHAR(255)

  12: SET @dbName = '<database name here>'

  13: DECLARE @sql NVARCHAR(4000)

  14:  

  15: SET @sql = 'USE ' + @dbName;

  16:  

  17: -- must make database trustworthy

  18:  

  19: EXEC sp_executesql @sql

  20: EXEC sp_changedbowner 'sa'  

  21: SET @sql = 'ALTER DATABASE ' + @dbName + ' SET TRUSTWORTHY ON;'

  22: EXEC sp_executesql @sql

  23:  

  24: GO

  25:  

  26: DECLARE @DotNet2Folder NVARCHAR(1000)

  27: DECLARE @DotNet35Folder NVARCHAR(1000)

  28: DECLARE @path NVARCHAR(4000)

  29:  

  30: SET @DotNet2Folder = 'C:\Windows\Microsoft.NET\Framework\v2.0.50727'

  31: SET @DotNet35Folder = 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.5'

  32:  

  33:  

  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] 

  37:  

  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] 

  41:  

  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] 

  45:  

  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] 

  49:  

  50: CREATE ASSEMBLY [System.Core]  

  51: FROM @DotNet35Folder + '\system.core.dll'  

  52: WITH permission_set = UNSAFE  

  53:  

  54: CREATE ASSEMBLY [System.DirectoryServices.Protocols]  

  55: FROM @DotNet2Folder + '\System.DirectoryServices.Protocols.dll'  

  56: WITH PERMISSION_SET = UNSAFE  

  57:  

  58: CREATE ASSEMBLY [System.DirectoryServices.AccountManagement]

  59: FROM @DotNet35Folder + '\System.DirectoryServices.AccountManagement.dll'

  60: WITH permission_set = UNSAFE  

  61:  

  62: CREATE ASSEMBLY [System.DirectoryServices]  

  63: FROM @DotNet2Folder + '\System.DirectoryServices.dll'  

  64: WITH PERMISSION_SET = UNSAFE  

  65:  

  66: GO  

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>'

   3:  

   4: DECLARE @assemFolder NVARCHAR(1000)

   5: SET @assemFolder = '<assembly folder here>'

   6:  

   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]

  13:  

  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] 

  17:  

  18: CREATE ASSEMBLY [SpittingCAML.SQLCLR.DatabaseAssemblies]  

  19: FROM @assemFolder + '\SpittingCAML.SQLCLR.DatabaseAssemblies.dll'  

  20: WITH permission_set = unsafe  

  21:  

  22: GO  

  23:  

  24: /********************************************************************************

  25: *

  26: * Name: [udf_IsGroupMember]

  27: *

  28: *

  29: * Description: 

  30: *

  31: *       Function determines if the supplied user is in an active directory group

  32: *     

  33: *        NB: this is a MANAGED CODE function utilising the 

  34: *                   SpittingCAML.SQLCLR.DatabaseAssemblies assembly class

  35: *

  36: * Revision History:

  37: *

  38: * Version Date         Author(s)        Description

  39: * 1.0     08 Dec 2009  Martin Allen        Intial Release

  40: *

  41: ********************************************************************************/

  42: CREATE FUNCTION [udf_IsGroupMember]

  43: (

  44:  @user nvarchar(1000), 

  45:  @domain nvarchar(1000), 

  46:  @groupName nvarchar(1000)

  47: )

  48: RETURNS BIT

  49: AS EXTERNAL NAME [SpittingCAML.SQLCLR.DatabaseAssemblies].[SpittingCAML.SQLCLR.DatabaseAssemblies.ADIntegration].[IsGroupMember]

  50:  

  51: GO

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)

   4:  

   5: -- TODO: Set parameter values here.

   6:  

   7: set @user = 'testuser'

   8: set @domain = 'mydomain'

   9: set @groupName = 'mygroup'

  10:  

  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 Smile

SC

Further reading on SQL Server Assemblies




You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

2 Responses to “Determining Active Directory group membership from SQL Server 2005”

  1. Bart Zuidgeest Says:

    Have you ever tried this on a sql 2008 server running on windows 2008. I can’t get it going.

    The following error occurs using the sp System.IO.FileLoadException: Could not load file or assembly ‘System.DirectoryServices.AccountManagement, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′ or one of its dependencies. Assembly in host store has a different signature than assembly in GAC. (Exception from HRESULT: 0×80131050)

    I loaded the assemblies as in your article. I only ommited system.core as it seem to be on the blessed list and thus should not be neccesary

  2. SpittingCAML Says:

    Bart,

    I’ve seen this before on x64 systems. Are you running SQL 2008 x64 on Windows 2008 Server x64?

    If so, you need to ensure the assembly is compiled x64 and the version of DirectoryServices.AccountManagement is the x64 version. I suspect it has a different public key token (and in a different location).

    Change SET @DotNet35Folder = ‘C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.5′ in the script to the location of the x64 .NET.

    let me know if this helps.

    SC

Leave a Reply