Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

SQL Server Security: Pros and Cons of Application Roles Expand / Collapse
Author
Message
Posted Friday, July 20, 2007 7:14 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, December 7, 2010 8:19 AM
Points: 327, Visits: 123

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.

Post #383661
Posted Friday, July 20, 2007 9:40 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 2:33 PM
Points: 2,844, Visits: 1,153

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.




Post #383761
Posted Friday, July 20, 2007 9:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 2:42 PM
Points: 14, 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
Post #383768
Posted Friday, July 20, 2007 10:04 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, June 26, 2013 8:01 AM
Points: 258, Visits: 19

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
Post #383771
Posted Friday, July 20, 2007 10:31 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Monday, August 18, 2014 8:24 AM
Points: 6,634, Visits: 1,871

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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #383789
Posted Friday, July 20, 2007 2:04 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, June 26, 2013 8:01 AM
Points: 258, Visits: 19

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
Post #383870
Posted Monday, July 23, 2007 7:58 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 10:05 AM
Points: 360, Visits: 1,957

"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.

Post #384174
Posted Friday, July 25, 2008 4:35 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, January 2, 2013 12:15 PM
Points: 1,443, 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
Post #540790
Posted Friday, July 25, 2008 7:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 26, 2012 11:54 AM
Points: 8, Visits: 45
Creio que nenhuma novidade foi acrescentada aqui.
Realmente Application Roles é muito pouco utilizada e difundida.
Post #540913
Posted Friday, July 25, 2008 8:41 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 1:10 PM
Points: 3,135, Visits: 11,480
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.



Post #540974
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse