DAAB 4.0 - SQL Application role (sp_setapprole) issue

Topics: Data Access Application Block
Nov 3, 2008 at 1:53 PM
Hello,

I'm using the Data Access Application Block 4.0 and it worked quite well. Now I changed my database security to using an application role (which is working well directly on db) and I tried to use it with my C# code.

I'm using the following C# code:

            Database db = DatabaseFactory.CreateDatabase();           

            DbCommand command = db.GetStoredProcCommand("sp_setapprole");
            command.CommandType = CommandType.StoredProcedure;
            db.AddInParameter(command, "@rolename", DbType.String, "role123");
            db.AddInParameter(command, "@password", DbType.String, "test123");

            db.ExecuteNonQuery(command);
            
            command = db.GetStoredProcCommand("spDepartmentGetMapping");
            command.CommandType = CommandType.StoredProcedure;
            
            using (IDataReader dataReader = db.ExecuteReader(command))
            {
                while (dataReader.Read())
                    ....
            }

Unfortunately the code is not executed as I exprected, because the following error is thrown:

1) when pooling=yes: "A severe error occurred on the current command.  The results, if any, should be discarded.\r\nA severe error occurred on the current command.  The results, if any, should be discarded."
2) when pooing=no: "The EXECUTE permission was denied on the object 'spDepartmentGetMapping', database 'test', schema 'dbo'."

Does anybody has an idea what I'm doing wrong???
Please help - it's urgend!
Thank's in advance,
Evelyn
Nov 4, 2008 at 5:34 AM
Hi Evelyn,

You can set the pooling to false and add a "Connection Reset=False" attribute to your connection string. You config should look like below:

<?xml version="1.0" encoding="utf-8"?>

<configuration>

  <configSections>

    <section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />

  </configSections>

  <dataConfiguration defaultDatabase="Sample" />

  <connectionStrings>

    <add name="Sample" connectionString="Data Source=SQLSERVER2005;Initial Catalog=SampleDB;Integrated Security=True;Pooling=False;Connection Reset=False"

      providerName="System.Data.SqlClient" />

  </connectionStrings>

</configuration>

 

 

 

Hope this helps.



Valiant Dudan
Global Technology and Solutions
Avanade, Inc.
valiant.a.dudan@avanade.com

Nov 4, 2008 at 7:30 AM

Hi Valiant,

thank you very much for your reply. Unfortunately the connection reset property was not helping. My error message is still: The EXECUTE permission was denied on the object 'spDepartmentGetMapping', database 'test', schema 'dbo' when I try to execute the above code.

Trying to execute the code via sql management studio works fine:
exec sp_setapprole 'role123', 'test123'
exec spDepartmentGetMapping

BR,
Evelyn
Nov 4, 2008 at 1:13 PM
What identity is your application running on? You may want to try using Sql authentication in your connection string just to verify if this is not a identity issue. There probably is a problem with the identity that your application is using that may not have privilage or may have but lower privilage to access the database.



Valiant Dudan
Global Technology and Solutions
Avanade, Inc.
valiant.a.dudan@avanade.com
Apr 23, 2009 at 3:41 PM
Hi Fidy,

I am facing the same problem. Did you find any fix for it. Can you please let me know more about it. The problem is exactly the same.

Regards,
Neeraj
Apr 23, 2009 at 3:52 PM
We were planning on using DAAB too and we use application roles.

1. Does it work?
2. Must we turn off pooling? That's a pretty big hit to performance...

Chanan.
Apr 24, 2009 at 3:08 AM
I think this is just more of permission issue. 

Could you try setting the connection pooling to true again and follow the suggestion in this thread -http://www.experts-exchange.com/Web_Development/Miscellaneous/Q_21698994.html

I've also come across this - http://support.microsoft.com/default.aspx?scid=kb;en-us;896373 .  Does this apply to you?


Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.
valiant.a.dudan@avanade.com
Jun 1, 2009 at 3:30 PM
Edited Jun 1, 2009 at 6:06 PM

The application block handles connection management. An application block method opens a connection and closes it prior to returning.  But the Application Role requires that the three stored procedures to run in the same connection   

 

Open the connection

1)   exec 'sp_setapprole'  --  Stores the original security context  in a cookie and activates the application role (strips of the original permisions and aquires the permissions of Application Role)

2)   exec 'xxxxxxxxxxxx'  --  Run the Store Procedure

3)   exc  ' sp-unsetapprole'  --   Deactive the application role and Restores the connection to the original security context using the cookie

Close the Connection

  SqlConnectionStringBuilder.ConnectionReset Property  (New in FrameWork 2.0 )   was helpfull.  In Framework 2.0, it worked when "connection reset=false;" was appended at the end of the connection string.   But this property was deprecated in the later versions. in 3.5 SP1,3.0 SP1,3.0 SP2, 2.0 SP1,2.0 SP2

 

 I am also facing the same problem now.   Is there any solution for implementing sql server Application Role in Appliction Block?

Regards,
Suresh

Jun 2, 2009 at 10:37 AM
Edited Jun 2, 2009 at 10:39 AM

Is setting Pooling=False is an option for you? I tried your steps. at first I ran to some exception. then I turn off pooling, everything work fine now . I tried this against a SQL server 2005 using Windows Authentication.

Valiant Dudan
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com

Jun 2, 2009 at 1:51 PM

Hi Valiant,

Disabling Application Pooling  will be a great hit to the performance for an enterprise level application.  Is there any other solution keeping the Application Pooling?

Regards,
Suresh

Jun 3, 2009 at 4:07 AM

I believe this issue is out of EntLib's scope and indeed, the workaround would be to disable pooling as stated here - http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q229564 and http://msdn.microsoft.com/en-us/library/bb669062.aspx - Application Role Features.

 

Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com

Jun 3, 2009 at 3:46 PM
Edited Jun 3, 2009 at 3:56 PM

Hi Sarah,

The Application block manages connection “Using” loop which is opened and closed at the end of the loop  the database class(database.cs).

 With the following changes in the file - database class(database.cs) and functions – ExecuteNonQuery, ExecuteScalar & LoadDataSet, if the connection is opened at the application, the first part of the  if statement (in red) is used to manage the connection from the application.

if (command.Connection != null)
{

    PrepareCommand(command, command.Connection);
    DoLoadDataSet(command, dataSet, tableNames);
}
else
{
    using (ConnectionWrapper wrapper = GetOpenConnection())
    {
        PrepareCommand(command, wrapper.Connection);
        DoLoadDataSet(command, dataSet, tableNames);
    }
}

The above lines in red are added to interrupt and find if there is a connection is already available on the command object and use it without disposing it of.  That means Command.Connection should be fully managed at the application - Create, open and close as below:

 Dim db As Database = DatabaseFactory.CreateDatabase()
Using conn As DbConnection = db.CreateConnection()
conn.Open()
       Dim dbCommand As DbCommand = db.GetStoredProcCommand(sqlCommand)
       call  the SP - sp_setapprole
       call  the SP - xxxxxxxxxxxxxx
       call  the SP - sp_unsetapprole

        conn.Close() 
End Using

Now it works fine with Application Pool on and Sql Application Role.
Am I doing the right thing to make the above changes in Application Block Code?

 

 

 

 

 

 

Jun 4, 2009 at 6:38 AM
Edited Jun 4, 2009 at 7:36 AM

If it works fine with your scenario that probably would be a yes. And yes I verfied that it is working fine.

Feb 17, 2011 at 4:41 PM

I am having the same problem with DAAB 5.0, I am trying to set application role of SQLserver before execute a select in the database.

Anyone found a workaround this problem ?

 

The table is only visible to the role not to the user that connected to the database (the user connection is working fine, I can execute a select getDate() for example) and I tested the user and the role outside the application with Sql Server Management Studio.

1) when pooling=yes: "A severe error occurred on the current command.  The results, if any, should be discarded.\r\nA severe error occurred on the current command.  The results, if any, should be discarded."
2) when pooing=no: "The EXECUTE permission was denied on the object ..."

Setting Connection Reset=False has no effect also.

How MSEL DAAB works between executions of multiple selects inside the same C# method? It seems to run each select in different connections/sessions...

Thanks,

   Regards,

      Gustavo

Feb 18, 2011 at 12:48 AM
Edited Feb 24, 2011 at 3:07 AM

EntLib automatically manages database connections that's why it isn't necessary to open and close a connection prior and after executing a command.  Thus, different calls to ExecuteDataSet, ExecuteNonQuery uses different connections.  

 

Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com

Feb 21, 2011 at 5:45 PM

It works if you open a connection prior to the execution and explicity set it to the commands that you will execute.

But I am using other benefits of the DAAB Database class, In my application I have do two calls: first (like the original post) using "Database.ExecuteNonQuery(command);" to set the role in the database (using sp_setapprole) and the second I am using DataAccessor to map the result set to an list of objects, like the following code:

DataAccessor<Sala> accessor = localDataBase.CreateSqlStringAccessor(sql, mapper);

IEnumerable<Sala> salaData = accessor.Execute();

In this scenario I don't have the option to explicity set the connection that the DataAccessor is using. I tried to put those two calls inside a using with the connection as argument, but got the same error (when executing the accessor) "The EXECUTE permission was denied on the object ..."

The Accessor and the benefits of using a mapper was a really help to my team, and I am trying to find a solution that allow us to keep using DAAB.

Thanks for your help,

   Gustavo

Feb 22, 2011 at 8:38 AM

If this is the case then I think it somewhat looks like a limitation for DAAB's Accessor. Though the entlib team is in the best position to confirm if this is indeed a limitation. Anyway what I can suggest for now is you can log this issue in the Issue Tracker.

Gino Terrado
Global Technologies and Solutions
Avanade, Inc.
entlib.support@avanade.com