Application Roles: What Are They, Really?

Application Roles offer a better, more secure way for users to gain access to SQL Server data. Here's why.

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.

There are pros and cons associated with either choice, but I want to focus on a specific con of each user having a 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 roles is to provide a best of both worlds scenario for application and user security.

Here's how application roles 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 log in 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.

About the Author

Eric Johnson, SQL Server MVP, is the owner of Consortio Services in Colorado Springs providing IT systems management and technology consulting. He is also the President of the Colorado Springs SQL Server User Group. He can be contacted at www.consortioservices.com.

Featured

  • Microsoft and SAP Enhance Partnership with Teams Integration

    Microsoft and SAP this week described continuing partnership efforts on Microsoft Azure, while also planning a Microsoft Teams integration with SAP's enterprise resource planning product and other solutions.

  • Blue Squares Graphic

    Microsoft Previews Azure IoT Edge for Linux on Windows

    Microsoft announced a preview of Azure IoT Edge for Linux on Windows, which lets organizations tap Linux virtual machine processes that also work with Windows- and Azure-based processes and services.

  • How To Automate Tasks in Azure SQL Database

    Knowing how to automate tasks in the cloud will make you a more productive DBA. Here are the key concepts to understand about cloud scripting and a rundown of the best tools for automating code in Azure.

  • Microsoft Open License To End Next Year for Government and Education Groups

    Microsoft's "Open License program" will end on Jan. 1, 2022, and not just for commercial customers, but also for government, education and nonprofit organizations.

comments powered by Disqus