SQL Server Application Role

Posted: March 24, 2014 in Database Security

I was recently having a discussion with a client and they were looking for recommendations on how to handle a common security issue with SQL Server. Here is there situation, they have a number of applications that connect to the database, and security was implemented by using Windows Authentication. The application required read and write access directly to the tables so this required that each of the users had direct read/write access to each of the tables in the database (starting to feel uneasy yet). It didn’t take long before people were connecting directly to the database to start making changes outside the application, or pull reports of their own. Even if the data was not sensitive, and it was… making changes outside the application created all sorts of problems.

Then answer was pretty simple and came in the form of application roles.

Access can be granted to the individual users either by each account or as a group. The permissions can be limited to allow for log on only. Once that has been completed the application role can be created using the Create Application Role syntax. Permissions can be granted accordingly to the application role that allow for the direct table access.

When this is done and someone logs on via the application the application can issue the sp_setapprole and that connection can now use the elevated permissions. If the user were to try to connect to the database without the application role the elevated permissions are not there unless they have the password for the app role and set that role for that connection.

So in other words the application becomes only access method to the data as long as permissions are not granted elsewhere to the individuals user account. This is a great way to take advantage of everything that AS has to offer in the way of account and permissions management without having to worry about opening the database up.

Comments
  1. dataflowe says:

    Hi Chris, great post and timely for me. Any chance you’re aware of a good writeup on tradeoffs between app roles vs users without logins + execute as?

  2. […] SQL Server Application Role – Chris Shaw(Blog|Twitter) […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s