Application Roles
An application role is a database principal that enables an application to run with its own, user-like privileges. You can use application roles to allow access to specific data to only those users that 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 activated 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 2005, application roles cannot access server-level metadata because they are not associated with a server-level principal. To disable this restriction, allowing 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:
- A user executes a client application.
- The client application connects to an instance of SQL Server as the user.
- The application then executes the sp_setapprole stored procedure with a password known only to the application.
- If the application role name and password are valid, the application role is activated.
- 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 activating an application role is to disconnect and reconnect to SQL Server. In SQL Server 2005, sp_setapprole has a new option that creates a cookie that contains context information before the application role is activated. 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).
.gif "ms190998.security(en-US,SQL.90).gif") Security Note: |
|---|
| The Microsoft ODBC encrypt option is not supported by SqlClient. When you are transmitting secrets over a network, use Secure Sockets Layer (SSL) or IPSec to encrypt the channel. If you must persist credentials within the client application, encrypt the credentials by using the crypto API functions. In SQL Server 2005, the parameter password is stored as a one-way hash. |
See Also
Concepts
Understanding Context Switching
Other Resources
sp_setapprole (Transact-SQL)
CREATE APPLICATION ROLE (Transact-SQL)