I've recently been thinking about database authentication in an ASP.NET application. While this concept is most definitely unoriginal - a great many web applications must perform data access of some sort - it is easy to get confused by the many variations of data access.
To summarize in brief, there are two primary methods for authenticating to a database: Windows Authentication (also called Integrated Security), and Sql Authentication. All SQL Servers support Windows Authentication and it's the natural choice. Enabling Sql Authentication, on the other hand, requires that the server be configured to support it; this can be accomplished either during installation or after the fact. What's more, not all organizations will support Sql Authentication.
Despite the aforementioned, perhaps he simplest technique to authenticate a user in the database is to use Sql Authentication. It requires that 1) a login be created in the SQL Server and mapped to the database and 2) the connection string contain the appropriate User ID and Password parameters. It is the simplest also because the database can be located locally or remotely and the authentication will succeed.
As soon as you use Windows Authentication in a web application the level of difficulty raises, even if only slightly. To properly implement Windows Authentication in a web application, the identity of the process must be determined. For purposes of this article, as it is not directly pertinent and can be quite large in scope, I will refrain from exploring the topic of ASP.NET process identity. Suffice it to say that on a Windows Server 2003/2008 the default identity for an IIS application pool is NT AUTHORITY\NETWORK SERVICE. On other platforms this identity will vary.
ASP.NET Application - Anonymous or Integrated Windows Authentication, No Impersonation
If the requesting user is not being impersonated, IIS will access resources using the identity of the application pool (e.g., NT AUTHORITY\NETWORK SERVICE).
ASP.NET Application - Anonymous Authentication, Impersonation Enabled
If your web application is using anonymous access and the user is being impersonated a couple of things can happen. First, if, in your web.config file, you specify <identity impersonate="true" />, IIS will impersonate the anonymous user specified for your web site (e.g., IUSR_MACHINENAME, IUSR, etc.). A user may be specified in the web.config file as well via <identity impersonate="true" userName="DOMAIN\User" password="xxx" />. In this case, the web server will impersonate the user on the server. NOTE: This user needs, at a minimum, Write access to the \Temporary ASP.NET Files folder.
ASP.NET Application - Integrated Windows Authentication, Impersonation Enabled
This is functionally similar to the previous item except if you use the simple form of the identity element (i.e., <identity impersonate="true" />), the user account that is performing the request is impersonated. This is quite helpful especially if you need to control access to a server-side resource by ACL.
When connecting to a SQL Server database that is local to the web server, that is, it is physically on the same machine, you can grant access to the identity under which the web application is running (ASPNET, NT AUTHORITY\NETWORK SERVICE, IUSR_MACHINENAME, or the impersonated user for instance).
When the database is physically remote, however, care must be taken to properly flow user credentials to remote server. That is, in fact, something of a misnomer. Credentials aren't actually flowing to the remote server, but rather an authentication token. This token is generated on the computer where the user identity is authenticated.
A user token generated on the web server will be able to flow to the remote machine without any extra work. This token is created in the following scenarios:
- Using Basic authentication - the user is actually logged-in on the server.
- Using no impersonation - the website is locally authenticated as ASPNET or NT AUTHORITY\NETWORK SERVICE.
- The impersonation identity is manually set in the web.config's <identity /> element.
NTLM will permit the token a 'single hop' to the remote server. Provided the identity in question has access to the database, the connection will be successfully established, and the requested information returned.
If, on the other hand, the web server is impersonating the requesting user, the solution is not so cut-and-dry. The user's token is created on the client computer and makes a 'single hop' to the web server. When making a request to a remote server a 'double hop' must be performed. NTLM will prohibit the token from being passed to the server and you will encounter an error resembling "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'".
This error may be confusing and disheartening to a developer who has not seen it before. This is often the result of having tested the website on a development machine (which is usually local) - "It works on my machine." Well, it works because the token is created locally and only needs a 'single hop' to get to the database server.
There are a few solutions that may help to address the oft-times confusing and frustrating 'double hop' conundrum.
- Setup Constrained Delegation
Less far-sweeping than full delegation, Kerberos constrained delegation allows tokens to flow against a limited set of services. This option is only available on Windows Server 2003+. To properly implement constrained delegation, you must set up a Service Principal Name (SPN), identifying the service and the machine trusted for delegating the tokens. While simple in principle, I've found this solution to be tempermental and very sensitive. You may seemingly have everything set up correctly and still not get it to work.
- Fall back (a.k.a., revert) to the base IIS process's identity
Having fought the 'double hop' issue more times than I care to admit, and for more hours than are healthy, this idea came to me last week while discussing the 'problem' with a friend and associate at Microsoft. While this idea is not revolutionary nor original, it is definitely useful. This technique allows your website to retain its ability to identify the calling user while deferring to the process's identity (e.g., NT AUTHORITY\NETWORK SERVICE) to access remote databases.
To make this (#2) work we need the ability to 'undo' the impersonation that ASP.NET performs and then reimpersonate when we're finished. Unfortunately, this functionality is not native to the .NET Framework as far as I'm aware - it is, however, accessible via the RevertToSelf() Window API function. In order to encapsulate the logic of reverting to the base process's identity and restoring impersonation, I've created a simple disposable class which is presented below:
/// <summary>
/// Represents a disposable class that, for the lifetime of the object, runs using the
/// underlying identity of the process. This class is useful within an ASP.NET application
/// that is impersonating the caller, but needs to access network or directory resources
/// that would otherwise be prohibited without setting up constrained delegation in Active
/// Directory.
/// </summary>
public sealed class RevertImpersonator : IDisposable {
public RevertImpersonator() {
// acquire the identity of the current user (the user being impersonated)
_userIdentity = WindowsIdentity.GetCurrent();
// revert to the underlying process' identity
// for ASP.NET applications that impersonate, this will be the identity of the IIS process
// (e.g., the identity of the application pool which, by default, is NETWORK SERVICE).
// NOTE: the NETWORK SERVICE account will access network resources as the MACHINE$ account, local resources as NT AUTHORITY\NETWORK SERVICE.
RevertedIdentity = ( 0 != RevertToSelf() );
}
private readonly WindowsIdentity _userIdentity;
[DllImport("advapi32.dll")]
private static extern int RevertToSelf();
~RevertImpersonator() {
restore();
}
/// <summary>
/// Returns whether the user's identity was successfully reverted on initialization.
/// </summary>
public bool RevertedIdentity { get; private set; }
public void Dispose() {
GC.SuppressFinalize(this);
restore();
}
private void restore() {
// re-impersonate the user during cleanup
if ( RevertedIdentity )
_userIdentity.Impersonate();
}
}
Effectively, this class allows you to encapsulate database calls thus:
using ( new RevertImpersonator() ) {
// perform data access here
}
An important note is warranted. As previously mentioned, using this class will revert the identity on the thread to the process's identity. You can set your IIS Application Pool to use a domain account rather than the default NT AUTHORITY\NETWORK SERVICE. Doing so will require that the domain user have access to the database in question. If, however, you decide to use the default, you must be aware of a few items. First, a local database will be accessed with the NT AUTHORITY\NETWORK SERVICE account as expected. A remote database will be accessed with the MACHINE$ account - this is how the NT AUTHORITY\NETWORK SERVICE account is authenticated remotely.