SQL Server Impersonation and Connection Pooling

Question

I've been given the task of writing an web interface for a legacy database we have where all users have database accounts and are assigned roles accordingly (we have triggers all over the place recording when users do certain things, all based on user_name()).

In order to use anything remotely modern and to avoid storing the user's password in plain text I'm connecting w/ an App-level account that has impersonation privileges for each user, and I'm trying to run Execute As User=@username and Revert to set and reset the execution context before and after running any SQL.

Unfortunately, the connection pooling's reset_connection call is mucking w/ my Connection, and it winds up throwing some nasty errors about Physical connection being invalid...

I can get around this error by not using the connection pool. But then my application user needs an insane amount of privileges to actually perform the impersonation. Furthermore, killing connection pooling is a bummer...

How can I do this without sacrificing security or performance? Keep in mind I can't change the fact that my users have database logins, and I'm really not excited about storing user passwords in a retrievable manner. Is my only option bypassing the connection pool so I can impersonate (and using an sa user so I have sufficient permissions to actually impersonate someone)?

Answer 1

To implement a kind of "fake" delegation without huge changes in application/database code I propose using context_info() to transport the current user to the database and replace the calls to user_name() with calls to dbo.fn_user_name().

An example on how to build up this solution

Create fn_user_name() function

I would create a function fn_user_name which will extract the user name from the context_info() on the connection or return user_name() when there is no context info available. note that the connection context is a 128 byte binary. Anything you put on there will be padded with zero characters, to work around this I stuff the values with whitespace.

create function dbo.fn_user_name()
returns sysname
as
begin
    declare @user sysname = rtrim(convert(nvarchar(64), context_info()))
    if @user is null 
        return user_name()
    return @user
end
go

Now you find replace all calls to user_name() in your code and replace them with this function.

Embed the context in your db calls in .net

There are 2 options here. Or you create your own SqlConnection class, or you create a factory method which will return an open sqlconnection like shown below. The factory method has as problem that every query you run will be 2 db calls. It is the least code to write though.

    public SqlConnection CreateConnection(string connectionString, string user)
    {
        var conn = new SqlConnection(connectionString);
        using (var cmd = new SqlCommand(
            @"declare @a varbinary(128) = convert(varbinary(128), @user + replicate(N' ', 64 - len(@user)))
              set context_info @a", conn))
        {
            cmd.Parameters.Add("@user", SqlDbType.NVarChar, 64).Value = user;
            conn.Open();
            cmd.ExecuteNonQuery();
        }
        return conn;
    }

you would use this as:

using(var conn = CreateConnection(connectionString, user))
{
   var cmd = new SqlCommand("select 1", conn);
   return conn.ExecuteScalar()
}

For the alternate version of SqlConnection you will need to overload DbConnection and implement all methods of SqlConnection. The execute methods will prepend the query below and pass in the username as extra parameter.

declare @a varbinary(128) = convert(varbinary(128), @user + replicate(N' ', 64 - len(@user)))
set context_info @a 

that class would then be used as:

using(var conn = new SqlContextInfoConnection(connectionString, user))
{
   var cmd = new SqlCommand("select 1", conn);
   conn.open;
   return conn.ExecuteScalar()
}

I would personally implement option 2 as it would be closer to the way a normal SqlConnection works.