THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Eric Johnson

My Thoughts on SQL Server and Database Related Technologies.

Application Roles: You’ve seen them, but have you really ever used them?

They have been around forever, but have you really ever implemented Application Roles in SQL Server? For that matter, do you really know what they are and how to use them? In this short tip, I hope to explain just that.

First off, we need to talk briefly about how application security can be implemented in SQL Server. There is some debate over which model is better and I am not endorsing any specific one here. Regardless of whether you use SQL Server Logins or Windows Authentication, you still have to decide whether an application will use a single login to access SQL Server (and all appropriate database objects), or allow each individual user to have their own login. They each have their very own sets of pros and cons, but I want to focus on a specific con of each user having their own login. The biggest issue is that each user login has access to your server and to one or more databases. Does the user need to delete data as part of their job? If so, they will have this right whether they log in via an application or directly to the server. Often, the application controls what can and cannot be deleted based on a set of business rules; these rules usually don’t exist on the SQL Server itself. In short, if each user has their own login, they can access SQL Server directly and potentially cause some damage.

This brings us to application roles. You create them and assign permissions to them just like regular database roles but you can’t put users in them. Instead, the goal of application role is to provide a best of both worlds scenario for application and user security. Here’s how they work. You set up each user to have an account on the SQL Server with practically no rights. All they should be able to do is login to the server and run a system stored procedure called sp_setapprole. This procedure accepts a couple parameters, including the name and password for the application role. Running sp_setapprole will immediately endow the user with all the permissions that you set up on the application role for the current session only.

So what does this mean for security? As long as the password for activating the application role is only known to the application, your users will not have any rights when they login to the SQL Server directly. In order to have the permissions they need, they will be required to use the application that knows the password and can unlock the permission for the application role. Now you can have SQL Server manage individual logins and still have a secure environment that uses the rules and filters in place within your applications.

Published Wednesday, October 08, 2008 8:48 AM by ejohnson2010

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Adam Machanic said:

I have messed with app roles quite a bit and tried to implement them in a project but in the end it was impossible due to the impact on connection pooling (app roles don't work with pooling, or is it the other way around? -- either way, it's an oil and water situation)

October 8, 2008 10:43 AM
 

jbelina said:

Another problem I've heard of with App Roles is that if you had an ADO connection that was busy and the application requested another query, it would spawn another connection.  That new connection didn't have the app role set and would fail.

At one of my former employers we used a hybrid approach where we had Windows Authentication for the users and a special security system setup where individual users, or groups would have permissions specified for a given application.  The application would call a stored procedure that would return a set of parameters based on the users login.  It would then use an encrypted SQL Username and password for the application and login as that user.  So any other connections made by the application would be under one SQL User, but that SQL User could be different for different groups, and didn't have the password embedded in the application so it could change on demand.

October 22, 2008 10:39 AM
 

Haggy said:

i've got the same problems like jbelina said. Does anyone have more information about this bahvior ?

Is there any way to get ARS worged with oledb?

November 24, 2010 9:35 AM

Leave a Comment

(required) 
(required) 
Submit

About ejohnson2010

Eric (Microsoft SQL MVP) is the co-founder of Consortio Services, and the primary Database Technologies Consultant. His background in Information Technology is diverse, ranging from operating systems and hardware to specialized applications and development. He has even done his fair share of work on networks. Since IT is really just a way to support business processes, he has also acquired his MBA. All in all, he has 10 years of experience with IT, a great amount of which has been working with Microsoft SQL Server. Eric has managed and designed databases of all shapes and sizes. He has delivered numerous SQL Server training classes and webcasts as well as presentations at national technology conferences. Most recently, he presented at TechMentor on SQL Server 2005 Replication, Reporting Services, and Integration Services. In addition, he is active in the local SQL Server Community, serving as the President of the Colorado Springs SQL Server Users Group.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement