Post reply

Application Roles in SQL Server 2005

  • 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

  • October 23, 2007 at 5:00 am

    #744427

    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

  • October 24, 2007 at 5:25 pm

    #745159

    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

  • October 24, 2007 at 8:47 pm

    #745197

    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

  • October 25, 2007 at 6:31 am

    #745347

    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

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

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