Edit

Share via

Application Roles

  • Article

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

An application role is a database principal that enables an application to run with its own, user-like permissions. You can use application roles to enable access to specific data to only those users who connect through a particular application. Unlike database roles, application roles contain no members and are inactive by default. Application roles are enabled by using sp_setapprole, which requires a password. Because application roles are a database-level principal, they can access other databases only through permissions granted in those databases to guest. Therefore, any database in which guest has been disabled is inaccessible to application roles in other databases.

In SQL Server, application roles can't access server-level metadata because they aren't associated with a server-level principal. To disable this restriction and thereby allow application roles to access server-level metadata, set the global trace flag 4616 using -T4616 or DBCC TRACEON (4616, -1). If you prefer to not enable this trace flag, you can use a certificate-signed stored procedure to allow application roles to view server state. For sample code, see this sample script on GitHub.

Connect with an Application Role

The following steps make up the process by which an application role switches security contexts:

  1. A user executes a client application.

  2. The client application connects to an instance of SQL Server as the user.

  3. The application then executes the sp_setapprole stored procedure with a password known only to the application.

  4. If the application role name and password are valid, the application role is enabled.

  5. At this point, the connection loses the permissions of the user and assumes the permissions of the application role.

The permissions acquired through the application role remain in effect for the duration of the connection.

In earlier versions of SQL Server, the only way for a user to reacquire its original security context after starting an application role is to disconnect and reconnect to SQL Server. Beginning with SQL Server 2005 (9.x), sp_setapprole has an option that creates a cookie. The cookie contains context information before the application role is enabled. The sp_unsetapprole stored procedure then uses the cookie to revert the session to its original context. For information about this new option and an example, see sp_setapprole (Transact-SQL) and sp_unsetapprole (Transaction-SQL).

Important

The ODBC encrypt option is not supported by SqlClient. When you are transmitting confidential information over a network, use Transport Layer Security (TLS), previously known as Secure Sockets Layer (SSL), or IPsec to encrypt the channel. If you must persist credentials in the client application, encrypt the credentials by using the crypto API functions. In SQL Server 2005 (9.x) and later versions, the parameter password is stored as a one-way hash.

Task Type
Create an application role. Create an Application Role and CREATE APPLICATION ROLE (Transact-SQL)
Alter an application role. ALTER APPLICATION ROLE (Transact-SQL)
Delete an application role. DROP APPLICATION ROLE (Transact-SQL)
Using an application role. sp_setapprole (Transact-SQL)

See also

Securing SQL Server