SQLServerCentral / Discuss Content Posted by Brian Kelley / Article Discussions / Article Discussions by Author / SQL Server Security: Pros and Cons of Application Roles / Latest Posts

RE: SQL Server Security: Pros and Cons of Application Roles

Anipaul — Mon, 28 Jul 2008 02:30:07 GMT

Nice article and good explanation in the article as well as in the discussion. :)

RE: SQL Server Security: Pros and Cons of Application Roles

K. Brian Kelley — Fri, 25 Jul 2008 11:32:28 GMT

[quote][b]Yelena Varshal (7/25/2008)[/b][hr]Brian,The app uses sp_executesql, this is from the profiler that I just ran for the PW change. The password I submitted to the app was completely different.execute sp_executesql N'sp_password NULL, ''milm434567'', ''test'''Disclamer # 1: I create cases with the vendor for a number of years to improve handling logins.Disclamer # 2 I know that dynamic statements are not safe.[/quote]Argh. You're right. If you call it through dynamic SQL it doesn't hide it. Not good.

RE: SQL Server Security: Pros and Cons of Application Roles

Yelena Varshal — Fri, 25 Jul 2008 11:13:40 GMT

Brian,The app uses sp_executesql, this is from the profiler that I just ran for the PW change. The password I submitted to the app was completely different.execute sp_executesql N'sp_password NULL, ''milm434567'', ''test'''Disclamer # 1: I create cases with the vendor for a number of years to improve handling logins.Disclamer # 2 I know that dynamic statements are not safe.

RE: SQL Server Security: Pros and Cons of Application Roles

K. Brian Kelley — Fri, 25 Jul 2008 11:09:48 GMT

[quote][b]Michael Valentine Jones (7/25/2008)[/b][hr]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.[/quote]I agree with you for the most part. Using a service account means you can take advantage of connection pooling, etc. There may be cases where it's important to record what user actually logged in using tracing (and not relying on the application). Other than that, I can't think of a realistic situation where you'd want to use the credentials of a user and switch to an app role unless you're running some web application that can't use Windows auth (for instance, hosted on Apache or Tomcat) and there are corresponding SQL Server based logins for access, which you're also relying on to permit the security check for the web application. I don't like those kinds of designs, but I have seen them.

RE: SQL Server Security: Pros and Cons of Application Roles

K. Brian Kelley — Fri, 25 Jul 2008 11:05:17 GMT

[quote][b]Yelena Varshal (7/25/2008)[/b][hr]One catch here: when the password gets created or modified the real database password could be traced with the Profiler. To run SQL Server Profiler, users must be granted the ALTER TRACE permission per BOL. They can also get the changed passwords from the trace file if the traces are set up and the user has access to the folder where the trace files are stored and has knowledge how to read them etc. But: if the user is that smart maybe he has to be the admin on this server for the first place. :)[/quote]Profiler or a server side trace should hide anything using ALTER LOGIN or sp_password, though. Are you running with a particular trace flag or something like that?

RE: SQL Server Security: Pros and Cons of Application Roles

K. Brian Kelley — Fri, 25 Jul 2008 11:04:26 GMT

[quote][b]reuben.anderson (7/25/2008)[/b][hr]Yes, Web applications that I see these days all seem to use a single generic login which is stored, hopefully encrypted, into web.config. Shouldn't the SQL 2005 implementation of application roles permit these web app developers to use integrated authentication? I guess that depends on the app as well - guest users coming from www won't have domain accounts. [/quote]It depends on the location of the web server. If the web server is in the DMZ, it shouldn't be on a trusted domain. That means no Windows authentication. With that said, application roles can be used with either.

RE: SQL Server Security: Pros and Cons of Application Roles

Yelena Varshal — Fri, 25 Jul 2008 11:01:32 GMT

Nice article, Brian!I share your opinion about application roles and would like to give you one workaround how users can not access the database outside the application without using application roles.You say: "If I grant INSERT and UPDATE rights to the users via a normal database role, that means these particular users could make changes in TroubleTickets through Microsoft Access, Query Analyzer, Enterprise Manager, or some other tool. "To work around this problem, one of the major CRMs uses this: they change the user password on the way to the database. The user uses his SQL Server standard login to login the the application and the application submits the modified password to the database. The passwords get created and reset through the application, so the user never even knows his real database password. One catch here: when the password gets created or modified the real database password could be traced with the Profiler. To run SQL Server Profiler, users must be granted the ALTER TRACE permission per BOL. They can also get the changed passwords from the trace file if the traces are set up and the user has access to the folder where the trace files are stored and has knowledge how to read them etc. But: if the user is that smart maybe he has to be the admin on this server for the first place. :)

RE: SQL Server Security: Pros and Cons of Application Roles

reuben.anderson — Fri, 25 Jul 2008 10:00:14 GMT

Yes, Web applications that I see these days all seem to use a single generic login which is stored, hopefully encrypted, into web.config. Shouldn't the SQL 2005 implementation of application roles permit these web app developers to use integrated authentication? I guess that depends on the app as well - guest users coming from www won't have domain accounts.

RE: SQL Server Security: Pros and Cons of Application Roles

Brian Jones-401554 — Fri, 25 Jul 2008 09:38:27 GMT

I've used application roles in the past to allow users read only access via their login (actually I use AD groups so only need to create them once) for reporting purposes, and restrict actual data updates to be via the application only. This pays dividends when installing a system at a site that allows users create their own reports using Access for instance. Unless an organisation is extremely strict about what people can and can't install on their desktop, then it is incredibly easy to download an app and poke around a database which grants access to your windows login. It is still possible to find out who the user is on the end of an approle, so tracking down deadlocks etc is not a great problem either.The ability to lock a database down in this manner, and still allow reporting access for power users makes approles in my opinion extremely useful.

RE: SQL Server Security: Pros and Cons of Application Roles

Michael Valentine Jones — Fri, 25 Jul 2008 08:41:52 GMT

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.

RE: SQL Server Security: Pros and Cons of Application Roles

Ronei Toporcov — Fri, 25 Jul 2008 07:52:24 GMT

Creio que nenhuma novidade foi acrescentada aqui.Realmente Application Roles é muito pouco utilizada e difundida.

RE: SQL Server Security: Pros and Cons of Application Roles

SuperDBA-207096 — Fri, 25 Jul 2008 04:35:32 GMT

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

RE: SQL Server Security: Pros and Cons of Application Roles

Ross McMicken — Mon, 23 Jul 2007 07:58:00 GMT

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

RE: SQL Server Security: Pros and Cons of Application Roles

Greg Wilson-404807 — Fri, 20 Jul 2007 14:04:00 GMT

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.

RE: SQL Server Security: Pros and Cons of Application Roles

K. Brian Kelley — Fri, 20 Jul 2007 10:31:00 GMT

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.

RE: SQL Server Security: Pros and Cons of Application Roles

Greg Wilson-404807 — Fri, 20 Jul 2007 10:04:00 GMT

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.

RE: SQL Server Security: Pros and Cons of Application Roles

peter weissbrod — Fri, 20 Jul 2007 09:59:00 GMT

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

RE: SQL Server Security: Pros and Cons of Application Roles

Scott Coleman — Fri, 20 Jul 2007 09:40:00 GMT

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.

RE: SQL Server Security: Pros and Cons of Application Roles

Martin Vrieze — Fri, 20 Jul 2007 07:14:00 GMT

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.

RE: SQL Server Security: Pros and Cons of Application Roles

homebrew01 — Mon, 27 Mar 2006 14:34:00 GMT

We're thinking of using application roles, but I don't quite get how to give it permissions. We currently have a sql login that's being used & abused. It has dbo access, and I want to use an app role that also has dbo access so that it can create work tables via an application. I can see how to grant access to specific tables and SPs, but I don't want to have to manually manage specific objects.

What am I missing ?

RE: SQL Server Security: Pros and Cons of Application Roles

ALZDBA — Tue, 02 Sep 2003 00:03:00 GMT

With Standard VB6 it needs a normal login-procedure and the a switch to theapplication-role executing the sp_setapprole.Is there a .Net-framework-based integration for SQLServer application roles?If not : are application roles obsolete ?

RE: SQL Server Security: Pros and Cons of Application Roles

K. Brian Kelley — Wed, 27 Aug 2003 12:39:00 GMT

Funny you mentioned that. I read the judge's comments and I don't think my article could in any way be construed with O'Reilly so I think I'm safe. If not, us DBAs aren't exactly full of money so it's like trying to squeeze blood out of a turnip.

RE: SQL Server Security: Pros and Cons of Application Roles

cppwiz — Wed, 27 Aug 2003 12:33:00 GMT

quote:
I'll try and be fair and balanced in this article, but I've provided this disclaimer in case I'm not.

I think Fox News will be starting another lawsuit because you used the phrase "fair and balanced." First Al Franken, now Brian Kelley...

RE: SQL Server Security: Pros and Cons of Application Roles

Steve Jones - SSC Editor — Wed, 27 Aug 2003 10:20:00 GMT

Nicely written Brian and shares many of my same views. I've tended to handle inappropriate access (like not using the applicaiton) administratively. A chewing out, a writeup, or a termination if people see the need to use Access or some other tool to change data.However, I'm curious about a couple things. What if you used a "shared login" for all users that had minimal right before invoking the app role? Would that eliminate one of the cons?Also, for pooling issues, an app could potentially just hold a connection open. For quite a few client/server apps, this might make sense. Alternatively, you could set a semaphore of some sort in the app when the role is invoked and not reissue "sp_setapprole" if the semaphore is set. A simple check could determine "which role" you had.Lastly, not sure the server roles item applies for this. The apps where we want to prevent access wouldn't usually have server roles for someone. There are exceptions, but not sure they're enough to mark this as a "con", more as an FYI.Again, great article.Steve Jonessjones@sqlservercentral.comhttp://www.sqlservercentral.com/columnists/sjoneswww.dkranch.net

RE: SQL Server Security: Pros and Cons of Application Roles

K. Brian Kelley — Wed, 27 Aug 2003 08:14:00 GMT

quote:
In the end I dumped the application role and harassed the developer to change his code. If there were a way to “undo” or roll back the application role setting, this would not have been a problem.

I agree wholeheartedly. While a simple stored procedure can reset client settings, nothing exists to reset an app role back to the base user. Not sure why as it would make resource pooling more friendly.

RE: SQL Server Security: Pros and Cons of Application Roles

Philip Kelley — Wed, 27 Aug 2003 08:05:00 GMT

We hit another “gotcha” with connection pooling with a web application a long time back.The ASPs called a DLL that uses SQL authentication (I know, I know) to access the database. We implement all database access through stored procedures, but there was this one routine where the developer decided to build and submit a SELECT statement within a stored procedure. Rather than harass him to do it differently I set up an application role with access rights to support the query, and the application acitvated the role if and as necessary.This, however, messed up other ASP pages, as the application role didn’t have execute stored procedure rights (as I am always harsh over database access privileges), but it took a while to figure out. Say we had 20 connections open in the pool to support the web site; at any point in time, 0 to 20 of them could be working under the context of this application role, and this status is “undetectable” by the pool manager. It’d assign a connection based on server, login, database (and/or whatever else), but was completely unaware of application role status, resulting in a connection being assigned with inappropriate rights.In the end I dumped the application role and harassed the developer to change his code. If there were a way to “undo” or roll back the application role setting, this would not have been a problem. Philip

RE: SQL Server Security: Pros and Cons of Application Roles

K. Brian Kelley — Tue, 26 Aug 2003 21:43:00 GMT

The issue with cross-database access also applies to cross-database ownership chaining with respect to valid users. If a login doesn't have a valid user in a second database (and guest isn't enabled), they'll still be blocked. So it shows up again and again in the SQL Server security model.xp_cmdshell? *shudder* There is a reason it's not given out by default.

RE: SQL Server Security: Pros and Cons of Application Roles

sjtaylor — Tue, 26 Aug 2003 21:32:00 GMT

Fair coverage of the issues. We're in the process of creating a new system running on SQL Server and ARs are the only way I could see to sensibly enforce security. We've hit the issue of cross-database access, but in a way it was good as the developer was calling xp_cmdshell which was going to introduce more problems than it solved.SJT

SQL Server Security: Pros and Cons of Application Roles

K. Brian Kelley — Sun, 24 Aug 2003 00:00:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/bkelley/sqlserversecurityprosandconsofapplicationroles.asp