Recipe: Implementing Role Based Security with ASP.NET using Windows Authentication and SQL Server

Problem

You are building an Intranet expense report application for your organization, and want to enable role-based authentication and authorization capabilities within it.  Specifically, you want to create logical roles called “approvers”, “auditors”, and “administrators” for the application, and grant/deny end-users access to functionality within the application based on whether they are in these roles.

Because your application is an Intranet solution, you want to use Windows Authentication to login the users accessing the application (avoiding them having to manually login).  However, because the roles you want to define are specific to your application, you do not want to define or store them within your network’s Windows Active Directory.  Instead, you want to define and store these roles within a database.  You then want to map Windows user accounts stored within Active Directory to these roles, and grant/deny access within the application based on them.

In addition to using roles to authorize access to individual pages within the application, you want to dynamically filter the links displayed within the site’s menu navigation based on whether users have permissions (or not) to those links.  And lastly, you want to build-in a custom role-management administration UI directly within the expense report application for “expense app administrators” to manage these roles and control who has access to the capabilities of the app:

 

Solution 

I've put together a detailed post that walks through step-by-step how to implement all of this.  You can read it here, and download the completed sample I walk through how to build here.

Hope this helps,

Scott

Published Sunday, July 23, 2006 9:32 PM by ScottGu

Comments

# Best of both worlds?

Monday, July 24, 2006 2:05 AM by todd brooks
Is it possible, with the example you show here to also include basic Forms authentication and allow the user, upon installation to choose which method they wish to use? Could this "dual" functionality be included in the same assembly and differentiated via changes to the web.config file?

# re: Recipe: Implementing Role Based Security with ASP.NET using Windows Authentication and SQL Server

Monday, July 24, 2006 2:19 AM by ScottGu

Hi Todd,

Yep -- you could definitely do this and allow the adminsitrator installing the application to choose which authentication mode to use.

One of the nice things about the ASP.NET security architecture is that it uses a common architecture.  The Roles system I describe in the article above, for example, works equally the same for Forms Auth, Passport, or any other custom authentication system you might want to build.

Likewise, the User object with the User.Identity.Name and User.IsInRole() method works the same regardless of what authentication option is used.

So because the code you write in the app is fairly authentication agnostic, it would be easy to switch between them on app install.

Hope this helps,

Scott

# re: Recipe: Implementing Role Based Security with ASP.NET using Windows Authentication and SQL Server

Monday, July 24, 2006 10:10 AM by Kevin
What if you need an extra layer. Most of our apps have the basic role approach, however we allow the user to create new areas that need roles and certain areas contain specific objects that depending on managerial whims could allow certain people in that role access to a document and other people in that role are denied. We are a fairly large company and doing roles for every scenario is mind boggilng and would be maintanable with growth. Thanks.

# re: Recipe: Implementing Role Based Security with ASP.NET using Windows Authentication and SQL Server

Monday, July 24, 2006 11:00 AM by ScottGu

Hi Kevin,

The Roles API is flexible, so you can add/remove roles on the fly (using the Roles.CreateRole() method).

You could use something like this with the built-in role provider to dynamically add roles as needed via your admin tool.

An alternative approach is to use the AZMAN role manager.  It supports the concept of both roles and capabilities within roles.  You'd need to add some custom glue code to access the sub-capability checks from ASP.NET, but that is something you could do.

Hope this helps,

Scott

# re: Recipe: Implementing Role Based Security with ASP.NET using Windows Authentication and SQL Server

Monday, July 24, 2006 6:28 PM by Steve
Hi Scott...another very helpful post! :) What is this theme you have? Is it Vista? Steve

# re: Recipe: Implementing Role Based Security with ASP.NET using Windows Authentication and SQL Server

Monday, July 24, 2006 8:25 PM by ScottGu

Hi Steve,

Glad you liked it! :-)

The screenshot above is from IE7 on Windows Vista.  I'm using the "Graphite" color scheme with zero transparency for my Windowing color.  I've been playing around with different color/translucency patterns on Vista and this is the one I like the most right now.

After switching to Vista and Office12 on my laptop for a few days, I can't go back to XP.  The eye-candy and visuals are addictive. :-)

Thanks,

Scott

# re: Recipe: Implementing Role Based Security with ASP.NET using Windows Authentication and SQL Server

Tuesday, July 25, 2006 2:42 PM by Dom
Scott, The User.Identity.Name property reveals a Windows account name with the VWD Express internal server. When using VS 2003 I get: "". Is the internal server configurable like that of ISS? For example, I can set anon access? The internal server is all very well but it doesn't simulate the account / login conditions of a production server, unless you know otherwise. Thanks

# re: Recipe: Implementing Role Based Security with ASP.NET using Windows Authentication and SQL Server

Tuesday, July 25, 2006 3:10 PM by Albert Pascual
Now, what about if the user database already exist in a ORACLE database, roles and permissions are stored very diffent than ASP.NET Any ideas how to acomplish same results? Al

# re: Recipe: Implementing Role Based Security with ASP.NET using Windows Authentication and SQL Server

Tuesday, July 25, 2006 4:07 PM by ScottGu

Hi Albert,

The nice thing about ASP.NET 2.0 is the Provider Model gives you the extensibility to plug-in any store to manage Roles.  You could build an Oracle Roles provider instead of using the built-in SQL one, and point at your existing Oracle database instead.  All of the code in my sample app above would stay the same.

Here is a web-site that talks more about the ASP.NET provider model and how it works: http://msdn.microsoft.com/asp.net/downloads/providers/

Hope this helps,

Scott

# re: Recipe: Implementing Role Based Security with ASP.NET using Windows Authentication and SQL Server

Tuesday, July 25, 2006 4:08 PM by ScottGu

Hi Dom,

Have you added the <authorization> rule to deny anonymous access to your web.config file in your VS 2003 project (like in the same I attached)?  Once you do that IIS will cause the authentication to occur -- at which point User.Identity.Name will return the windows username of the visiting browser.

Hope this helps,

Scott

# re: Recipe: Implementing Role Based Security with ASP.NET using Windows Authentication and SQL Server

Tuesday, July 25, 2006 5:48 PM by Dom
Scott, I think you misunderstood my question. In VWD, when using User.Identity.Name, I do not want the Windows account name. I want the "", which is what happens in VS 2003 with anon access and no authorisation rules. Similarly, when I disable anon access, I get the windows account username. Is the internal server configurable like IIS 5 and 6 are? Thanks.

# re: Recipe: Implementing Role Based Security with ASP.NET using Windows Authentication and SQL Server

Wednesday, July 26, 2006 1:11 AM by ScottGu

Hi Dom,

For security reasons the VWD web-server doesn't allow anonymous access with Windows Authentication -- so it always provides the user-name.  You can, though, have anonymous access with Forms Auth and VWD web-server.

If you want completely anonymous access when using Windows Auth, you'll need to use IIS.

Hope this helps,

Scott

# re: Recipe: Implementing Role Based Security with ASP.NET using Windows Authentication and SQL Server

Wednesday, July 26, 2006 9:32 AM by TYRO
Simply Brilliant. Thanks a lot Scott

# re: Recipe: Implementing Role Based Security with ASP.NET using Windows Authentication and SQL Server

Wednesday, July 26, 2006 11:52 AM by Dom
Thanks Scott. That understanding helps (on VWD server). I presume it's possible to get the VWD Express debugger to run with IIS as server? I have been able to do this with VS 2005. Could you author a post that shows how to do this and give reasons for why someone would want to use ISS over the built in server, like the logins example as you have covered (as in the values one normally gets with a production server environment? Thanks.

# re: Recipe: Implementing Role Based Security with ASP.NET using Windows Authentication and SQL Server

Wednesday, July 26, 2006 1:01 PM by ScottGu

Hi Dom,

Yep -- you can definitely use the Visual Web Developer Express debugger to debug IIS.  That is fully supported.

The main reasons for including the VWD web-server were:

1) Convenience -- it avoids you having to setup a web-server and/or configure an application root.

2) For systems that don't have IIS installed (for example: XP Home, or corporations that don't allow web-servers on dev machines).

3) For cases where you want to run as a non-admin on the system.  IIS requires admin rights for some operations, while the VWD web-server will run with just normal user rights.

Hope this helps,

Scott

# re: Recipe: Implementing Role Based Security with ASP.NET using Windows Authentication and SQL Server

Thursday, July 27, 2006 5:35 AM by Granville Barnett
I'm putting this to good use now!! Great post Scott.

# re: Recipe: Implementing Role Based Security with ASP.NET using Windows Authentication and SQL Server

Thursday, July 27, 2006 3:08 PM by LukCAD

Hello Scott!

It is another nice example. Indeed, yours your video examples, Mitchel's and Tabor's are more very power then any super books. If you will show that example by video, some people will understand how it is easy and useful to have role's permission by fly on portal or any web application. Indeed the role politic is basic foundation in any server's application dedicated for servicing a lot of users by internet or intranet. The default providers of membership and roles made the big jump to fast programming in this sphere. The template with role and membership controls i think will good step to another level of programming art. I was glad to look at the video-issue about how it fast now implement SAP's components by ASP.NET with having todo templates based on asp.net's security schema. Indeed, earliest version of SAP connectors was been the power regards even only two programming possabilities: creating saplogo.aspx page and creating proxiserver pages with all necessary libraries about tables, methods and events for usage it in the web project by only clicking on mouse. I suppose that the way of power combined template is very usefull in our time and make new power layer of programming to help developer to write applications for data communication between web browser's client and such power systems like SAP, Oracle, Simatic NET and so on.

Great.

Sincerely, LukCAD.

# re: Recipe: Implementing Role Based Security with ASP.NET using Windows Authentication and SQL Server

Thursday, July 27, 2006 4:24 PM by Darko Konrad
Perfect timing, the day before reading the your helpful post, I figured out that would be the way to go for my intranet app. It's nice to have a offcial ratification through the godfather of asp.net ;-) I have a question however: I placed my both DBs (aspnetdb and the app one) on the DEV Server running only SQL Server. The connection strings are set up correctly as well ( with remove LocalSqlServer and add the new one on the DEV Server). The Problem: altough I use the Windows authentication and impersonation my app is trying to contact the aspnetdb (and not the app db) using the NETWORK SERVICE account of my dev machine. WHY? What am I missing? Is there a possible scenario of having the IIS Server and SQL Server 2005 (running the both DBs) in physically separate servers? I really want to use the same databases also during the dev time. Any suggestions? thx and regards, Darko

# my brain hurts

Thursday, July 27, 2006 5:54 PM by todd
I looked at the custom provider and it seems like a good fit for what I need to do with some modifications. I guess if I explained what I am trying to do then it might be easier to steer me in the direction I need to go. The main system has different modules and people can have access to one or many modules. John Doe has access to: Property Management (Module 1) - 'User' role - View Properties(viewproperties.aspx) Customers (Module 2) - 'Admin' role - Add New Customer(addcustomer.aspx) - Reports(reports.aspx) Future Modules, etc. John has access to module 1 and is in 'User' role. User role is restricted to certain pages, etc. John has access to Module 2 and is in 'Admin' role. Admin role can view all. When John logs in, 2 tabs are shown, Property Management and Customers. When he clicks on an item, sub navigation is shown based on his role. Essentially the application needs to scale out to however many modules are needed and top-level navigation (module) needs to be built on the fly based on users access level. What would be the best approach to this scenario? Have the navigation (modules and subsequent pages) all pulled from the database or would a mix of database and web.config in folders work better? Thanks in advance

# re: Recipe: Implementing Role Based Security with ASP.NET using Windows Authentication and SQL Server

Thursday, July 27, 2006 8:28 PM by BryanK
Hi Scott, Great article. I have it working with an Oracle RoleProvider I created (I used the sample Access provider and converted all of the inline SQL to work with my Oracle 10 XE db). I do have one question. I am using Windows Authentication (like your example) and I am only using the Role and Profile providers. Is there a way to get a unique list of ALL users that have been assigned to any profile? I realize that I can get my user list from the AD (gigantic) but I want just the list of users from the aspnet_users table that have been assigned a role. I know I can make a call to that table and possible extend my custom Role provider...but thought I'd ask if you have seen any one else attempting this. Thanks

# re: Recipe: Implementing Role Based Security with ASP.NET using Windows Authentication and SQL Server

Friday, July 28, 2006 12:29 AM by ScottGu

Hi Bryan,

There is actually a Roles.GetUsersInRole(roleName) method that you can use to get back a list of all users within a specific role.

Hope this helps,

Scott

# re: Recipe: Implementing Role Based Security with ASP.NET using Windows Authentication and SQL Server

Friday, July 28, 2006 12:32 AM by ScottGu

Hi Darko,

When you registered your new providers, did you clear out the old ones first?  You'll want to-do this by adding a <clear/> directive before your new <add> directive.  Otherwise you'll just add a new provider to the collection and not replace the one that uses SQL Express.

Hope this helps,

Scott

# re: Recipe: Implementing Role Based Security with ASP.NET using Windows Authentication and SQL Server

Friday, July 28, 2006 12:36 AM by ScottGu

Hi Todd,

For totally dynamic layout like you are describing, you might want to pull the layout from the database.  You might want to check out the old ASP.NET 1.1 Portal Starter Kit here: http://www.asp.net/downloads/default11.aspx?tabid=62 for an example of how to-do this.

It doesn't use the new ASP.NET 2.0 features (master pages, roles, and web parts) that will make building this type of application much easier.  But it does include a lot of the basics for how to go after the scenario you want.

Hope this helps,

Scott

# re: Recipe: Implementing Role Based Security with ASP.NET using Windows Authentication and SQL Server

Friday, July 28, 2006 4:13 AM by Darko Konrad
Hi Scott, thx for the swift response. I guess I didn't explain my self very well. Second try: 1. I have 2 databases ( one of them called aspnetservicesdb and configured with the aspnet_reqsql tool) 2. both DBs on the SQL Server machine (not locally) 3. using roleManager and memberhipProvider pointing to the AspNetServicesDB ConnString explicitly 4. added to both providers list 5. App DB can be reached through impersonation (as expected) -- The problem -- IIS is connectiong to the remote SQL Servers aspnetservicesdb with DomainName\MachinName$ account of my DEV machine running VS. Why it is not using impersonation like for the other DB which is located on the very same server like the aspnetservicesdb? Please help me :-) THX a million!!! Darko

# re: Recipe: Implementing Role Based Security with ASP.NET using Windows Authentication and SQL Server

Friday, July 28, 2006 4:48 AM by Darko Konrad
In addition to my last post: I just tested it with a blank new project: first using Cassini (successful) and then using IIS (Windows Authentication only) which was not successful. Why is IIS accessing the remote SQL Server db with other identity (DomainName\MachineName$) than the one which should be impersonated? I mean for the other db it is impersonating?! I am confused... :-(

# re: Recipe: Implementing Role Based Security with ASP.NET using Windows Authentication and SQL Server

Friday, July 28, 2006 9:54 PM by ScottGu

Hi Darko,

The difference you are seeing is that the built-in VS web-server runs using the user-identity of the developer who launched it.  So in this case it is using your login identity to connect to the SQL database.

When ASP.NET runs under IIS, it uses the user-account configured for the worker process it is running within.  For IIS6 that is the account you listed above.  To change this you can go into IIS6 and adjust the application pool user account name.  Alternatively, you can go into SQL Server and allow the standard IIS account access to that particular SQL table.

Hope this helps,

Scott

# re: Recipe: Implementing Role Based Security with ASP.NET using Windows Authentication and SQL Server

Wednesday, August 2, 2006 1:49 AM by ScottGu

Hi Jay,

The above solution actually scales very well.  We have ~400,000 users on the www.asp.net web-site defined within roles using it and it performs very well.

By default roles are requested on each request -- but for an additional performance boost you can configure ASP.NET to cache the role-list as an encrypted http cookie (which enables you to only have to lookup the roles every n seconds -- where n is configurable).

For more granular role permissions you could do one of two approaches:

1) Look into using the AZMan system that Microsoft provides (which allows you to define capabilities underneath each role).

or:

2) Implement your own role-provider that plugs in and implements a sub-role policy system (for example: Administrator.SuperUser).

Hope this helps,

Scott

# re: Recipe: Implementing Role Based Security with ASP.NET using Windows Authentication and SQL Server

Monday, August 14, 2006 12:19 PM by Duncan
Hi Scott, I've downloaded your code and am trying to set it up to use a database on my test server (non local). I've followed the instructions in your 'read me' file and am getting 'Login failed for user 'mydomain\myusername'. Nothing has been entered to the tables in the 'ASPSecurity' db that I created. If I amend the web.config file to use SQL Express it works without problems. Any ideas? Cheers, Duncan

# re: Recipe: Implementing Role Based Security with ASP.NET using Windows Authentication and SQL Server

Monday, August 14, 2006 1:23 PM by ScottGu

Hi Duncan,

Do you have SQL 2000 or SQL 2005 installed?  Are you connecting to it via IIS5 or IIS6?  If so, have you granted the appropriate windows account (ASPNET for IIS5 and Network Service for IIS6) persmission to access the SQL database you created?

Thanks,

Scott

# re: Recipe: Implementing Role Based Security with ASP.NET using Windows Authentication and SQL Server

Wednesday, August 16, 2006 7:55 AM by Duncan
Hi Scott, Thanks for your quick response, apologies for my late reply. I'm using SQL2000 and IIS5. I tried granting my windows account access to the database and the aspnet_* roles and although I can now view the default page in a browser nothing is added to the tables for roles etc. Is there a generic windows account that I should be granting access to rather than my own? I'm sure this is something very staightforward, but I'm a complete novice to this kind of security. Previously I've used a single db user account per web app to allow access to the databse. Thanks, Duncan

# re: Recipe: Implementing Role Based Security with ASP.NET using Windows Authentication and SQL Server

Wednesday, August 16, 2006 10:23 AM by Duncan
Hi Scott, I think I've made some progress. I've moved the code to run from the same server the database resides on. This uses IIS6. I've added IIS_WPG, which contains the Network Service, as a db user. The result is I can view the default page but no roles are added to my database. I have no menu options on the page. Cheers, Duncan

# re: Recipe: Implementing Role Based Security with ASP.NET using Windows Authentication and SQL Server

Wednesday, August 16, 2006 10:56 AM by Duncan
Hi Scott, I've finally got it working. I'd left the connection string I was using to connect to the remote db from my local machine in the config file. Changed this to localhost and its working now! Thanks, Duncan

# re: Recipe: Implementing Role Based Security with ASP.NET using Windows Authentication and SQL Server

Friday, August 25, 2006 12:57 PM by Bibek
I am using the Role Base Security using my local SQL server db(instead of the SQL Express or ASPNETDB.MDF). I am able to create user, roles and assign user with role. I am using sitemap for my menus. I have roles assigned to each item in the site map so that only those user who belong to the role are able to see the menu and links. But I am not able to get the Menu displayed at all. The System.Security.Principal.WindowsIdentity.GetCurrent().Name gives me the name of the current user and exist is in the db I have created and assigned to a role as well. Following is part of my config file. What am I missing? Thanks!

# re: Recipe: Implementing Role Based Security with ASP.NET using Windows Authentication and SQL Server

Friday, August 25, 2006 8:09 PM by ScottGu
Hi Bibek, Have you enabled "security trimming" on the SiteMap provider? This is needed in order for it to filter the nodes by their role status. Hope this helps, Scott

# re: Recipe: Implementing Role Based Security with ASP.NET using Windows Authentication and SQL Server

Tuesday, August 29, 2006 1:26 PM by Bibek
Enabling "securityTrimmingEnabled" did not fix my problem. I enabled it in the web.config file sitemap and also in my .sitemap. I eventually enabled it for each of the node in my .sitemap file but that did not help either. I wrote code to return me values to see my user identity name, and check to see if I belong to the roles and this seems to be working fine giving correct results. I am still looking to see what I am missing.

# re: Recipe: Implementing Role Based Security with ASP.NET using Windows Authentication and SQL Server

Thursday, September 21, 2006 7:03 AM by SRI
Can i get the source code for the above example.if so mail it to srimal_hs@yahoo.com. thanks

# re: Recipe: Implementing Role Based Security with ASP.NET using Windows Authentication and SQL Server

Sunday, September 24, 2006 12:28 AM by Chris

Bibek-

Check your sitemap file to see if you have a siteMapNode element without a URL attribute defined for it.  I tried creating a navigation heirarchy such as having a "Reports" navigation item with many sub-menu items below it.  While setting the sitemap file up this way gave the navigation items a "parent/child" look, turning security trimming on wiped out the entire navigation menu at run-time and it came up blank in the browser.

Personally, I think this is a bug in the provider for the sitemap files...

Chris

# re: Recipe: Implementing Role Based Security with ASP.NET using Windows Authentication and SQL Server

Sunday, September 24, 2006 1:11 PM by ScottGu

Hi Todd,

You can check whether a user is in a given role on any page by writing:

User.IsInRole(rolename)

You could then use this to control the navigation flow of the user.  

Hope this helps,

Scott

# re: Recipe: Implementing Role Based Security with ASP.NET using Windows Authentication and SQL Server

Thursday, September 28, 2006 12:12 PM by Kieran
Hi Scott, I have an asp.net 2 app thats using membership for security. Ive secured an admin folder and set it to only allow people in the admin role. This works fine on localhost and prompts me to login, if correct it lets me in. Ive now moved the app to a remote server and when I login correctly it just continues to prompt me to login again? Is there any possible reasons for this? Thanks!

# re: Recipe: Implementing Role Based Security with ASP.NET using Windows Authentication and SQL Server

Saturday, September 30, 2006 2:09 PM by ScottGu

Hi Kieran,

It might be that you are running into this issue with your configured provider: http://weblogs.asp.net/scottgu/archive/2006/04/22/Always-set-the-_2200_applicationName_2200_-property-when-configuring-ASP.NET-2.0-Membership-and-other-Providers.aspx

Hope this helps,

Scott

# re: Recipe: Implementing Role Based Security with ASP.NET using Windows Authentication and SQL Server

Sunday, November 19, 2006 4:35 PM by ScottGu

Hi Christian,

You shouldn't need to ever remove this name.  It is just a placeholder for you to assign roles.  

Hope this helps,

Scott

# re: Recipe: Implementing Role Based Security with ASP.NET using Windows Authentication and SQL Server

Wednesday, November 29, 2006 4:44 PM by amit

Hey scott,

Thanks for this great explanation. My problem is that I am using asp.net 1.1 , will this technique work for me? If not, what changes can I make to make it work? I know that the 2.0 comes with a role manager built in, but I am using my own custom sql server role tables to handle this and trying to figure if I can use the Microsoft Security Application Block to help me.

# re: Recipe: Implementing Role Based Security with ASP.NET using Windows Authentication and SQL Server

Monday, December 4, 2006 10:40 AM by Melanie Peterson

Hi Scott,

I posted to this thread on 12/1, but it doesn't seem to have made it, so I'm going to try again.  Your article is great; exactly what I'm trying to do.  However, I'm having a little trouble implementing it.  I have a preexisting sql database with my app's data in it, and I want to implement roles so I can use them in a sitemap.  I ran aspnet_regsql to create the tables I need for roles in this database (aspnet_Rules, aspnet_Users, etc). I followed your post's instructions on implementing roles, and I added a variation on your code to my Global.asax to create roles on the fly. However, my aspnet_Users table remains empty, as does aspnet_UsersinRoles. What am I doing wrong? If you need me to email you code, just send me an email address.

Thanks,

Melanie Peterson