Entity Framework 5 - Implementing SQL Server "Execute As User"

Question

I am writing a database application using Visual Studio 2012 with Entity Framework 5 and SQL Server 2008. I would like Entity Framework to impersonate a SQL Server user (i.e. user without a login). I have created a new constructor for the DB context MyDatabaseEntities which includes an argument for the name of the user to impersonate. Here is the code that I've written:

public partial class MyDatabaseEntities
{
    private String _impersonateUser = null;

    public MyDatabaseEntities(String impersonateUser)
        : base("MyConnectionString")
    {
        _impersonateUser = impersonateUser;

        this.Database.Connection.StateChange += Connection_StateChange;

    }

    void Connection_StateChange(object sender, StateChangeEventArgs e)
    {
        if (e.CurrentState == ConnectionState.Open && e.OriginalState != ConnectionState.Open)
        {
            using (var cmd = this.Database.Connection.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;

                cmd.Parameters.Add(new SqlParameter("user", _impersonateUser));

                cmd.CommandText = "EXECUTE AS USER = @user";

                cmd.ExecuteNonQuery();

            }

        }

    }

I had to add the check...

if (e.CurrentState == ConnectionState.Open && e.OriginalState != ConnectionState.Open)

...because the method Connection_StateChange method seems to execute even when the state hasn't changed. Then problem is that when I run the code twice,

public void RunSimpleQuery()
{
    using (MyDatabaseEntities context = new MyDatabaseEntities("UserName"))
    {
        var result = context.TableName.ToList();

    }

}

...Entity Framework throws a SqlException:

A severe error occurred on the current command. The results, if any, should be discarded.\r\nA severe error occurred on the current command. The results, if any, should be discarded.

Any ideas?

Update 1

I in my code above, I changed...

cmd.CommandText = "EXECUTE AS USER = @user;";

...to...

cmd.CommandText = "REVERT; EXECUTE AS USER = @user;";

...and I still get the same SqlException error.

Rufous

The problem is that EF closes connection when it doesn't need it and returns it back to the pool. So when it executes some SQL again it request new connection from the pool where your event may not be initialized. – Ulceration

@LadislavMrnka Yes, you identified the problem. I guess that I'll have to specify in the connection string not to use connection pooling...it's a shame. Please create an answer for this (just say what you said in the comment) so I can give you the credit. Thanks! – Rufous

You should rather try to take control over the connection yourselves (by passing it to DbContext) but there was some problem with this: blogs.msdn.com/b/diego/archive/2012/01/26/…. – Ulceration

@LadislavMrnka I am going to overload the MyDatabaseEntities constructor and supply it with the required connection string (without pooling for impersonation). I'll post my code in an update. Again, please provide an answer (copy and paste your comments) so I can give you the credit! =) – Rufous

Great question. Did you get this working, if so can you post an answer showing what you did? – Avowal

Can you post an answer showing what you did to get this working? – Delenadeleon

Answer 1

The problem is that EF closes connection when it doesn't need it and returns it back to the pool. So when it executes some SQL again it request new connection from the pool where your event may not be initialized. But again I believe that you should try to solve this with manually controlling connection lifetime to have both benefit of connection pooling and be able to meet your requirements.

Ulceration

BTW: I take your advice with respect to manually controlling the connection life time to have the benefit of connection pooling with impersonation (the link that you provided is very helpful). I think that I'm going to go with your way. I'll update my code and post it as another update. – Rufous

Answer 2

I know is an old question, but maybe will be useful for someone.

I did in a different way, using your code...

Instead of

Connection_StateChanged event

I create two methods in the same class:

    public void ChangeUser(String sUser)
    {
        if(Database.Connection.State != ConnectionState.Open)
            Database.Connection.Open();

        using (var cmd = Database.Connection.CreateCommand())
        {
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add(new SqlParameter("user", sUser));
            cmd.CommandText = "EXECUTE AS USER = @user;";
            cmd.ExecuteNonQuery();
        }
    }

    public void Revert()
    {
        if (Database.Connection.State != ConnectionState.Open)
            Database.Connection.Open();

        using (var cmd = Database.Connection.CreateCommand())
        {
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "REVERT;";
            cmd.ExecuteNonQuery();
        }
    }

I use it before and after execute stored procedure,

using (var db = new MyDatabaseEntities())
        {
            db.ChangeUser(model.Username);
            var result = db.Something();
            db.Revert();
            return result;
        }

It works fine with SPs and it doesn't throw an exception even after many executions. If I could catch an event after command execute, maybe all be encapsulated on MyDatabaseEntities.

Tigress

Is this thread safe? – Concatenate

No, it is not thread safe – Tigress