Creating Application Roles in SQL Server (ADO.NET)

Application roles provide a way to assign permissions to an application instead of a database role or user. Users can connect to the database, activate the application role, and assume the permissions granted to the application. The permissions granted to the application role are in force for the duration of the connection.

Security Note
Application roles are activated when a client application supplies an application role name and a password in the connection string. They present a security vulnerability in a two-tier application because the password must be stored on the client computer. In a three-tier application, you can store the password so that it cannot be accessed by users of the application.

Application Role Features

Application roles have the following features:

• Unlike database roles, application roles contain no members.

• Application roles are activated when an application supplies the application role name and a password to the sp_setapprole system stored procedure.

• The password must be stored on the client computer and supplied at run time; an application role cannot be activated from inside of SQL Server.

• The password is not encrypted. Beginning with SQL Server 2005, the parameter password is stored as a one-way hash.

• Once activated, permissions acquired through the application role remain in effect for the duration of the connection.

• In SQL Server 2000, you cannot switch the execution context back to the original caller. Therefore you must turn off connection pooling in order to use application roles. For more information, see SQL Server Connection Pooling (ADO.NET).

• The application role inherits permissions granted to the public role.

• If a member of the sysadmin fixed server role activates an application role, the security context switches to that of the application role for the duration of the connection.

• If you create a guest account in a database that has an application role, you do not need to create a database user account for the application role or for any of the logins that invoke it. Application roles can directly access another database only if a guest account exists in the second database

• Built-in functions that return login names, such as SYSTEM_USER, return the name of the login that invoked the application role. Built-in functions that return database user names return the name of the application role.

The Principle of Least Privilege

Application roles should be granted only required permissions in case the password is compromised. Permissions to the public role should be revoked in any database using an application role. Disable the guest account in any database you do not want callers of the application role to have access to.

Application Role Enhancements

Since the release of SQL Server 2005, the execution context can now be switched back to the original caller after activating an application role, removing the need to disable connection pooling. The sp_setapprole procedure has a new option that creates a cookie, which contains context information about the caller. You can revert the session by calling the sp_unsetapprole procedure, passing it the cookie.

Application Role Alternatives

Application roles depend on the security of a password, which presents a potential security vulnerability. Passwords may be exposed by being embedded in application code or saved on disk.

For versions of SQL Server 2005 and later, you may want to consider the following alternatives.

• Use context switching with the EXECUTE AS statement with its NO REVERT and WITH COOKIE clauses. You can create a user account in a database that is not mapped to a login. You then assign permissions to this account. Using EXECUTE AS with a login-less user is more secure because it is permission-based, not password-based. For more information, see Customizing Permissions with Impersonation in SQL Server (ADO.NET).

• Sign stored procedures with certificates, granting only permission to execute the procedures. For more information, see Signing Stored Procedures in SQL Server (ADO.NET).

External Resources

For more information, see the following resources.

See Also

Concepts

Application Security Scenarios in SQL Server (ADO.NET)

Other Resources

Securing ADO.NET Applications

Overview of SQL Server Security (ADO.NET)

ADO.NET Managed Providers and DataSet Developer Center