Application Roles in SQL Server 2005

Question

Application Roles in SQL Server 2005

Barry-368475

Valued Member

Points: 58
More actions
October 23, 2007 at 4:44 am

#183096

Hi All,
I'm planning to use application roles to allow access to the objects only to the users that connect through a particular application and restrict the access to others.The schema used for all the database objects is same.
I have 135 applications accessing the databases in different clusters and the no. of open connections per sec would be around 1000 during peak hrs. Should connection pooling be disabled if i want to use app roles?
What would be the other performance impacts?
Regards,
Bharath

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

VaiydeyanathanVS Hall of Fame Points: 3053 More actions · Answer 1

Allow the users to connect to the databases through Windows Authentication as this will work for them to browse through the entire databases. If you are specific about SQL authentication, then ask your System Admin to configure in a such manner

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 2

If you're using SQL Server 2005, connection pooling can be used with application roles. There is a new stored procedure, sp_unsetapprole, which should be run before "closing out" the connection. You'll have to set a cookie to store the context at the beginning of the connection, but it's not terribly difficult to do so. Check the sp_unsetapprole topic in Books Online.

K. Brian Kelley
@kbriankelley

Barry-368475 Valued Member Points: 58 More actions · Answer 3

Thanks for ur reply..

sp_unsetapprole stored procedure will deactivate the application role and revert to the original securtiy context. So, network errors can be negated in this case and connection pooling can be enabled if the user is coming thro the application. But, what if i connect thro the management studio and try to set the app role using sp_setapprole stored procedure. This will give an error if i have already set the app role for a connection.

Will there be any other major performance impact using app roles in SQL Server 2005 SP2? Just wanted to confirm this because we would be dealing with huge data and the no.of open connections would also be on the higher side.

Regards,

Barry

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 4

sp_unsetapprole assumes you already captured the original security context in a cookie. In the example you gave, where the connection already had an app role set, that's not what you want. Basically, you want to unset the application role before closing the connection.

K. Brian Kelley
@kbriankelley