Using ASP.NET Membership Roles (SQL table based roles) with Windows Authentication (Integrated Security)

I found this great blog posting by Scott Guthrie regarding how to use the ASP.NET Membership Roles with Windows Authentication. In this scenario users don’t have to login because Windows Authentication (I knew it as Integrated Security, but I don’t know where I got that name :). This works well with Internet Explorer and Intranet environments. The scenario also dictates that the roles are defined in SQL Server tables instead of Active Directory. Beyond that, Scott’s blog posting (noted above) explains the scenario very well.

Scott’s example goes into more functionality than I will here. However, I do cover how to create the supporting SQL tables and stored procedures and he doesn’t, but he does link to a page that does. He covers how to due Security Trimming of menus, admin interface, automatically creating the roles, etc. If you need details on those things please check ScottGu’s Blog: Implementing Role Based Security with ASP.NET using Windows Authentication and SQL Server. I have to give nearly all credit for the source of all my knowledge to Scott’s blog posting. NOTE: Scott also includes a link to download a working website project.

I want to give the basic steps only. I assume you have a website that just has a default web.config and thus default security.

Step 1: Enable Windows Authentication

Add the authentication and authorization tags to your web.config under the system.web tag. This will require all users to be authenticated. Be sure to enable Windows Integrated Authentication in IIS.

<system.web>
  …
  <authentication mode="Windows"/>
  <authorization>
      <deny users="?"/>
  </authorization>

Step 2: Configure for SQL Server

By default Visual Studio wants to use SQL Express instead of SQL Server. Really, the only thing that Visual Studio needs is to know where the ASP.NET Membership provider database is located. To tell Visual Studio where to connect to SQL Server, add the following (modify for your database location, etc) to your web.config.

<connectionStrings>
    <remove name="LocalSqlServer"/>
    <add name="LocalSqlServer" connectionString="Data Source=(local);Initial Catalog=MyDB;Integrated Security=True" providerName="System.Data.SqlClient"/>
    

Please note, the name LocalSqlServer is required, do not change this.

Step 3: Create supporting tables in SQL Server database

The roles membership table and stored procedures requires tables to be created in a database. This can be in your application database, or it can be in a separate database. There may be an easier way to create these tables and stored procedures, but this works well, but it is a bit of an obscure location for the .exe.

If you are using .NET 2.0 or greater (yes v3.0 and v3.5 and v3.5.1 also) the .exe you need to execute is located in a path similar to the following:

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe

Unless you want to generate support for all the other Application Services (not necessary for this scenario) I recommend using the following command line arguments. Change the server and database name to suit your environment.

aspnet_regsql.exe -E -S myServer -d MyDB -A m -A r

You can also run the aspnet_regsql.exe without parameters and a wizard will run. The wizard does NOT allow you to only install certain features, it installs all the features. Use the command line with args or use the less flexible, but prettier wizard. Your choice.

Step 4: Enable SQL based Role Management

Replace this line in your web.config

<roleManager enabled="true" />

with these lines.

<roleManager enabled="true" defaultProvider="SqlRoleManager">
    <providers>
        <clear/>
        <add name="SqlRoleManager" type="System.Web.Security.SqlRoleProvider" connectionStringName="LocalSqlServer"/>
    </providers>
</roleManager>

NOTE: In this example the membership database and my application database are the same. They don’t have to be though.

NOTE: If you are using a virtual directory for development, I would recommend adding the applicationName attribute to the SqlRoleManger tag. It doesn’t appear to be a problem with file based Web Site projects in Visual Studio 2008, but it appears that it is with IIS 5 and using virtual directories. To better understand the issue, see here.

 

Step 5: Add the roles

You can do this via code or using the configuration ui.

To use the UI, run the Project | ASP.NET Configuration tool. Click the Security tab, and click the Create or Manage roles link. Add any number of roles you want.

To create the roles (maybe on application start in the Global.asax.cs file)

void Application_Start(object sender, EventArgs e)
{
    if (!Roles.RoleExists("Admin")) 
    {
        Roles.CreateRole("Admin");

    }
}

Step 6: Add users to the roles

The ASP.NET Configuration tool doesn’t work for adding users when using Windows Integrated Authentication mode. So, you have to do this via code. The following block of code checks to see if the user is in a role. If the user is not in the role, the user is then added to that role.

if (!Roles.IsUserInRole(userName, roleName))
{
    Roles.AddUserToRole(userName, roleName);
}

You may want to create a custom user interface to add users to roles. Scott Gutherie provides an example of it in VB.NET in his downloadable working demo. Below is a virtual copy of the admin page that is in the project, but mine is in C#, not VB.NET.

Here is the code-behind

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Security;

namespace MyApp
{
    public partial class UserAdmin : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void PopulateRoleList(string username)
        {
            RoleList.Items.Clear();
            string[] roleNames = Roles.GetAllRoles();

            foreach (string roleName in roleNames)
            {
                ListItem roleListItem = new ListItem();
                roleListItem.Text = roleName;
                roleListItem.Selected = Roles.IsUserInRole(username, roleName);
                RoleList.Items.Add(roleListItem);
            }
        }

        protected void UpdateRolesFromList()
        {
            foreach (ListItem roleListItem in RoleList.Items)
            {
                string roleName = roleListItem.Value;
                string userName = TxtUserName.Text;
                bool enableRole = roleListItem.Selected;

                if (enableRole && !Roles.IsUserInRole(userName, roleName))
                {
                    Roles.AddUserToRole(userName, roleName);
                }
                else if (!enableRole && Roles.IsUserInRole(userName, roleName))
                {
                    Roles.RemoveUserFromRole(userName, roleName);
                }
            }
        }

        protected void LookupBtn_Click(object sender, EventArgs e)
        {
            PopulateRoleList(TxtUserName.Text);
            UpdateBtn.Visible = true;
        }

        protected void UpdateBtn_Click(object sender, EventArgs e)
        {
            UpdateRolesFromList();
            PopulateRoleList(TxtUserName.Text);
        }
    
    }
}

Here is the .aspx content.

<%@ Page Title="" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true" CodeBehind="UserAdmin.aspx.cs" Inherits="MyApp.UserAdmin" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" runat="server">


    <h3>Role Manager</h3>

    <div>
        Enter UserName: 
        <asp:TextBox ID="TxtUserName" runat="server"></asp:TextBox>
        
        <asp:Button ID="LookupBtn" runat="server" Text="Search" 
            onclick="LookupBtn_Click" />
        
    </div>

    <div class="roleList">
        <asp:CheckBoxList ID="RoleList" runat="server">
        </asp:CheckBoxList>
    </div>
    
    <div>
        <asp:button ID="UpdateBtn" text="Update" Visible="false" runat="server" 
            onclick="UpdateBtn_Click" />
    </div>
    
</asp:Content>

Step 7: Authorizing Access based on Roles

The most basic way to implement security is at the page level. This can be done via code using the membership api and Server.Transfer() calls, but it can also be done via the web.config and is the recommended way.

Let’s make sure users in our Admin role have access to everything by default. To do this modify the web.config authorization tag so that it it allows the Admin role, and blocks everyone else. This example assume your admin group is called Admin.

<authorization>
    <deny users="?"/>
    <allow roles="Admin"/>
</authorization>

To control which pages (or directories) different roles have access to you will need to explicitly grant rights. You need to add a location tag for each page you want to grant rights to. The location tag needs to be added in  the configuration tag. You can specify the path to be a file or a directory. You can specify multiple roles using a commas, but do NOT use semi-colons. Semi-colons sort of work such that the first item in the list is read. You can also create different web.config in directories to control the rights for that directory only. This can help reduce web.config clutter. Here is an example entry for your web.config.

<configuration>
    <location path="Review.aspx">
        <system.web>
            <authorization>
                <allow roles="Reviewer,Process Manager"/>
                <deny users="*"/>
            </authorization>
        </system.web>
    </location>

Tips: In the web.config, when you are specifying users or roles, * and ? have special meaning.
* means all users
? means anonymous
It is also important to understand that the allow and deny tags are applied in the order they show in the parent tag. Order matters.

References

• ScottGu’s Blog: Implementing Role Based Security with ASP.NET using Windows Authentication and SQL Server
• From ScottGu’s Blog: Working downloadable demo
• Web.config schema