Extending SQLRoleProvider to Support Active Directory Group Membership.

This is a ASP 2.0 + solution, for ASP .Net Apps using Integrated Windows Authentication. Please note this post has been updated.

The System.Web.Security.SqlRoleProvider allows you to store and manage Roles for your .net web applications in an SQL Database. This is very useful for building ASP .Net application where you want to a group of “admin” end-users to be able manage who can access a system and what they can do. ( I will talk about building a AD integrated System access management system in a later blog, right now I will concentrate on the nuts and bolts).

Using the standard SQLProvider system roles are provided on a per user basis, so if you have thousands of users for the system then you would need to actually add each user individually. The whole point of this is to provide “end-user” system management, so I want to make it as easy as possible.

Then I thought that it would be much easier if I could extend the default SQLProfileprovider to support Active Directory groups. That way if a set of users where already defined in Active Directory group then all the “admin” users would need to do is grant that group the role in the application and that would give everyone in that group access, and keep the system far more manageable.

If you haven’t already used an SQL Roleprovider then you need to go the http://msdn2.microsoft.com/en-us/library/ms998314.aspx and follow the instructions related to the SQLRoleprovider. This will install a default installation of the Role provider database that integrates with the standard system.Web.Security.SqlRoleProvider. Make sure that if you run the stored procedures the application name matches the applicationName in the Providers Key in your web.config and make sure that the name you use is your Domain\User name.

So for example if I have AppName of SQLProviderTest a Role called Admin and my user is MYDOM\User1 so I issue the commands.

EXEC aspnet_Roles_CreateRole 'SQLProviderTest', 'Admin'
EXEC aspnet_UsersInRoles_AddUsersToRoles 'SQLProviderTest', ' MYDOM\User1', 'Admin', 8

And the web.config section would look something like this

<roleManager enabled="true" defaultProvider="SQLRoleProvider" cacheRolesInCookie="true"> <providers> <add name="SQLRoleProvider" type="System.Web.Security.SQLRoleProvider" connectionStringName="SqlRoleManagerConnection" applicationName="SQLProviderTest" /> </providers> </roleManager>

If you then compile and run default.aspx as specified in the instructions you it should display that you are in the role.

So we need to extend the standard SqlRoleProvider to support groups. Below is the code for the ADSQLRoleProvider class. To implement this do the following steps:

In visual studio create a App_Code folder under the root directory of your web project.

Add new class file in this folder called ADSQLRoleProvider.cs
Paste the following code into the class code file and save it.

using System;
using System.Collections.Generic;
using System.Text;
using System.Web.Security;
using System.Web;
using System.Security.Principal;

/// <summary>
/// Summary description for ADSQLRoleProvider
/// </summary>
public class ADSQLRoleProvider : System.Web.Security.SqlRoleProvider{

public ADSQLRoleProvider(){}

#region ADLookup & Support Methods

private string[] getusersADgroups(IIdentity User)
{

//HttpContext.Current.User;
WindowsIdentity Wident = (WindowsIdentity)User;
List<string> groups = new List<string>();

IdentityReferenceCollection UGroups = Wident.Groups;

foreach (IdentityReference Grp in UGroups)
{

NTAccount acc = (NTAccount)Grp.Translate(typeof(NTAccount));
groups.Add(acc.Value);
}

return groups.ToArray();

}
private string[] GetRolesForGroup(string groupname)
{
return base.GetRolesForUser(groupname);
}
#endregion

#region Extended SQLRoleProvider Methods
public override bool IsUserInRole(string username, string roleName)
{
if (base.IsUserInRole(username,roleName))
{
return true;
}
else
{
string[] Ugrps = getusersADgroups(HttpContext.Current.User.Identity);
string[] GroupsRoles;

foreach (string g in Ugrps)
{
GroupsRoles = GetRolesForGroup(g);
foreach (string grole in GroupsRoles)
{
if (grole == roleName)
{
return true;
}
}
}
}
return false;
}

public override string[] GetRolesForUser(string username)
{
System.Collections.Generic.List<string> roleList = new List<string>();

string[] ActualUserRoles = base.GetRolesForUser(username);

//Only do the next check if the username matches the current web user and the users roles are empty

if ((ActualUserRoles.Length == 0) && (username.ToLower() == HttpContext.Current.User.Identity.Name.ToLower()))
{

string[] Ugrps = getusersADgroups(HttpContext.Current.User.Identity);
string[] GroupsRoles;

foreach (string g in Ugrps)
{
GroupsRoles = GetRolesForGroup(g);
foreach (string grole in GroupsRoles)
{
if (!(roleList.Exists(delegate(string s) { return s == grole; })))
{
roleList.Add(grole);
}

}
}
return roleList.ToArray();
}
else
{
return ActualUserRoles;
}
}
#endregion
}



Open the web.config file and make sure that the original connection string for the Roleprovider database is correct.
Change the role provider section in the web.config to

<roleManager enabled="true" defaultProvider="ADSQLRoleProvider" cacheRolesInCookie="true"> <providers> <add name="ADSQLRoleProvider" type="ADSQLRoleProvider" connectionStringName="SqlRoleManagerConnection" applicationName="SQLProviderTest" /> </providers> </roleManager>

Compile the website to make sure you don’t get errors.

You have now update the application to use a custom sqlroleprovider that will first check for roles of a user, and if it doesn’t find any it will then check for any roles belonging to any Active Directory groups that user is a member of.

To test that this is working remove yourself from the role by running the stored proc aspnet_UsersInRoles_RemoveUsersFromRoles

For my example above this is

EXEC aspnet_UsersInRoles_RemoveUsersFromRoles 'SQLProviderTest', 'MYDOM\User1', 'Admin'

Re-run the application to make sure that you are not in any roles.

Now add a domain group that you are a member of to the Admin role. For my example the user MYDOM\User1 is a member of MYDOM\IT_Apps group so I will add that one with the Stored proc command

EXEC aspnet_UsersInRoles_AddUsersToRoles 'SQLProviderTest', ' MYDOM\IT_Apps', 'Admin', 8

If you run the application again you will see that you are in the role.

In up and coming blogging I will talk about how to build the user-front end to this, that allows users to search, select and assign roles to AD users and groups. This will allow you to build easy use managed applications with role based security. Some screen shots of what I am talking about are below: