title: how to enable SQL Application Role via Entity Framework
body: <p>I'm now developing big government application with entity framework. at first i have one problem about enable SQL application role. with ado.net I'm using below code:</p>
  
  <pre><code>SqlCommand cmd = new SqlCommand("sys.sp_setapprole");
              cmd.CommandType = CommandType.StoredProcedure;
              cmd.Connection = _sqlConn;
              SqlParameter paramAppRoleName = new SqlParameter();
              paramAppRoleName.Direction = ParameterDirection.Input;
              paramAppRoleName.ParameterName = "@rolename";
              paramAppRoleName.Value = "AppRole";
              cmd.Parameters.Add(paramAppRoleName);
  
              SqlParameter paramAppRolePwd = new SqlParameter();
              paramAppRolePwd.Direction = ParameterDirection.Input;
              paramAppRolePwd.ParameterName = "@password";
              paramAppRolePwd.Value = "123456";
              cmd.Parameters.Add(paramAppRolePwd);
  
              SqlParameter paramCreateCookie = new SqlParameter();
              paramCreateCookie.Direction = ParameterDirection.Input;
              paramCreateCookie.ParameterName = "@fCreateCookie";
              paramCreateCookie.DbType = DbType.Boolean;
              paramCreateCookie.Value = 1;
              cmd.Parameters.Add(paramCreateCookie);
  
              SqlParameter paramEncrypt = new SqlParameter();
              paramEncrypt.Direction = ParameterDirection.Input;
              paramEncrypt.ParameterName = "@encrypt";
              paramEncrypt.Value = "none";
              cmd.Parameters.Add(paramEncrypt);
  
              SqlParameter paramEnableCookie = new SqlParameter();
              paramEnableCookie.ParameterName = "@cookie";
              paramEnableCookie.DbType = DbType.Binary;
              paramEnableCookie.Direction = ParameterDirection.Output;
              paramEnableCookie.Size = 1000;
              cmd.Parameters.Add(paramEnableCookie);
  
              try
              {
                  cmd.ExecuteNonQuery();
                  SqlParameter outVal = cmd.Parameters["@cookie"];
                  // Store the enabled cookie so that approle  can be disabled with the cookie.
                  _appRoleEnableCookie = (byte[]) outVal.Value;
  
              }
              catch (Exception ex)
              {
                  result = false;
                  msg = "Could not execute enable approle proc." + Environment.NewLine + ex.Message;
              }
  </code></pre>
  
  <p>But no matter how much I searched I could not find a way to implement on EF.</p>
  
  <p>Another question is: how to Add Application Role to Entity data model designer?</p>
  
  <hr>
  
  <p>I'm using the below code for execute parameter with EF:</p>
  
  <pre><code>AEntities ar = new AEntities();
  
              DbConnection con = ar.Connection;
              con.Open();
              msg = "";
              bool result = true;
              DbCommand cmd = con.CreateCommand();
  
              cmd.CommandType = CommandType.StoredProcedure;
              cmd.Connection = con;
              var d = new DbParameter[]{
              new SqlParameter{ ParameterName="@r", Value ="AppRole",Direction =  ParameterDirection.Input}
            , new SqlParameter{ ParameterName="@p", Value ="123456",Direction =  ParameterDirection.Input}
             };
              string sql = "EXEC " + procName + " @rolename=@r,@password=@p";
              var s = ar.ExecuteStoreCommand(sql, d);
  </code></pre>
  
  <p>When run ExecuteStoreCommand this line return error:</p>
  
  <blockquote>
    <p>Application roles can only be activated at the ad hoc level.</p>
  </blockquote>