مشاركة عبر

Application Roles

  • مقالة

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 work with both authentication modes. 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 will be inaccessible to application roles in other databases.

In SQL Server, application roles cannot access server-level metadata because they are not associated with a server-level principal. To disable this restriction and thereby allow application roles to access server-level metadata, set the global flag 4616. For more information, see Trace Flags (Transact-SQL) and DBCC TRACEON (Transact-SQL).

Connecting 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, sp_setapprole has an option that creates a cookie. The cookie contains context information before the application role is enabled. The cookie can be used by sp_unsetapprole to revert the session to its original context. For information about this new option and an example, see sp_setapprole (Transact-SQL).

Security note Security Note

The ODBC encrypt option is not supported by SqlClient. When you are transmitting confidential information over a network, use 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 and later versions, the parameter password is stored as a one-way hash.

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

Concepts

Securing SQL Server