SQL Server application role, performance hit

Question

I plan to use MS SQL Server 2005 application roles in my application. I will start the role by executing the *sp_setapprole* and finish by executing the *sp_unsetapprole* SPs. The application is implemented in ASP.NET.

I've read that connection pooling doesn't work with application pooling and there is no way to react on connection disconnect event (execute sp_unsetapprole just before disconnection).

I plan to call sp_setapprole at the start of all my SPs and call sp_unsetapprole at the end of all my SPs.

Have you used SQL application roles? What are your XPs? What about performance hit?

Answer 1

I have not used app roles before, but from what I know about the perf hit is that after setting the application role there is no way to revert to the prev. security context. Thus the connection cannot be reused in pooling. This alone is a huge perf. hit that forces you to think twice about using app roles.

However, the docs say that starting from SQL Server 2005 there is a way to remember the original security context in a kind of cookie returned from sp_setapprole and after that use sp_unsetapprole to revert back to it. So the pooling should work again. If I were you, I would compare the perf. with a couple of simple statements/sprocs.

Any reason you don't use the standard ASP.NET membership API on the application level instead of app roles?

Answer 2

I've rolled my own "approle" in the past, it's not too hard. Create a database roll for each type of user (manager, casher, clerk, whatever). Create a database user with the group name (manager_user, casher_user, clerk_user etc). Create accounts for your real users and put them in the database rolls. Validate your asp.net users by logging them into the database (open & close a connection), a lookup table or best if you use windows authentication and just get their user name from IIS. Check their membership in a database role but log in to the database using role_user. You can secure the database objects via the role_user, the users don't login and don't have access to any sql objects and you get connection pooling.