This post is based on an old presentation I gave several years back. A video of the presentation used to be available here, but today I couldn't get it to work, so I am attempting to make available most of the information from the presentation within this post. Keep in mind that the demo associated with this presentation is also available here. And some of these concepts may have been covered in more detail elsewhere on my blog:

Basic SQL Server Security concepts - logins, users, and principals
Basic SQL Server Security concepts - permissions and special principals: sa, dbo, guest
SQL Server 2005: procedure signing demo
SQL Server 2005: An example for how to use counter signatures

So, here it goes - brace yourselves because this is going to be a long post.

Login Token and User Token

The execution context is the basis for authorization checks and is determined through the login and user tokens.

These tokens contain various information, but at a high level each token contains:

  1. One principal as primary identity
  2. Zero or more principals as secondary identities (roles or groups)

At server scope we get the login token, whose content can be examined via sys.login_token. When a database is accessed, a database scoped token is created based on the content of the login token - this is the user token, whose content can be examined via sys.user_token.

Impersonation

Impersonation allows us to change the current execution context, by setting a new active login token and user token.

Application roles provide a database scoped context, but the impersonation features introduced in SQL Server 2005 give us alternatives to using application roles. One of the most obvious benefits of the new impersonation methods is the elimination of the need to manage an application role password.

Impersonation Models

SETUSER, introduced in SQL Server 2000 should be avoided going forward. It requires high privileges (sysadmin) and impersonations are not stackable.

The EXECUTE AS commands are making SETUSER obsolete and provide access to the new impersonation model introduced in SQL Server 2005. EXECUTE AS commands allow impersonation at two levels: server level (also known as login impersonation) and database level (aka user impersonation).

Impersonation Levels

At server level, there is no difference between a token obtained through authentication and one obtained through impersonation. Note however that Windows login impersonation is bounded to SQL Server level.

At database level, impersonation is a bit more complex: Inside the database where the impersonation took place we get a full user token; at server level, however, we get a stripped down login token; finally, outside the database where the impersonation took place, we get no access (we call this "sandboxing"), but this default behavior can be changed and the mechanisms that allow us to leave the sandbox will be covered further down this post.

Impersonation Methods

There are two ways of performing impersonation: explicit and implicit.

Explicit impersonation is also known as standalone impersonation. Permissions for performing such impersonation are checked against the caller's context, at run time. The EXECUTE AS LOGIN command allows us to perform server level impersonation and it requires the IMPERSONATE ON LOGIN permission on the impersonated login. The EXECUTE AS USER command allows us to perform database level impersonation and it requires the IMPERSONATE ON USER permission on the impersonated user.

Explicit impersonations are stackable, which means we can get back to the previous context by calling the REVERT command. In stack terms, EXECUTE AS pushes a new execution context frame on the execution context stack, while REVERT pops the top execution context frame; the current execution context is always being determined by the execution context frame at the top of the stack. The REVERT command must be called in the same database where the context was originally set (so it can leave us in a situation where the previous execution context is meaningful). It is not allowed to revert a context that was set outside the current module, but we will see later that it is possible to push the caller's context within a module. REVERT does not require any permission for executing it. Finally, when a module ends execution, the context is always automatically reverted to the context set before the module call.

There are also a number of options that allow us to replace application roles. The WITH NO REVERT option allows a context to be set that cannot be reverted - REVERT will simply do nothing if it is invoked. The WITH COOKIE INTO option allows us to get back a cookie value which can be passed back to REVERT - this option allows only the setter of the context to have the knowledge needed (the cookie value) to be able to revert the context. It is also possible to create users without an associated login, via CREATE USER ... WITHOUT LOGIN command option.

At any point, it is possible to find out the original server level context via the ORIGINAL_LOGIN() builtin function. This value is also always shown in traces, to allow an administrator to more easily track the original context.

Implicit impersonation is also referred to as module level impersonation. This is specified at the time the module is created and takes effect later when the module is executed. The impersonation level is implied by the respective module type. For example, impersonation is performed at server level for server triggers, but at database level for stored procedures, functions, database triggers, queues, etc.

In terms of permissions, the IMPERSONATE permission is tested against the principal that is modifying the module. For the principal that calls a module, only the EXECUTE permission on that module is being checked. Of course, context is always reverted once a module completes its execution.

The default option (when none is specified) is to perform EXECUTE AS CALLER - this is the same behavior that was available in older versions of SQL Server (the only behavior). We can of course explicitly specify the context that the module should execute as, which requires IMPERSONATE permission on that principal. And then we have two special options - EXECUTE AS SELF and EXECUTE AS OWNER which behave as their names suggest (EXECUTE AS SELF is useful for automation, not for the unlikely case that a user has forgot his context). EXECUTE AS OWNER will require IMPERSONATE permission on the module's owner - if there is no explicit owner marked for the module, the schema owner is the owner of that module - this means that later, if the schema of the module is changed, the action can only be accomplished by someone that has IMPERSONATE permission on the new schema's owner.

There is a special form of explicit impersonation that can be used within a module that does implicit impersonation to execute a portion of that module under the caller's context - EXECUTE AS CALLER. This will change the context to the caller's context and the important keyword here is change - this does not revert the implicitly impersonated context. To get back to the previous context, REVERT can be called. It is very important to note that EXECUTE AS CALLER does not require any permission check.

Cross database impersonation

Earlier, we mentioned that database impersonation is sandboxed, but there are mechanisms that allow us to leave the sandbox. Now is the time to look closer at these mechanisms.

To allow a user token to be valid outside the database where it was set, an authenticator can be added to the token. The authenticator will vouch for the content of the token, and if that authenticator is trusted in another database, then the token will be honored there; otherwise it will be rejected. Authenticating capability is managed via the AUTHENTICATE and AUTHENTICATE SERVER permissions.

There are two ways of setting authenticators. The first one is simpler, but less flexible - it is based on turning on the TRUSTWORTHY option of a database, which in turn allows its DBO to act as an authenticator. The second method requires more steps to set up, but it allows for much greater flexibility and control granularity - it is based on using certificates as authenticators.

TRUSTWORTHY setting

This is a per-database setting which indicates that contexts set in the database will have the DBO as authenticator. Only a sysadmin can change the TRUSTWORTHY setting on a database and by default, this setting is OFF. If a database would be attached to a server, this setting would also be set to OFF at attach time. The command for changing this setting is ALTER DATABASE <database_name> SET TRUSTWORTHY = {ON | OFF}.

Setting the DBO as authenticator is the first step. The second step is for the target database to express trust into that DBO's role as an authenticator. This can be accomplished in one of  two ways. The first way (more granular) is by granting the AUTHENTICATE permission in the target database to the user mapped to the login that maps to DBO in the source database. The second way is to grant AUTHENTICATE SERVER permission to the login mapped to DBO in the source database - this is a more powerful setting that is equivalent to granting AUTHENTICATE in each database of the server. AUTHENTICATE SERVER  is a very powerful permission, so only members of sysadmin are allowed to grant it.

So, this method consists of two steps to enable cross-database access from a source database to a target database:

  1. the source database needs to have the TRUSTWORTHY setting turned ON so that its DBO becomes authenticator for all tokens originating from that database
  2. the DBO must be trusted as authenticator in the target database by being granted AUTHENTICATE permission there (or by being granted AUTHENTICATE SERVER at the server level, but this approach should be avoided unless there is a good reason to use it)

Certificates and asymmetric keys as authenticators

This method allows us to set certificates (or asymmetric keys) as authenticators by signing modules that perform implicit impersonation. The certificate must exist in the source database and either in the target database (when trust is established using AUTHENTICATE) or in the master database (if using the more powerful AUTHENTICATE SERVER permission). With this method, the TRUSTWORTHY setting is not needed - we use the certificates as authenticators, so we don't need the dbo to be marked as an authenticator as well.

The way we grant AUTHENTICATE permission to certificates is by mapping them to users or logins and then granting those principals the desired permissions. The users and logins mapped to certificates do not provide an access point to the server and are only used for associating permissions with certificates.

So, this method relies only on signing modules which adds the certificate as an authenticator and then in establishing trust into that specific certificate as an authenticator, by mapping it to a user in the target database and granting it AUTHENTICATE permission. Again, the alternative of mapping the certificate to a login in the master database and granting that login AUTHENTICATE SERVER should be avoided unless there are good reasons to use it.

Module signing

Either certificates or asymmetric keys can be used to sign modules. Signing attaches a cryptographic signature to the module. This impacts execution context in two ways:

  1. if the module does implicit impersonation, the certificate will be added as authenticator
  2. if a principal is mapped to the certificate, it becomes a secondary identity of the token used during the execution of the module

A signature affects the execution context only if it is valid - changing a signed module requires re-signing it to keep the signature functional.

Also, by default, a signature only affects the execution context within the scope of the signed module. To sign, CONTROL permission on the respective certificate is required.

Signature validation is performed automatically by SQL Server. Mapping principals to certificates or asymmetric keys is the method through which we can associate permissions to those certificates and asymmetric keys.

Signatures are effective only as long as the private keys are well protected. To achieve this, keep the following in mind:

  • The grantor of permissions should be the owner of the certificate
  • If the grantor is different from the owner, then the owner has expressed trust in the grantor to not misuse the certificate when it granted them CONTROL permission on the certificate
  • Instead of providing the ability to sign to others, it is better to perform the signing yourself and just provide the signature to others - that signature can then be applied using the WITH SIGNATURE clause, which doesn't require CONTROL permission on the related certificate

Earlier, I mentioned that a signature affects the execution context only within the scope of the signed module. So if a signed module calls into another, the signature is lost during the call to the second module. To change this default behavior and allow a signature to last during calls to other modules, we can countersign the other modules. A countersignature does not change execution context - it is just a mechanism that allows signatures to persist outside their original scope. A countersignature will only carry over a signature made with the same certificate as the countersignature.

Back to Login Token and User Token

With this additional information, we can flesh out more the earlier description of the contents of a token. A token can thus contain:

  1. One principal as primary identity
  2. Zero or more principals as secondary identities (roles and groups, but also principals mapped to certificates or asymmetric keys)
  3. Zero or more authenticators (DBO or certificates or asymmetric keys)

Additional Resources

For more information, also check these MSDN articles:

Understanding Context Switching
Extending Database Impersonation by Using EXECUTE AS