Application Roles

Question

Application Roles

PR-403671

SSC Enthusiast

Points: 159
More actions
January 7, 2008 at 12:31 pm

#185351

Hi all,
Any one can explain how application roles work? and how can we integrate aplication role from database to application?
Thanks

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

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

jsheldon SSCertifiable Points: 5250 More actions · Answer 1

PR,

I found this link that spells out the application role purpose and how to establish an application role between app and SQL server

http://articles.techrepublic.com.com/5100-22_11-5068954.html

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 2

Hello,

This article explains how it works

http://msdn2.microsoft.com/en-us/library/ms190998.aspx

please read it and then ask here if you need to know more details.

but basically the application takes care of the user authentication in any form and then when user is authenticated by the application, the application logic decides what application role the user should be connected to SQL Server. Then it activates the appropriate application role as explained in the article. The role has permissions associated with it.

Let us know if you have more questions.

Regards,Yelena Varsha

PR-403671 SSC Enthusiast Points: 159 More actions · Answer 3

PR-403671

SSC Enthusiast

Points: 159

January 8, 2008 at 7:41 am

#765719

Thank you for you reply

Joe Clifford SSCrazy Eights Points: 8739 More actions · Answer 4

One caveat in regards to application roles, application roles are enforced entirely by the application, not by Active Directory or SQL Server which makes the application itself just one more place where authentication/access must be managed.

Years ago, prior to Active Directory, application roles were pretty common but over the years I've seen a transition to using Active Directory groups instead and they are currently used much more frequently than application roles within SQL Server. The advantage are obvious, most current development languages are capable of determining AD group membership (e.g. User.IsInRole) and SQL Server can use those same AD groups to enforce authentication/access for not only the application but for any database access including adhoc reporting tools, etc.

J

cmille19 SSCertifiable Points: 5970 More actions · Answer 5

I remember looking at applicaiton roles in SQL 7.0 and the biggest issue I noticed was that application roles cannot do connection pooling. I haven't used them since and instead opted for service accounts/applicaiton logins and handled security through the application.