Monday, November 3, 2014

IIS7 Mixed Windows and Forms Authentication

I recently undertook a project with the following requirements:
- an ASP .NET website hosted on IIS 7.5
- Windows authentication for users logged in to the organisation's internal domain (single sign-on)
- forms (user/password) authentication for anonymous public internet users

While simple enough in theory, it seems that this scenario is officially unsupported by Microsoft.

With Google searches yielding a mix of results, mostly for a mix of IIS versions and a variety of approaches, I was able to finally arrive at a solution. In the process, I learnt a lot about the ASP .NET/IIS authentication system.
Because the most relevant articles on StackOverflow come to the conclusion that this kind of Mixed Mode Authentication is not possible, I thought I'd write this post.

Firstly, I'd like to go over some of the principles that I found applied to this process.
Then I'll sketch the solution I used.
If you'd like to save time, I also offer a complete C# skeleton solution for download.

IIS Versions and Settings

Firstly, the authentication model fundamentally changed at IIS 7. This solution deals only with IIS 7+ 'Integrated Pipeline' mode authentication.
Earlier IIS versions or IIS 7+ running in 'classic' pipeline mode work in a different way and this post is not relevant to that situation.

In IIS 7 or 7.5, three authentication types need to be selected: Windows, Forms and Anonymous. You'll get a warning from IIS about mixing challenge and redirect authentication types, but you can ignore it.

Forms Authentication Overview

Let's cover a few characteristics of Forms Authentication:
- authentication is recorded and maintained by use of a cookie (except in very rare case not dealt with here)
- Forms Authentication safeguards access to the site: on initial request by an unauthenticated user, a redirection occurs to the 'login' page specified in the web.config, with the original request URL appended to the login page URL in the querystring (this is why it's a 'redirect' authentication type)
- if Forms Authentication is enabled, successful Windows Authentication does not prevent the forms-auth redirect from occurring: it is necessary for code to create a valid forms-authentication cookie before the site can be accessed
- specific folders or pages of the web site can be opened up to anonymous users, and can then be accessed without triggering the redirect mechanism

In the main <system .web=""> section of the web.config, the <authorization> section specifies the access for the whole site:
<authorization>
  <deny users="?">
    <allow users="*">
    </allow>
  </deny>
</authorization>
'?' represents anonymous users, and '*' all users (authenticated and non-authenticated/anonymous), so denying all anonymous users and then allowing all users is equivalent to allowing only authenticated users.

Then outside the main <system .web=""> section (ie. in the top level <configuration> section), we add authorization settings for specific pages or folders
<location path="css">
  <system .web="">
    <authorization>
      <allow users="?">
      </allow>
    </authorization>
  </system>
</location>
<location path="AccountRecover.aspx">
  <system .web="">
    <authorization>
      <allow users="?">
      </allow>
    </authorization>
  </system>
</location>
The first gives anonymous access to the 'css' subfolder of the root folder, and the second gives anonymous access to the 'AccountRecover.aspx' page.
It is common practice in ASP .NET to give anonymous access to 'content' folders such as image, css and js folders since a request to the Login page will often refer to images or css files necessary to deliver the look and feel of the site prior to a user logging in. There is rarely any security issue giving anonymous users access to these folders.
The AccountRecover.aspx page needs to be given anonymous access because, by definition, the user won't be logged in if they have forgotten their details and wish to recover their account. There are a few other pages that need this access also.

Windows Authentication Overview

- Windows authentication, if selected by itself, normally triggers IIS itself (not ASP .NET) to issue a HTTP 401 challenge on first page request for an unauthenticated user
- if Anonymous authentication is enabled, then authentication defaults to anonymous on first page request, and no Windows auth 401 challenge occurs. However, the web server may manually issue a 401 challenge at any point after this
- The 401 challenge is handled by the browser typically like this (different browsers can vary):
  (a) if the user is already logged in to a Windows domain, then the challenge is usually answered invisibly by the browser, providing the logged on credentials.
  (b) if the user is not logged on to a Windows domain, then a user name and password window will be displayed by the browser, inviting the user to log on to such a domain
  (c) the logon credentials are returned to the web server and if they pass authentication, the user is now logged in. If they fail then the browser displays the 401 Not Authorized error. The user can try again by navigating 'back'.

Authentication Settings in Asp .NET

A range of settings are mentioned by the various articles on authentication:

System.Security.Principal.WindowsIdentity.GetCurrent().Name

This is the identity of the IIS worker process.
It may be the default system/IIS process, or a service or user account if the application pool has been assigned to a particular account, or it may even be a dynamic user account if Identity Impersonation has been turned on.
However this is an IIS artifact, and nothing to do with us in the current scenario, so we will ignore it.

Request.ServerVariables["LOGON_USER"]

This is cited by various articles out there as the logged on Windows user. Perhaps this was relevant before IIS 7, but now it appears to echo Page.User.Identity.Name as below

Page.User.Identity.Name

If logged in via Forms Authentication, this contains the username used to log in.
If authenticated by Windows, it contains the name (including the domain) of the Windows identity used to answer the 401 challenge.


Issues Mixing Windows and Forms Authentication

There appears to be only a single successful strategy to mix the two types of authentication, and that is to use forms authentication as the basis for the login system, but also allow the user to trigger and respond to a 401 challenge, programmatically creating a forms authentication ticket if successful.

The issues with this are the following:

- Page.User.Identity.Name returns different values (as above) depending on what type of authentication is active. If Windows authentication is active, it can't be turned off except by closing and reopening the browser. Forms authentication is only active if Windows authentication is not.
For this reason every page in the application should be able to identify a user given either their Forms Authenticated username, or by their Windows credentials.
In particular, it should not be possible for one user to have a Forms username that is the same as another user's Windows credentials. Luckily, Windows credentials appear to always contain a domain and a backslash. The presence of a backslash in the username is a reasonable test for the type of authentication that is active.

- Windows authentication often seems to drop out mid-session, falling back to into forms mode using the generated forms auth cookie. I'm just warning about this; it's not a problem - unless relying on some aspect of one of the the types of authentication.

- the pre-built ASP .NET forms authentication and authorization management controls work fine for forms authenticated users, but choke for Windows authenticated users because they try to use the authenticated username (which is the Windows username) to look up the forms-based user records. It is possible to find the equivalent forms user name and programmatically feed it in to these controls to emulate forms auth when using Windows auth. For example, with the <asp:changepassword> control, the 'UserName' property allows us to do this

Putting It All Together

Given that most users requiring Windows authentication will already be logged in to their domain, the most seamless authentication result would be if we could silently test for successful Windows authentication immediately after the Forms Authentication redirection to the Login page, and if successful, create the Forms Authentication ticket and proceed to the originally requested URL.

We can trigger immediate Windows Authentication by sending the 401 challenge on first entry to the Login page, but the problem lies in what occurs if the Windows login attempt fails.
Remember that once the 401 challenge has been sent, things proceed smoothly if credentials are accepted, but if they fail the browser will bring up a 401 'Not Authenticated' error for the page where the attempt failed and block further access to the site. The user could no longer access the Login page to attempt forms-based user/password login.

Give this problem, the best compromise is to leave the Login page as a purely forms-based page, and offer a button to trigger windows-based authentication only for those who want it. If windows authentication fails, clicking 'back' will go back to the login page, or if totally blocked, the browser can be closed and re-opened.

Another logical approach would be to construct two separate entry pages, one for windows authentication users and one for forms authentication users. This was tested, but does not operate well within the redirect based authentication paradigm, and I could not reliably prevent the Windows Authentication URL from being redirected to the Forms redirect URL under some conditions, even if the page was added to the Forms Authentication anonymous access list as shown earlier.
This option was therefore discarded.

In summary, the key points are:
- the Windows login credentials for a user account should be stored as an additional column in the ASP .NET Membership provider user record (thus linking it to a Forms based user)
- the login screen should be forms based, and contain a button to trigger Windows login
- the Windows login button should respond with an HTTP 401 response challenge
- the login page and all other applications should be able to identify a Forms user given either their Forms Authenticated username, or by their Windows credentials. It must be ensured that Forms and Windows usernames are distinct and unique.
- where a user has successfully Windows Authenticated, but the credentials are not recognised, a separate linking page may be created to allow the user to log in using their Forms based username and password, and for the Windows credential to be saved against the Forms user database record.

final note!
I found this StackOverflow question.
Has anyone tried this approach? I note above that I couldn't get the two-pages approach to work because of the forms redirect, however I didn't know about the ability to specify authentication mode per-page. That could be an alternative.

Sample Application

At arrow-of-time.com, I am offering a complete Visual Studio C# skeleton solution for download. This solution contains the ASP .NET pages and classes to achieve all of the discussed behaviour, and detailed deployment instructions.


Varbinary(MAX), BLOBs and linked MSSQL tables in Microsoft Access

It is time to catalogue more of the crimes against humanity that is the evolution of Microsoft Access.
I've had document storage for OLE object fields in MS Access linked tables working fine for a while now, so swapping to SQL Server linked tables shouldn't be a problem, right ?
Wrong.
I started off trying to solve a seemingly innocent error message that occurred only when storing a document over a certain size, about half a megabyte. This snowballed into most of a weekend taking a tour through the entire history and inner workings of linked SQL tables.
I present the results here in the hope that it may save others.

(1) Overview of the MAX types


In MSSQL2005, Microsoft introduced the varchar(MAX), nvarchar(MAX) and varbinary(MAX) types. These replaced text, ntext and image. The advantages were that the new text types could be operated on with LIKE, LEFT and other text functions, and could be used in sorting, and that all the new types were stored in the database more efficiently.
The old text, ntext and image were listed as deprecated, and still have warnings that they will be removed completely in a future version of MS SQL Server, although they are still present in MSSQL2014.

MSSQL2000 did not have these MAX types, and was the last version in which text, ntext and image were not deprecated.

(2) Overview of MS Access Linked SQL Server Tables


Linked tables with an MSSQL back end in MS Access always use the ODBC driver, and always use DAO, which is the Access native data API. The DAO tabledef object stores the ODBC connection string in its .connect property.
There is the option to use an ODBC DSN to make the connection, in which case the connection string holds the name of the DSN, but not the server, database name or authentication details, since they are stored in the DSN. The table linker wizard always uses DSN based connections.
The connection string looks something like this:
ODBC;DSN=MyDSN;DATABASE=MyDB;Trusted_Connection=Yes

Another option is the DSNless connection, which specifies the server and database directly. The table's connection strings must be set using code, something like:
ODBC;DRIVER=SQL Server;SERVER=TESTSERVER\SQL2008EXPRESS;Trusted_Connection=Yes;DATABASE=MyDb;QuotedId=no;

(3) ODBC Drivers


There are essentially three ODBC drivers, which must be either chosen when creating a DSN, or specified as the DRIVER parameter in a DSNless connection:
a. SQL Server: the historical driver, released prior to 2005
b. SQL Native Client: the driver released with SQL2005
c. SQL Server Native Client 10.0: the version-specific driver for SQL Server. Other versions include 11.0, 12.0, etc.

You might expect because that the 'SQL Server' ODBC driver came out before MSSQL2005 existed, it might not handle the MAX datatypes gracefully, and you'd be correct.
What was surprising was that none of the other drivers handle it gracefully either.
Also, because MS Access 2003 came out before MSSQL2005 existed, we might also expect it not to work even with the later drivers (although Access 2003 SP3 added a lot of back-patched for things like this and DID come out after 2005).

However, I can confirm that MS Access 2007 and 2003, none of these drivers handle the MAX types correctly.

I suspect that this may be an Access version issue, or OS version issue, or driver version issue, but I don't have several days to go down that particular rabbit hole.

(4) Editing large data in MS Access


There are is a well documented way to write binary data to a back end table in MS Access. Essentially, a DAO or ADO recordset is opened and the .AppendChunk method is used to append byte arrays of data until the whole binary image is written.
Generally a 'chunk size' is specified and the image is broken up and written in chunks of that size, with a final leftover smaller chunk at the end or start.

However editing varchar(MAX) columns will almost always be through a table bound to a form.

As mentioned, writing to linked tables, whether an MSSQL back end or MS Access back end, and whether using DAO or ADO, ultimately uses DAO and the ODBC drivers because that's the base mechanism for linked tables.
This applies to all linked tables bound to forms.

The only way around the ODBC drivers is to open an ADO connection using OLEDB direct to the back end MSSQL database. This is by necessity a code-based activity. It cannot be used for bound data.

(5) Putting it all together


Now that we have had the overview discussion, we can proceed with the actual problem

Point 1: The Old DataTypes work Fine


Sticking with textntext and image appears to be the best option.
While this solution is flagged as NOT RECOMMENDED anywhere you google it, for reasons discussed earlier, it actually appears to be the only fully supported and tacitly recommended solution.
For example, the upsizing wizard has an option for this, and it's recommended by Microsoft to choose it.

So simply, if you want large data types in SQL Server to work across all scenarios for linked tables, use the old data types.

Point 2: The ODBC Drivers for the new MAX datatypes don't work at all


Using the 'SQL Server' ODBC driver, the varbinary(MAX) column linked as an OLE field in MS Access 2003 and 2007, but trying to insert a BLOB of more than about half a megabyte gave me the following:
Error: 7125 - ODBC Error:[Microsoft][ODBC SQL Server Driver][SQL Server]The text, ntext, or image pointer value conflicts with the column name specified. (in xf_Test_RWBlob:TestBlobRW)
Error: 0 - ODBC Error:[Microsoft][ODBC SQL Server Driver]Warning: Partial insert/update. The insert/update of a text or image column(s) did not succeed. (in xf_Test_RWBlob:TestBlobRW)

Using either of the 'Native' ODBC drivers, the varbinary(MAX) column linked as a fixed binary(255) field in MS Access 2003, and as an OLE object in MS Access 2007, but either Access version still gave me:
Error: 0 - ODBC Error:[Microsoft][SQL Native Client]String data, right truncation (in xf_Test_RWBlob:TestBlobRW_DAO)

As I said earlier, this may be an Access version issue, or OS version issue, or driver version issue. But long story short, I couldn't get it to work reliably, or, in fact, at all.

Point 3: The Workarounds are Partial


For just the case of the BLOB, it is possible to open an ADO connection in code using OLEDB direct to the back end MSSQL database, using a connection like:
Provider=SQLOLEDB;Data Source=TESTSERVER\SQL2008EXPRESS;Initial Catalog=MyDB;Integrated Security=SSPI;

This works. 
However this doesn't help us with bound linked tables containing nvarchar(MAX) and varchar(MAX) columns. They'll still throw errors if we add too much text to the control.
This would be workable if editing was done by unbound controls and data was saved with an ADO connection.

Summary


We are far better off sticking with the old, deprecated types. The alternatives are at best a messy, and at worst a massive, workaround.
Thanks, Microsoft, for once again breaking everything and leaving us to pick up the pieces.
I'd love it if someone proves me wrong here, and tells me how to do it properly. But I don't think that's going to happen.

Final Note


After writing this, I kept getting errors in MS Access 2003 on a table with an nText and an Image column using the 'SQL Server' driver. Eventually I discovered that if I moved the nText column to occur after the Image column in the table, the error disappeared. Then I tried using the later drivers. This also fixed the problem.
I have a motive for going with the old driver: many of my clients don't have the newer one installed, whereas the old one is always there. If I can make it work, it's less headaches for everyone.
Also, you'll probably laugh, but I still use Access 2003 for all my development. It's the last useable version, IMO. The newer version double the amount of clicks necessary to do anything. I can't stand them. I suppose one day I'll have to update. It's the rare issues like this that will probably force my hand.