Brian Vander Plaats



30 Jan 2017

Generate Queryable List of Names from Excel

Here’s a handy excel formula to remember:

=CONCATENATE(CHAR(39),A2, " ", B2, CHAR(39), CHAR(44))

list of names excel

For quickly generating a list of queryable list of names:

Select * from dbo.Person
where Name in
'Bill Gates',
'Steve Jobs',
'Larry Ellison',
'Steve Case',
'Jon Carmack',
'Elon Musk',
'Sergey Brin'

This can be handy if you get spreadsheets from people and want to quickly filter results in the database. I did this today for someone who wanted a list of user names for about 40 people, far too many to spend half an hour typing these in!

27 Jan 2017

ADFS Authentication - Adding to Existing Site

The Visual Studio new project template is a handy wizard for setting up AD FS on a new site, but the wizard isn’t available for existing sites. Additionally, the wizard forces you to include ASP.NET MVC components, even if you just need to set up a Web API back-end. This post shows you how to do this manually.

  1. Make sure SSL is enabled in your project.

  2. Set The SSL URL to the one provided by your AD FS administrator, or configure AD FS to use your website url/port. e.g. https://localhost:44300/

  3. Add required packages (search for these on NuGet) manual adfs nuget
    • Microsoft.Owin
    • Microsoft.Owin.Security
    • Microsoft.Owin.Security.Cookies
    • Microsoft.Owin.Security.WsFederation
    • Microsoft.Owin.Host.SystemWeb
  4. Add AD FS Metadata to Web.config
    <add key="ida:ADFSMetadata" value="" />
    <add key="ida:Wtrealm" value="https://localhost:44300/" />
  1. Add a Startup.cs file/class if it does not already exist

  2. Add configuration hooks to Startup.cs. Note that if you have not added Microsoft.Owin.Host.SystemWeb to your packages Startup.Configuration() will not fire.

public void Configuration(IAppBuilder app)

private void ConfigureAuth(IAppBuilder app)
    app.UseCookieAuthentication(new CookieAuthenticationOptions());
        new WsFederationAuthenticationOptions
            Wtrealm = ConfigurationManager.AppSettings["ida:Wtrealm"],
            MetadataAddress = ConfigurationManager.AppSettings["ida:ADFSMetadata"]
  1. Add Authorization to controllers / routes.
    • Method 1: Set global filter

        public static void Register(HttpConfiguration config)
            // Authorize all api routes
            config.Filters.Add(new AuthorizeAttribute());
    • Method 2: Add Authorize Attribute to controller

        public class PersonController : ApiController
  2. Build and Run your app. Your routes requiring authorization should now redirect you to the AD FS login page

manual adfs signin

19 Jan 2017

ADFS Authentication Adding Custom Claims

While user authentication is a key component of AD FS, the returned user claims are powerful tools for client applications.

  • Common user information (Name, login, email) can be retrieved without code duplication in multiple applications
  • Security roles can be added to the claims token, reducing roundtrips or querying security information from a database during application lifetimes
  • Unique system fields / tokens / identifiers that would normally be stored in some global variable or hidden fields

There are two ways to add to the available claims:

  1. Configure AD FS to send additional claims - this will be shown in a future post
  2. Dynamically add claims during user sign-on

Before going into details here, a few high level points to keep in mind:

  1. Claim tokens are shared between all sites in a subdomain e.g
  2. Claim tokens can expire (based on AD FS settings), or be removed by the user logging out.
  3. Claims from the AD FS server can be removed at any time.
  4. Changes made to the claims will not affect users that have a current claims token.

Thus, your application should never assume that a claim exists.

Adding Roles to claims

An excellent usage of claims information is populating the application security roles the user has access to. This is information that will be checked frequently during the applications lifetime, and querying a security database on each access is inefficient.

To begin, you will need a security database with users/roles/groups etc. At my company we use the venerable ASPNETDB database that has been part of the membership system for a long time.

Adding the claims is done in the ConfigureAuth() method. In a vanilla configuration, this looks as follows:


app.UseCookieAuthentication(new CookieAuthenticationOptions());

    new WsFederationAuthenticationOptions
        Wtrealm = realm,
        MetadataAddress = adfsMetadata

Adding claims is done by specifying additional items in WsFederationAuthenticationOptions

new WsFederationAuthenticationOptions
    Wtrealm = realm,
    MetadataAddress = adfsMetadata,
    Notifications = new WsFederationAuthenticationNotifications
        SecurityTokenValidated = context =>
            string accountName = "";
            foreach (var claim in context.AuthenticationTicket.Identity.Claims)
                if (claim.Type == ClaimTypes.Upn)
                    accountName = claim.Value;

            //Query roles for user from security database
            string[] stringRoles = { "TestRole1", "TestRole2", "TestRole3", "TestRole4" };
            var roles = stringRoles.Select(s => new { RoleName = s });

            //loop through returned roles, and add
            foreach (var role in roles)
                                                    new Claim(ClaimTypes.Role, role.RoleName));
            return Task.FromResult(0);


Basically we specify an event handler to fire as soon as the claims token exists. Note while this is configured in startup.auth.cs, this code only runs when the user signs in. This means that if you change which roles the user has in the database, or in Active Directory, the user will not see these roles until they logout, and signs in again.

The first step is getting the user identity we will use to retreive user roles - the user name is a good option here, but you could use a number of claims (again, based on what the AD FS adminstrator configured)

  • nameidentfier - brianvp
  • upn -
  • emailaddress -

Next, you will take your chosen identifier and query your security database.

Then, iterate through these roles, and add to the claims collection using the AddClaim() method. Make sure to specify ClaimTypes.Role. The actual role name is simply a string. Note that if you specify a role twice, it will be added twice. This should not be a problem with multiple applications, as again, this handler does not fire on app startup, only during the sign-in process. Once the user is signed in to an application on a domain, they will not need to sign in to other applications on the domain.

Using Roles

Now that the roles are added, you need to configure your application to use them. The simplest way is to combine roles into [Authorize] tags on your controllers. For example, you could lock down your editor pages by placing the role over create/edit controller actions, but not over the view actions:

public ActionResult Details(int? id)

[Authorize(Roles = "ModelEditorRole")]
public ActionResult Create( Model model)

[Authorize(Roles = "ModelEditorRole")]
public ActionResult Edit(int? id)

Another way is to look at the user’s current roles while performing an action inside a method. This can be done by iterating through the user’s claims:

bool inTestRole4 = false;
var claims = ((ClaimsIdentity)Thread.CurrentPrincipal.Identity).Claims;
foreach (var claim in claims)
    if (claim.Type == ClaimTypes.Role && claim.Value == "TestRole4")
        inTestRole4 = true;

Or, even easier, you can use the simple check:



Adding custom claim types

Adding a custom claim is basically the same as adding a role claim. Instead of specifying ClaimTypes.Role, you give it your own name / value:

SecurityTokenValidated = context =>
    var accountName = context.AuthenticationTicket.Identity.Claims.Where(x => x.Type == ClaimTypes.Upn).FirstOrDefault().Value;

    //do something to retrieve appropriate value for current user
    int CompanyUserID = 4000333;

    context.AuthenticationTicket.Identity.AddClaim(new Claim("CompanyUserID", CompanyUserID.ToString()));

    return Task.FromResult(0);

Again, a good candidate for these claims are broad, static bits of information that will not change, or at least the frequency of change should be much longer than the typical claims token expiration period (as the new value would not be received until next user login).

18 Jan 2017

ADFS Authentication Using Visual Studio Wizard

This post demonstrates how to set up a new ASP.NET MVC project using AD FS. Before you can do this, you need to have an AD FS Server up and running. In my testing, I used an on-network AD FS Server, but a cloud / azure AD FS option exists as well (but I haven’t worked with at this point).

You will need a few pieces of information from your AD FS administrator before proceeding:

  • Metadata document URL e.g.
  • URI to identify your application. For local development you can use https://localhost:44300/, but this must be configured on the AD FS side as well. If you specify something else it will not work!

Setting up the Project

  1. Create a new project using ASP.NET Web Application template
  2. Check the MVC Template, and optionally click on the Web API checkbox visual studio new project templates adfs
  3. Click the “Change Authentication” box.
  4. Change the options to “Work and School Accounts”
  5. Select On-Premises and enter the metadata from your adminstrator and click OK visual studio new project adfs metadata
  6. Click OK to create the rest of your Project

Open up your project settings, and change the SSL URL to match the one from your AD FS administrator visual studio project url

By default, your entire project requires authentication, so on your first build & run, you will immediately see the login screen. To see the home page before logging in, remove the [Authorize] filter from HomeController

Trying out the AD FS components

Build and run your project. You should see the following:

new adfs project sign in

click sign in - you will be redirected to your AD FS Server’s login page. Note that this is not your website

new adfs project adfs login

Once you sign in, you will automatically redirect back to your sample website. Note that if any metadata is misconfigured (on the AD FS Server or in your project), you will start to see problems here.

new adfs project signed in

You are now signed in until you explicitly sign out, or your claims token expires (claim expiration is controlled by your server administrator). The claim token itself is stored in a cookie, and sent with each request to the server

new adfs project cookie

Click Sign Out - you will be directed to the sign out page on your AD FS Server, and then immediately redirect back to your website. This does a few things:

  1. invalidates the claims token on the AD FS Server by calling:
  2. Removes the ASP.NET Cookie from your browser

new adfs project signed out

Accessing the Claims Token

You can access your claims token by casting Thread.CurrentPrincipal.Identity to a ClaimsIdentity and then accessing the Claims collection.


 public ActionResult Index()
    ViewBag.ClaimsIdentity = Thread.CurrentPrincipal.Identity;

    return View();


<h3>Logged in User Claims:</h3>
<table class="table table-condensed">
        <th>Claim Type</th>
        <th>Claim Value</th>
        <th>Value Type</th>
        <th>Subject Name</th>
        <th>Issuer Name</th>
    @foreach (System.Security.Claims.Claim claim in ViewBag.ClaimsIdentity.Claims)

new adfs project user claims


That’s it! You now have a site that you can lock down to authenticated users only. Simply add [Authorize] tags to the necessary feature controllers, and don’t forget to secure all your api paths! To add role-based security for additional granularity, you can add roles to the claims token itself, which I will go over in a future post.

17 Jan 2017

Website Authentication using ADFS Overview

This is the first in a series of posts going over the use of Active Directory Federation Services (AD FS). For roughly the past year, I’ve been exploring authentication options for an external website project at work. Previously we focused on using Windows Authentication, but that only really works for Intranet applications, and not so well or at all on mobile / tablet environments. Since this application is only for employees, I wanted to use existing Active Directory resources. I briefly explored oAuth as an option, but since we would not be using google/facebook etc for credentials, setting up our own oAuth provider seemed to be as much or more work than utilizing AD FS. Specifically my design goals were:

  • Implement Single Sign-on. Users should not have to log into multiple applications / minimize number of logins
  • Use existing domain accounts - Users do not need another login to manage
  • Do not allow the application to access or store user credentials / secrets - If you are going to use a common credential between websites, no individual website should have access to these credentials directly. At best, each site would need to re-implement security features, and at worst, one site failing to implement security properly exposes all the sites.
  • Work well for Internet and Intranet applications. While Windows Authentication worked for us in the past, it doesn’t work well in a post-windows pc world.

Early last year, I created two demo projects, one using oAuth, and the other using AD FS. Both were successful, but the oAuth solution is using google, so after reviewing with my other developers we decided to use AD FS. At present, we have two live applications using AD FS, and so far has worked out really well. Below are the demo projects for reference:

The rest of this post describes key points of the AD FS architecture

AD FS Overview

Active Directory Federation Services is a service that allows sharing identity information between “trusted” partners, called a “federation”. At a high level, it allows a website to delegate authentication to a trusted service, and accept a “claim” from this service on the user’s behalf to make authorization decisions. For example:

  • John Doe wants to access the corporate payroll site
  • The payroll site requires users to login in (obviously)
  • When John hits the payroll site, he is not authenticated, so the payroll site redirects John to the AD FS login page
  • John enters his credentials (user name and password) to the AD FS login site.
  • The AD FS site verifies the credentials - if valid, it generates a “claims token”, containing certain information about John. This typically includes his username (johnd), full name, “John Doe”, email, “”, and any other property the AD FS service is configured to send.
  • AD FS redirects the user back to the payroll site, sending along the claims token
  • The payroll site now assumes that the original user is John Doe, and presents resources John Doe is authorized to use.

Additionally, the claims token is typically stored in a cookie, so if John closes his browser and reloads the payroll site, it can use the existing claims token without requring John to re-log in to AD FS.


Some key terminology:

  • Federation - A group of two or more independant sites / services, that are in a special trust relationship. With the proper configuration, theoretically any service could be added to the exising AD Federation. Much of the work required to set up AD FS is managaging this federation, both on the AD FS service, and the website.
  • Account Federation Server - Issues the claims tokens to users based on authenticating that user, basically the AD FS server
  • Relying Party - the website using the claim for authenticating & authorizing the user
  • Single Sign-on / SSO - a user authentication mechanism that allows a user to use one login to access multiple applications
  • Claim - A statement about the user. There are three types of claims - Identity Claims, Group Claims, and Custom Claims
    • An identity claim is basic information about the user e.g. username
    • A group claim is information about which domain groups the user belongs to e.g. PayrollUsers
    • a custom claim is a key/value pair that can be used by applications. For example, we use a corporate user identity code that is standard between all web applications.


Below is a simplified view of what my current AD FS system looks like

ADFS Architecture

  • The AD FS Federation server is a Windows 2012 R2 Instance responsible for:
    • Registering each Relying Party / Web Server / Website
    • defining what claims metdata is in in the claims token
    • Provides a login endpoint for users to enter credentials into
    • Provides a signout endpoint to clear the claims token
    • Initially we used a 2008 R2 Instance, but upgraded to a 2012 R2 instance. The configurations are largely the same, but one key improvement is the ability customize the user login page (the 2008 R2 page looks terrible on mobile devices)
  • The Web Servers are Windows Server (2008 R2 - 2012 R2) instances which host one or more websites configured to used AD FS
    • The websites are built with ASP.NET / MVC / Web API
    • use Owin middleware for communicating with AD FS
      • redirecting the user to the AD FS Server to login
      • redirecting the user to the AD FS Server to sign out
      • Processing the Claims Token / making the claim available to the web application
    • contain AD FS endpoint / configuration metadata
  • The User Instances (PCs / tablets / mobile) access the websites
    • Upon hitting the Web Server instance, user is redirected to the AD FS Server
    • Upon successful login, the claims token is stored in a cookie in the user’s browser.
    • The cookie is read by the website after the AD FS Server redirects the user back to the website. One of the most important configurations in AD FS is specifing the proper redirect endpoints.
    • The website will continue to use the browser cookie until the user signs out, or the cookie expires.
    • Currently we have a cookie expiration of 24 hours, which will force the user to log in at most once per day.