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)?

Kenric

Note the Physical connection errors coincide with the following error: The connection has been dropped because the principal that opened it subsequently assumed a new security context, and then tried to reset the connection under its impersonated security context. This scenario is not supported. See "Impersonation Overview" in Books Online. – Kenric

Will the web application users connect with windows authentication and is the domain kerberos capable? – Maxwell

How about [ab]using another part of the connections parameters e.g. Application Name or Workstation ID (connectionstrings.com/all-sql-server-connection-string-keywords) in conjunction with the App_Name() or Host_Name() functions? Not ideal, but could be an acceptable workaround given the restrictions... – Accommodating

@Filip no, username and password – Kenric

@gvee, I know how to bust the connection pool, that's not the issue. – Kenric

do you have the capacity to replace the user_name() calls with dbo.fn_user_name() for example? – Maxwell

@FilipDeVos maybe, if the solution is convincing enough. – Kenric

@Crisfole May I ask if you found a solution to your problem? We have a very similar situation where we are intercepting queries in Entity Framework and wrapping them with an "EXECUTE AS" pre-query and "REVERT" post-query. The impersonation and queries are working, but we are getting hit with "The connection has been dropped..." errors via email like crazy. Since the queries do work, it seems we could probably ignore/supress the errors, but that doesn't sit well with any of us. – Illconsidered

Nope. I created my own wrapper for getting connections that had the option of bypassing the connection pool for any time I was using execute as – Kenric

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.

Maxwell

a few problems with this solution: 1. It isn't compatible with our VB6 legacy app which I can't change, and can't use the above code. 2. I am not a big fan of executing two separate requests every time I want to execute something... – Kenric

your client server app does not need to change, the fn_user_name() spits out the same as user_name() if no context is set. – Maxwell

that's why I propose option 2 where the context call is prepended to the sql string to execute. I'll try to cook up the class when I have some time tonight. – Maxwell

Man, I sure hope I don't have to maintain this application one day in the future. – Vitelline

Answer 2

I know this is old, however this post is the only useful resource I've found, and I thought I'd share our solution that builds on Filip De Vos answer.

We also have a legacy VB6 app that utilises sp_setapprole (I appreciate this doesn't quite fit the OPs original post). Our .NET components that share the same database (and are essentially part of the application framework) are heavily based on Linq to SQL.

Setting the approle for a datacontext connection proved troublesome, considering the number of times a connection is opened and closed.

We ended up using writing a simple wrapper as suggested above. The only overridden methods are Open() and Close(), which is where the approle is set and unset.

Public Class ManagedConnection
    Inherits Common.DbConnection

    Private mCookie As Byte()
    Private mcnConnection As SqlClient.SqlConnection

    Public Sub New()
        mcnConnection = New SqlClient.SqlConnection()
    End Sub

    Public Sub New(connectionString As String)
        mcnConnection = New SqlClient.SqlConnection(connectionString)
    End Sub

    Public Sub New(connectionString As String, credential As SqlClient.SqlCredential)
        mcnConnection = New SqlClient.SqlConnection(connectionString, credential)
    End Sub

    Public Overrides Property ConnectionString As String
        Get
            Return mcnConnection.ConnectionString
        End Get
        Set(value As String)
            mcnConnection.ConnectionString = value
        End Set
    End Property

    Public Overrides ReadOnly Property Database As String
        Get
            Return mcnConnection.Database
        End Get
    End Property

    Public Overrides ReadOnly Property DataSource As String
        Get
            Return mcnConnection.DataSource
        End Get
    End Property

    Public Overrides ReadOnly Property ServerVersion As String
        Get
            Return mcnConnection.ServerVersion
        End Get
    End Property

    Public Overrides ReadOnly Property State As ConnectionState
        Get
            Return mcnConnection.State
        End Get
    End Property

    Public Overrides Sub ChangeDatabase(databaseName As String)
        mcnConnection.ChangeDatabase(databaseName)
    End Sub

    Public Overrides Sub Close()

        Using cm As New SqlClient.SqlCommand("sp_unsetapprole")
            cm.Connection = mcnConnection
            cm.CommandType = CommandType.StoredProcedure
            cm.Parameters.Add("@cookie", SqlDbType.VarBinary, 8000).Value = mCookie

            cm.ExecuteNonQuery()
        End Using

        mcnConnection.Close()
    End Sub

    Public Overrides Sub Open()
        mcnConnection.Open()

        Using cm As New SqlClient.SqlCommand("sp_setapprole")
            cm.Connection = mcnConnection
            cm.CommandType = CommandType.StoredProcedure
            cm.Parameters.Add("@rolename", SqlDbType.NVarChar, 128).Value = "UID"
            cm.Parameters.Add("@password", SqlDbType.NVarChar, 128)Value = "PWD"
            cm.Parameters.Add("@fCreateCookie", SqlDbType.Bit).Value = True
            cm.Parameters.Add("@cookie", SqlDbType.VarBinary, 8000).Direction = ParameterDirection.InputOutput
            cm.ExecuteNonQuery()

            mCookie = cm.Parameters("@cookie").Value
        End Using
    End Sub

    Protected Overrides Function BeginDbTransaction(isolationLevel As IsolationLevel) As DbTransaction
        Return mcnConnection.BeginTransaction(isolationLevel)
    End Function

    Protected Overrides Function CreateDbCommand() As DbCommand
        Return mcnConnection.CreateCommand()
    End Function
End Class

Before:

Using dc As New SystemOptionDataContext(sConnectionString)
    intOption= dc.GetIntegerValue("SomeDatabaseOption")
End Using

After:

Using dc As New SystemOptionDataContext(New ManagedConnection(strConnectionString))
    intOption= dc.GetIntegerValue("SomeDatabaseOption")
End Using

Hope this helps others.

Pampa