CODEWITHSUNDEEP
c#sql-serverentity-framework
okrumnow
okrumnow

Reputation: 2416

EF with SQL server application role

I need to use application rule security on sql server. And i want to use Enity Framework Code First.

After a successful login my connection is set to application role. I then create my DbContext using this connection.

But: EF expects a closed connection object. And closing a connection drops the application role.

How can i solve this dilemma?

Upvotes: 3

Views: 2430

Answers (3)

N Jones
N Jones

Reputation: 1014

Since this question is high on the search result list, I just wanted to throw in a word of caution. I have an app that needed to use an application role and okrumnow's solution seemed at first to work.

However, in unit testing I discovered that sometimes handling the StateChanged event will cause the event to be raised twice and you'll get the error:

"Impersonate Session Security Context" cannot be called in this batch because a simultaneous batch has called it.

It seems to help to change the conditional to:

args.CurrentState == ConnectionState.Open &&
    args.CurrentState == ConnectionState.Closed

But it still doesn't eliminate the error. I confirmed this in EF4.3 and EF5. Ladislav is correct that the ideal way is creating a connection for the DbContext and telling the context that it doesn't own it.

Also, connection pooling is never possible with this setup since there is no ConnectionState.Closing event where you can call sp_unsetapprole before your connection is closed.

Since I had the flexibility, my solution was to eliminate the usage of an app role and using a dedicated SQL login instead. Either way you're hard-coding a password...

Upvotes: 4

okrumnow
okrumnow

Reputation: 2416

I managed to get this work with two steps:

Switch connection pooling off, which is mentioned all the time for connections using application roles. As i have a desktop application, this is no problem for me.

Add a handler to DbConnection.StateChanged and activate the application role on every opening of the connection. Without connection pooling, it is not necessary to sp_unsetapprole on closing. So this works for me:

context.Database.Connection.StateChanged += (sender, args) =>
  if (args.CurrentState == ConnectionState.Open) {
    activateAppRole((DbConnection)sender, ...);
  }
}

I guess, if Pooling is vital for someone, she may call sp_unsetapprole on closing the connection in this same handler.

Upvotes: 6

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364369

EF doesn't have any native support for this. I guess the workaround can be:

  • Creating your own connection and passing it (closed) to EF context / EntityConnection. This should enforce that you will have connection lifetime under your control and EF will not close it (but I already saw complains that it doesn't work with DbContext).
  • Once you have instance of the context created set application role. Context itself should not generate any queries to the database (except DbContext with code first checking version of the database) so setting the role after context creation should not cause any problems.

Upvotes: 0

Related Questions