Application Roles in SQL 2005

We have an an application that was written using OLE DB (ADO) against a SQL 2000 Server that uses an Application role to give rights to the database objects. It connects, calls sp_setapprole and goes on. If the database needs to LOCK a record, it is creating a new ADO Connection and instantiating the Approle again. This model has been working fine up til now.

Now we are installing a SQL 2005 server for the latest version of the product we are working on and are running into an error. The error is

Error: 18059, Severity: 20, State: 1.
The connection has been dropped because the principal that opened it subsequently assumed a new security context, and then tried to reset the connection under its impersonated security context. This scenario is not supported. See "Impersonation Overview" in Books Online.

It's happening when the second ADO Connection for locking a record is being created and the sp_setapprole is being executed.

One of my questions is what is the problem with executing the approle on a different connection? Our code has not changed, so obviously SQL 2005 is doing something different. The other is What can we do to correct this?

Is the resource pooling different? We had problems in the beginning with approles and figured out through research that we needed to add OLE DB Services=-2 to the connection string to turn off resource pooling.

Is there an extra step to using Approles in SQL 2005?

Any help would be greatly appreciated as we need to resolve this ASAP.

Thanks,

David

SQL Server 2005 supports a richer impersonation model, and we made some changes to prevent potential for undesired escalation of privileges on both the existing and new impersonation mechanisms, including approles.

From your description I can guess that you are using a connection pool, and apparently the pool is misusing an impersonated context (approle). Does it still fail if you disable the connection pool?

If this is the case, one solution for using approles and connection pool would be to use the new @.fCreateCookie and @.cookie parameters in sp_setapprole along with sp_unsetapprole (http://msdn2.microsoft.com/en-us/library/ms365415.aspx). This allows the client code to obtain a server-generated cookie that can be used to revert to the original context before resetting the connection.

Unfortunately I haven’t experienced any behavior like the one you described; at this point I cannot really tell if the behavior you are experiencing is a problem on the way the client code is calling SQL Server, if this is a scenario where we decided to change the behavior of SQL Server to prevent an undesired escalation of privileges or a real bug in SQL Server 2005.

Please, let us know if the workaround I suggested work, if not I would like to understand more about the scenario so I can try to reproduce this behavior on my test environment.

Thanks a lot,

-Raul Garcia
SDE/T

SQL Server Engine

|||

We don't need to revert to the original context, so It's supposed to be disabling the resource pooling by adding "OLE DB Services=-2" on the connection string of the ADOConnection. We found the problem with connection pooling coding against SQL 2000.

Is that still effective in a SQL 2005 environment? Has the setting changed? It seems like something isn't set right since this worked in SQL 2000 and doesn't in SQL 2005.

Thanks,

David

|||

To clarify this situation more....

We aren't using .NET and the SqlClient for this app.

We are running a Win32 application, connecting to the SQL Server using ADO (MDAC 2.7 or 2.8), holding this connection open until the application closes at which point we close the connection. We are turning on ROW Level locking in SQL 2000 (not sure if this has been done or not on the SQL 2005 server) and using a separate connection with "locking hints" on the tables when a lock call is made to force a model we have to use for backwards compatability. (i.e.

select * from authors as a WITH (NOLOCK) { normal get }

select * from authors as a with (UPDLOCK) where identitycol = 1 { get with a lock for updating }

)

I wasn't sure how SQL Server 2005 would handle connectivity from the SQL 2000 client with sp3.

|||

Ok. Found the problem.

In certain circumstances, the connection string was being set with the OLE DB Services=-2 like it was supposed to be. Therefore the Pooling was still on and causing the problem.

Thanks for the assistance!

Application Role VB6

In VB6 I have the following code to use an application role
With cn
.ConnectionString = "MyConnectionString"
.ConnectionTimeout = 10
.Properties("OLE DB SERVICES") = -2
.Open
'Had to allow execute for sps against the windows group until we can
suss app role
.Execute "EXEC sp_setapprole 'MyApp',{ENCRYPT N 'MyPassword'},'ODBC'"
End With
I added a windows group to my sql server database, containing windows users
who will use this app. I gave the group no permissions of any kind. This
group as I understand it will allow the users to open the initial connection
to the database before the application role is then applied to the connectio
n.
Next I added an application role to the database and gave it select, insert
etc. on any tables the app needs and execute permission on any stored
procedures the app uses.
When a user (from the windows group) runs the app, data can be selected from
the tables but you get 'Execute permission denied' on any stored procedure
that runs.
If I explicitly grant permission to the windows group for one of the stored
procedures, they are able to access the stored procedure from within the app
.
Ok, I think it looks like the app isn't using the application role.
If I delete the application role from the database and amend the VB code so
as not to use an application role, the user is unable to perform the select
operation on any table, this confuses me as it looks like it was using the
application role to gain access to the data.
Anybody got any ideas.
Regards
PaulYou might try running a Profiler trace to see what's going on behind the
scenes. I suspect ADO is opening a separate connection for your stored proc
execution and this fails because the app role wasn't activated on that
connection.
Note that ADO will implicitly open another connection when the specified
connection is busy, such as in the case of an open recordset on that
connection.
Hope this helps.
Dan Guzman
SQL Server MVP
"Paul Whittaker" <PaulWhittaker@.discussions.microsoft.com> wrote in message
news:DEBB1148-23EF-425B-92D6-55DE6CA4DD77@.microsoft.com...
> In VB6 I have the following code to use an application role
> With cn
> .ConnectionString = "MyConnectionString"
> .ConnectionTimeout = 10
> .Properties("OLE DB SERVICES") = -2
> .Open
> 'Had to allow execute for sps against the windows group until we
> can
> suss app role
> .Execute "EXEC sp_setapprole 'MyApp',{ENCRYPT N
> 'MyPassword'},'ODBC'"
> End With
> I added a windows group to my sql server database, containing windows
> users
> who will use this app. I gave the group no permissions of any kind. This
> group as I understand it will allow the users to open the initial
> connection
> to the database before the application role is then applied to the
> connection.
> Next I added an application role to the database and gave it select,
> insert
> etc. on any tables the app needs and execute permission on any stored
> procedures the app uses.
> When a user (from the windows group) runs the app, data can be selected
> from
> the tables but you get 'Execute permission denied' on any stored procedure
> that runs.
> If I explicitly grant permission to the windows group for one of the
> stored
> procedures, they are able to access the stored procedure from within the
> app.
> Ok, I think it looks like the app isn't using the application role.
> If I delete the application role from the database and amend the VB code
> so
> as not to use an application role, the user is unable to perform the
> select
> operation on any table, this confuses me as it looks like it was using the
> application role to gain access to the data.
> Anybody got any ideas.
> Regards
> Paul|||Thanks Dan
That was spot on, it was opening another connection (a recordset hadn't been
closed before it tried to execute the stored procedure).
Paul
"Dan Guzman" wrote:

> You might try running a Profiler trace to see what's going on behind the
> scenes. I suspect ADO is opening a separate connection for your stored pr
oc
> execution and this fails because the app role wasn't activated on that
> connection.
> Note that ADO will implicitly open another connection when the specified
> connection is busy, such as in the case of an open recordset on that
> connection.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Paul Whittaker" <PaulWhittaker@.discussions.microsoft.com> wrote in messag
e
> news:DEBB1148-23EF-425B-92D6-55DE6CA4DD77@.microsoft.com...
>
>