Archive for the 'Active Directory' Category

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; 


  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:             } 


  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


   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;


  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


  24: GO


  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'  



  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'  



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


   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 


  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  


  22: GO  


  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: )


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


  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)


   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 Smile


Further reading on SQL Server Assemblies

Worming tablets required

I think our IT department learnt a very valuable lesson today!

Most of our servers are behind a fortress of firewalls, VPN connections and yet, today, we find ourselves ashamed of what has happened!

The Conficker grade E worm (Worm:Win32/Conficker.E: identified by the MMPC on April 8, 2009) made its way onto our network. It matters not how it got there, but does point the finger at our inadequate antivirus products, or perhaps it was more complacency…. no single machine is directly linked with the outside world, we use linux and unix based firewalls… it couldn’t affect us… could it?

Well it did… the investigation is on… my detective hat is on, and my finger is pointed at a small section of the network that has an un-patched server infrastructure. The reasons for not patching them almost outweigh the hassle it is to deal with today’s attack… for antiquated pieces of software/hardware that we can’t replace/rewrite* simply don’t run on operating systems with a patch! I’m sure there are other organisations out there with similar problems.

Have a read of what it does: here

It is rather nasty, and has the ability to block access to certain websites, certain applications and certain system administration tasks! (am I the only one to think it is rather clever?… not that I applaud the application of this genius in any way!)

As most of what you’ll read on the internet is about solution to it, I thought I’d share one of the most annoying symptoms

  • Account lockout policies being activated.

This one was a huge issue for us! With just under 7000 active directory users in the entire organisation, roughly 2500 were locked out… and this number kept rising all day. As you’d imagine, frustrated users were on the phone to the helpdesk, and some people, because of this issue did not deliver the service that our customers expect.

So fed up of the issue, and not being able to do our normal tasks today (the source control system went down, all our SharePoint boxes were down to be patched/checked), a colleague and I wrote a windows form application in C# that periodically queried our active directory to look for locked out accounts.

ad_accounts_locked Figure 1: Locked out user account tracker

The progress bar in Figure 1 shows the amount of users affected by this worm. A significant number!

The vet has been called, and a dose of software patches are on the way. Look in the research URLs below if you are yourself scrabbling for a solution. Good Luck :-)


* We could replace/rewrite them but it would be far too expensive and disruptive to the business!


Removing autorun might help prevent spread of the worm
F-secure FAQ on conficker
F-secure technical description of conficker
Conficker Worm: Help Protect Windows from Conficker

Individuals with information about the Conficker worm are encouraged to contact their international law enforcement agencies. Additionally, Microsoft has implemented an Antivirus Reward Hotline, +1-425-706-1111, and an Antivirus Reward Mailbox,, where tips can be shared.

When the Delegation tab is missing in Windows 2003 Active Directory Users and Computers

So you’ve been investigating Kerberos, and you’ve done the command ’setspn.exe’ setup (part of the Windows 2003 support tools install) of your Security Principle Names for your domain controller… but when you try to assign a user account some delegation rights… the damn delegation tab is missing!

The first thing to do is to check that your domain controller is operating in Windows 2003 mode. By default you’ll find your system will operate in the Windows 2000 compatible mode.

To do this, look at the screenshot below, you’ll need to go to active directory users and computers, right click on your domain, and click ‘Raise Domain Functional Level’


This will give you something like this:


This dialog box tells me that my domain controller is operating in the correct mode for the Delegation tab to be displayed in the active directory settings. If your server is running in Windows 2000 compatible mode, you’ll be given the option to raise it’s functional level - you should do this if you want the delegation tab to appear! I’m guessing, as I’ve never tried it out, that if you want the server to be a domain controller with Windows 2000 and lower machines it wont work when you change this setting, so beware, as once you’ve done it… there’s no going back!

And there you have it… my server now has a delegation tab:


I hope this helps you out, as when I googled for this issue, I found nothing… please add any links to more descriptive discussion of this issue you you know of any.



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.