locked
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.Database.Connection.Open();

    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;
                cmd.Parameters.Add(pCookieId);
    
    
                cmd.ExecuteNonQuery();
    
    
                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;
                cmd.Parameters.Add(pCookieId);
    
                cmd.ExecuteNonQuery();
            }
    
            byte[] cookie;
            public Db()
            {
                //ensure initialization
                this.Database.Initialize(false); 
                
                //force the connection to remain open for the lifetime of the DbContext 
                //instead of being returned to the AppDomain's Connection Pool
                this.Database.Connection.Open();
    
                //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)
            {
                UnSetAppRole(cookie);
                base.Dispose(disposing);
            }
    David


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


    Thursday, June 4, 2015 12:59 PM