How to use Application Roles with Entity Framework RRS feed

  • Question

  • How does one call sp_setapprole before a connection used by an Entity Framework object context closes, since the context opens and closes its own connection automatically, as needed? The only relevant event available on a store connection (DbConnection ) is StateChange , which does not include a ConnectionState of Closing . Attempting to call sp_setapprole when ConnectionState is Closed results in, "A severe error occurred on the current command. The results, if any, should be discarded."
    Wednesday, February 16, 2011 10:02 PM

All replies

  • In business logic every time uoi open connection call sp_setapprole  role and use ExecuteNonQuery

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, February 17, 2011 8:34 AM
  • Yes, I realize that I need to call sp_setapprole on an open connection and that I can use ExecuteNonQuery or similar mechanisms. What I need to determine is how to do this automatically while using the entity framework.

    As I mentioned in my initial inquiry, the EF store connection, which is simply a DbConnection under the covers, does not expose an event (such as Closing) that would allow me to invoke sp_setapprole before a connection is closed. The only close event we are given is Closed , which is too late to do any good. I'm hoping to avoid burdening our systems with having to manually manage the connection.

    Tuesday, February 22, 2011 4:31 PM
  • Did you get a solution for this? This is exactly what I am looking for right now?
    Friday, April 17, 2015 5:32 PM
  • I am looking for solution too.

    topicstarter please reply.

    Thursday, June 4, 2015 11:22 AM
  • You could pool your own connections, call sp_setapprole on each one and pass them to your DbContext on construction.

    Or in your DbContext constructor call


    This will force the connection to remain open for the duration of the DbContext.

    Then call sp_setapprole.  Before your DbContext closes unset the app role.  eg

       class Db : DbContext
            byte[] SetAppRole(string approle, string password)
                var cmd = this.Database.Connection.CreateCommand();
                cmd.CommandText = "sp_setapprole";
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.Add( new SqlParameter("@rolename", approle) );
                cmd.Parameters.Add(new SqlParameter("@password", password) );
                cmd.Parameters.Add(new SqlParameter("@fCreateCookie", 1));
                var pCookieId =new SqlParameter("@cookie", System.Data.SqlDbType.VarBinary);
                pCookieId.Size = 8000;
                pCookieId.Direction = System.Data.ParameterDirection.Output;
                return (byte[])pCookieId.Value;
            void UnSetAppRole(byte[] cookie)
                var cmd = this.Database.Connection.CreateCommand();
                cmd.CommandText = "sp_unsetapprole";
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                var pCookieId = new SqlParameter("@cookie", System.Data.SqlDbType.VarBinary);
                pCookieId.Size = 8000;
                pCookieId.Value = cookie;
            byte[] cookie;
            public Db()
                //ensure initialization
                //force the connection to remain open for the lifetime of the DbContext 
                //instead of being returned to the AppDomain's Connection Pool
                //create the appliction role if it doesn't exist
                //this.Database.ExecuteSqlCommand("if user_id('MyAppRole') is null create application role MyAppRole with password='P@ssword1'");
                cookie = SetAppRole("MyAppRole", "P@ssword1");
            protected override void Dispose(bool disposing)

    David http://blogs.msdn.com/b/dbrowne/

    Thursday, June 4, 2015 12:59 PM