MS SQL Server : Applicaton Role

[jim-thomas]

In our DB First environment using MS SQL Server all access is based on application role. Does EF7 provide any current mechanism for queries using app role? (I assume 'simple interception' will provide that capability at some future date, since it is listed as a high priority, but my question is what will be available with EF7 RTM.)

[rowanmiller]

Application roles are enabled on a per-connection basis by executing sp_setapprole. To this end, it should simply be a matter if executing some raw SQL when an instance of a context is created.

I just tried this with EF7 RC1 and it worked...

public class MyContext : DbContext
{
        public MyCotnext()
        {
            this.Database.ExecuteSqlCommand("EXEC  sp_setapprole <role> <password>");
        }

...

[jim-thomas]

Thank-you, that's seems like a great solution but unfortunately it did not work for me. I get a 'Select permission denied' as if the query is not using the app role.

Here is the dbcontext:

public partial class StaffContext : DbContext
{
    public StaffContext()
    {
        try
        {
            this.Database.ExecuteSqlCommand("EXEC sp_setapprole 'foxgl', 'psw'");
        }
        catch (Exception exception)
        {
            Console.WriteLine("App Role exception: " + exception.Message);
        }
    }

    protected override void OnConfiguring(DbContextOptionsBuilder options)
    {
        options.UseSqlServer(@"Server=sql21devf;Database=Staff;Trusted_Connection=True;Pooling=false;");
    }   

and here is the query code which generates the 'Select permission denied error' :

using (var db = new StaffContext())
{
    List<TopProject> projects = (from row in db.TopProject select row).ToList<TopProject>();
}

I also have a database role, is_developer, which grants me select access. When that access is granted this code works great. When I turn off select access on is_developer, this code fails to use the app role (which definitely has 'select' granted) and reports the denial. To avoid any ambiguity I also removed my is_developer role yet still get the 'Select permission denied' using this foxgl app role. Note that we have been using this foxgl app role for years with EF4 so this is not new technology for us although I am not a DBA and may be making some trivial mistake.

Note that "Pooling=false". With "Pooling=true" I get the familiar "A severe error has occurred on the current command. The results, if any, should be discarded."

I trust this is enough detail for you to tell me what you did differently that made your code work.

[jim-thomas]

I have gotten it to work by assuming the connection was closed after the sp_setapprole executed meaning the next query would open a new connection. Explicitly opening the connection prevents EF from closing the connection (as was true with previous versions of EF):

public StaffContext()
    {
        try
        {
            db.Datadase.OpenConnection();
            this.Database.ExecuteSqlCommand("EXEC sp_setapprole 'foxgl', 'psw'");
        }

I would like to know how you got your query to work without this explicit OpenConnection(), e.g. is there some connection string magic or other config that keeps the connection open?

[rowanmiller]

@jim-thomas I didn't actually verify it worked end-to-end, just that executing the stored procedure worked. I didn't realize the app role would be lost when the connection was closed and re-opened. What you are doing is a good solution for the moment. When we have some better interception points (coming in the future) then we should be able to make this a lot cleaner for you.

[rowanmiller]

Note for triage: I don't think we need to do anything to specifically support app roles but we have seen a folks wanting to use several database feature now that require sending some pre-amble type SQL to the database before issuing any SQL. When we do the high level interception points, I think a relational specific OnDbConnectionOpened hook would be very helpful.

[jim-thomas]

Thanks for your guidance - it got us to a solution. I googled this topic many times and found no other info. I am surprised anyone else hasn't encountered it but perhaps EF7 is too new. App Roles have to be one of the easiest ways to protect data from the plethora of database access tools out there which don't enforce the business rules an enterprise application is built to do.

[rowanmiller]

Added a note about this scenario to our Lifecycle Hooks issue #626