SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server Security: Pros and Cons of Application Roles


SQL Server Security: Pros and Cons of Application Roles

Author
Message
Martin Vrieze
Martin Vrieze
SSC-Addicted
SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)

Group: General Forum Members
Points: 450 Visits: 125

I'm starting to set up reports in Reporting Services. The MS book "SQL Server 2005 Reporting Services" recommends setting up an "application role" for accessing data within the context of the Reporting Services packages. On first observation, it seems to produce a "workaround" on SQL Server Security.

To me this introduces some problems...

1. It actually adds more complexity to SQL Server security since the Reporting Services security model does not seem to be tied to SQL Server Security.

2. As more reports are added, more "report roles" need to be added to restrict access to sensitive reports.

3. Granted I have not fully researched this but...There does not seem to be a way to use SQL Server security tools and then tell the Reporting Services tool to use Windows Integrated security...when I attempt to do this, I cannot get in through the browser on my pc (even though I have full access priviledges)...only the administrative account on the local SQL server machine appears to be able to get in. I tried using the prompt for username/password using Windows Integrated security which does work. However, I do not want to require the user to key in username/password for every single report...clunky!!!

It would appear that managing the data connection/access for Reporting Services will require Application Roles in order to manage security and keep the Reporting Services app from being clunky to the end user.

Just my observation at a very early point on the learning curve.


Scott Coleman
Scott Coleman
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3834 Visits: 1465

There are some gotchas with app roles, but I think the major pro of letting users perform actions through an application without giving them any rights to the data or procedures outweigh all the cons.

Loosing your server role rights, such as sysadmin, after activating an activation role seems like a non-issue. The app role is intended for use by an application, not by a sysadmin issuing ad-hoc queries, and the application shouldn't be capable of attempting to perform actions that the app role doesn't have rights to.

Connection pooling is based on an exact match of the entire connection string. I keep app role and non-app role connections in separate pools simply by adding a different "Application Name=;" value to the connection strings. This does require a check [SELECT USER_NAME()] when you open a connection to see if you got a pooled connection that already has the app role active.

There is also a "Pooling=No" option that can be used in a .NET SqlConnection.ConnectionString.





peter weissbrod
peter weissbrod
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 92
I think you should also consider using AzMan. Let the windows users and groups define the user population, and the AzMan data store can persist the provisioning data completely outside of the database.

The microsoft security team built it specifically for application-level provisioning features, it integrates seamlessly with windows users and groups, it comes with an MMC snap in for development and administration, and finally, it can be deployed as a Active directory GPO, XML file, or lightweight active directory application-mode (ADAM) object.

We use azman for our latest enterprise project, and we have zero database-related concerns for application-level provisioning. Our clients like it because their IT admin can control it through the MMC snapins, deploy it in their own fasion, and we dont have to write an administrator interface.

I dont have a specific link, but a quick search for 'azman' on google should get you started down the path.
cheers
-pete
Greg Wilson-404807
Greg Wilson-404807
SSC Veteran
SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)

Group: General Forum Members
Points: 296 Visits: 21

There is a way to reset permissions (in 2005) sp_unsetapprole. It does take some careful coding to make sure it is always called before returning connections to the app_pool, but it is very doable.




Warm Regards,

Greg Wilson
solidrockstable.com
K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (10K reputation)

Group: Moderators
Points: 10444 Visits: 1917

Yup, and it's a nice feature. This article is back from the SQL Server 2000 days. It is another one I need to update.



K. Brian Kelley
@‌kbriankelley
Greg Wilson-404807
Greg Wilson-404807
SSC Veteran
SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)

Group: General Forum Members
Points: 296 Visits: 21

FYI - If you are using app role security with reporting services, you need to use a custom data extension on the reporting server. I've got some sample C# code on my blog about how to do it. (Although fairly simple to do, it takes an enormous amount of boiler plate code to do. If you download the boiler plate from my blog and start with that, it is a pretty quick process.)

I am one of the few guys who absolutely LOVES SQL app role security, and I'm planning a few more blog posts about App Role Security and connection pooling. The first about code patterns needed to get basic .NET connection pooling working, and the second on using custom connection pooling (where you keep the connection open without issuing an sp_unsetapprole and returning it to the pool). Be patient with me, I'm a little behind in my blog because I have a < 3wks old infant at home that is taking up a lot of my energy.

If anyone is having trouble with app roles, they are more than welcome to contact me.




Warm Regards,

Greg Wilson
solidrockstable.com
Ross McMicken
Ross McMicken
Mr or Mrs. 500
Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)

Group: General Forum Members
Points: 560 Visits: 2237

"Loosing your server role rights, such as sysadmin, after activating an activation role seems like a non-issue. "

This is absolutely true. I can't think of a reason why anyone should be accessing an application with an ID that has sysadmin rights in SQL Server. Any ID , and by extension the user of that ID, that has sysadmin rights in SQL Server should have minimal access to data via the application. The sysadmin ID's, particularly on production servers, should only be used to administer SQL, not do anything else. All of our DBA's have separate ID's for sysadmin access, and they use either Citrix or RPC to get to the servers.


SuperDBA-207096
SuperDBA-207096
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1969 Visits: 711
Brian,
Awesome article!

Most of the apps I've worked with use an intenal security mechanism and connect to the DB via 1 application login...

I think application roles have thier place, just have not come across too many instances where I've seen them used.

Mark
Ronei Toporcov
Ronei Toporcov
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 49
Creio que nenhuma novidade foi acrescentada aqui.
Realmente Application Roles é muito pouco utilizada e difundida.
Michael Valentine Jones
Michael Valentine Jones
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5818 Visits: 11771
I don’t think there is much use for Application roles except for client server applications.

Most of our applications are web based and end-users do not have logins in the database. Access to the database is only given to the service account that the web site is running under, and this account does not have any elevated privileges. Security is handled by limiting access to the web app to specific Windows groups. Finer grained security is handled within the application by letting an application administrator grant or revoke access to specific application functions.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search