JasonDentler.com

A web site I recently worked on has two major parts. The first is internet-facing and allows customers to create data records. The other is for internal use to allow office staff to alter data created by the customer.

SQL Audit Setup

The database uses MS SQL Server trigger-based audit logs to record data changes with a username and timestamp. They are literally for audit purposes, and not used in the application. Every database table has a matching Audit table with exactly the same fields plus a username, change type, and timestamp field.

Only updates and deletes trigger an entry in the audit log. The data recorded in the log represents the record prior to the change. The username field is defaulted to SYSTEM_USER, a SQL server specific variable that returns the current account name associated with the connection. This can be either a SQL server account or a windows account. Change type is a single character U or D depending on the action being performed. Timestamp is a datetime field defaulted to GETDATE().

In order to associate audit log records with a particular username, all updates and deletes must run in the context of the user.

Authentication and Impersonation

Customers do not log in to the website. There is no authentication. When a customer interacts with the database, we used a generic SQL account specified in the connection string. All customer changes are recorded with the name of this generic account.

When the office staff uses the website, they are required to authenticate with their windows accounts. This account is impersonated all the way to the database so the audit log will record the specific user name.

There are several ways to achieve this impersonation. The option I chose to use is basic authentication. Over SSL (an https website) this is just as secure as any other data. The primary drawback is that users will get the standard, generic popup log in box. Since the username and password are transmitted to the web server, the web server can easily impersonate this user to any resource, including SQL, even on another server.

You can also run Windows authentication. This was referred to as NTLM back in the day. The benefit of this over basic authentication is that Internet Explorer will authenticate with the web server automatically using the current user’s windows credentials. Since this authentication is done with public keys instead of passwords, the web server can’t impersonate the user to resources (including SQL server) on other servers. This means it’s not really a working solution by itself. If you’re running SQL server on your production web server, stop reading now and go fix that.

Fortunately, windows authentication can take advantage of delegated Kerberos authentication. This is difficult to set up and well beyond the scope of this post. Basically, after trading some more public keys around, the SQL server knows that the web server is telling the truth about the current user and agrees to run the SQL statements in the context of that user. Internet Explorer users are still not prompted for their user names and passwords, and it just works. 

Warning: Impersonation, no matter which method you choose, is not recommended. It effectively disables connection pooling, which stops your application from scaling. In fact, this whole post is probably one bad idea after another.

In this particular case, I’m safe. We can’t physically fit more than a dozen “staff” users, and we’ll certainly never get approval to hire even that many. I’ve used this same architecture for hundreds of users before I knew any better – and the hardware was much slower. In short, it’ll work in this case.

Dynamic Connection Strings

This led to another small issue. Customers should log in with a generic SQL account. Staff should log in with their windows accounts.

Jose R. led me to this article on the NHForge wiki from November 2008. Well, things have changed slightly in the last year. Also, I wanted something a little more pluggable.

NHibernate uses a DriverConnectionProvider to supply it with a connection. To alter the connection string as needed, we only need to override the ConnectionString property of this class.

using NHibernate.Connection;

namespace DynamicConnectionStringExample
{
    public class DynamicConnectionProvider : DriverConnectionProvider 
    {

        public static IConnectionStringProvider connectionStringProvider;

        protected override string ConnectionString
        {
            get
            {
                if (null!=connectionStringProvider)
                {
                    return connectionStringProvider.GetConnectionString();
                }
                return base.ConnectionString;
            }
        }

    }
}

Note: The example I show here assumes you are using one of the standard bytecode providers such as NHibernate.Bytecode.Castle. However, you could very easily use an IoC bytecode provider (except Ninject) from uNHAddIns and use constructor dependency injection to inject the IConnectionStringProvider dependency. If not, you’ll need to manually set the ConnectionStringProvider.

To use this, just tell NHibernate about it:

Environment.Properties[Environment.ConnectionProvider] = typeof(DynamicConnectionProvider).AssemblyQualifiedName;

Of course, the “magic” really happens in the IConnectionStringProvider. If we have an authenticated user, GetConnectionString() returns a connection string with Integrated Security = true. If we don’t, we return one with a SQL username and password.