Application Security and Application Roles

The security system in Microsoft® SQL Server™ is implemented at the lowest level, the database itself. This is the best, most robust method for controlling user activities regardless of the application used to communicate with SQL Server. However, you may have situations when security controls must be customized to accommodate the special requirements of an individual application, especially when dealing with complex databases and databases with large tables.

Additionally, you may want users to be restricted to accessing data only through a specific application without the ability to access data directly, for example using SQL Server Query Analyzer or Microsoft Excel. This prevents a user from connecting to SQL Server using an application such as SQL Server Query Analyzer and executing a poorly written query, which affects the performance of the whole server.

SQL Server accommodates this situation through the use of application roles. The fundamental differences between standard and application roles are:

• Application roles contain no members. Users, Microsoft Windows NT® groups, and roles cannot be added to application roles; the permissions of the application role are gained when the application role is activated for the user’s connection through a specific application(s). A user’s association with an application role is due to being capable of running an application that activates the role, rather than being a member of the role.
• Application roles are inactive by default and require a password to be activated by using the sp_setapprole system stored procedure. The password can be provided by the user, for example, through an application prompt, but it is more likely that the password is incorporated within the application. The password can be encrypted as it is sent to SQL Server.
• When an application role is activated for a connection by the application, the connection permanently loses all permissions applied to the login, user account, or other groups or database roles in all databases for the duration of the connection. The connection gains the permissions associated with the application role for the database in which the application role exists. Because application roles are applicable only to the database in which they exist, the connection can gain access to another database only by virtue of permissions granted to the guest user account in the other database. Therefore, if the guest user account does not exist in a database, the connection cannot gain access to that database. If the guest user account does exist in the database but permissions to access an object are not explicitly granted to guest, the connection cannot access that object regardless of who created the object. The permissions the user gained from the application role remain in effect until the connection logs out of SQL Server.

Important It is necessary for a connection to lose default permissions applied to the login/user account or other groups or database roles in all databases for the duration of the connection and gain the permissions associated with the application role to ensure that all the functions of the application can be performed. For example, if a user is usually denied access to a table that the application must access, then the denied access should be revoked for the user to successfully use the application. Application roles overcome any conflicts with user’s default permissions by temporarily suspending the user’s default permissions and assigning them only the permissions of the application role.

Application roles allow the application to take over the responsibility of user authentication, rather than SQL Server. However, because SQL Server still needs to authenticate the application when it accesses databases, the application must provide a password because there is no other way to authenticate an application.

If ad hoc access to a database is not required, users and Windows NT groups do not need to be granted any permissions because all permissions can be assigned by the applications they use to access the database. In such an environment, assuming access to the applications is secure, standardizing on one system-wide password assigned to an application role is possible.

There are several options for managing application role passwords without hard-coding them into applications. For example, an encrypted key stored in the registry (or the SQL Server database), for which only the application has the decryption code, can be used. The application reads the key, decrypts it, and uses the value to set the application role. Using the Multiprotocol Net-Library, the network packet containing the password can also be encrypted. Additionally, the password can be encrypted, before being sent to SQL Server, when the role is activated.

When an application user connects to SQL Server using Windows NT Authentication Mode, an application role is a good way to set the permissions the Windows NT user has in a database when using the application. This allows Windows NT auditing of the user account and control over user permissions, while they use the application, to be easily maintained.

If SQL Server Authentication is used and auditing user access in the database is not required, it can be easier for the application to connect to SQL Server using a predefined SQL Server login. For example, an order entry application authenticates users running the application itself, and then connects to SQL Server using the same OrderEntry login. All connections use the same login, and relevant permissions are granted to this login.

Note Application roles work with both authentication modes.

Example

As an example of application role usage, a user Sue runs a sales application that requires SELECT, UPDATE, and INSERT permissions on the Products and Orders tables in database Sales to work, but she should not have any SELECT, INSERT, or UPDATE permissions when accessing the Products or Orders tables using SQL Server Query Analyzer or any other tool. To ensure this, you could create one user-database role that denies SELECT, INSERT, or UPDATE permissions on the Products and Orders tables, and add Sue as a member of that database role. You can then create an application role in the Sales database with SELECT, INSERT, and UPDATE permissions on the Products and Orders tables. When the application runs, it provides the password to activate the application role by using sp_setapprole, and gains the permissions to access the Products and Orders tables. If Sue tries to log in to SQL Server using any tool except the application, she will not be able to access the Products or Orders tables.

To create an application role

To set an application role

To change the password of an application role

To remove an application role