SQL Server Applciation roles

[Tony]

Has anyone tried to use SQL Server application roles from ADO.NET.

If I call sp_setapprole once per connection, I get a "General Network Error"
on the second connection (first works great). So I figured it is being
maintained after the initial connection has this set by the connection pool,
but not setting to the application role on the second connection causes
other errors such as "CREATE TRIGGER permission denied on object
'DDTR_Column'" as it must not be in the context to the application role, but
if I set the app role on this connection, I get a "General Network Error".

So, is this just a bug or am I doing something wrong. I can't seem to find
any mention of this in the docs or online.

Has anyone ever used application roles, and if so, what's the trick?

Tony

 

[Mary Chipman]

Basically you have to turn off connection pooling to get them to work
properly, so for most apps it's not worth the performance hit. You're
better off using regular database roles. See these articles for more
info:
SQL Application Role Errors with OLE DB Resource Pooling
http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q229564
Pooling in the Microsoft Data Access Components

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmdac/html/pooling2.asp

-- Mary
MCW Technologies
http://www.mcwtech.com

 

[Tony]

Wow, that sucks...

Big thanks for the info, will save me time tracking this thing down.

Tony