Impersonate User or Login Mapped to Certificate in SQL Server

certificateimpersonationSecuritysignaturesql server

Assume that there is a certificate created in database

create certificate certName
    with subject = 'subj';
GO

And a user mapped to this certificate

create user userName
    from certificate certName;
GO

Trying to impersonate this user directly

execute as user = 'userName';
GO

or specifying user in the execute as clause of the module

create procedure procName
with execute as 'userName'
as
    set nocount on;
GO

returns error

Msg 15517, Level 16, State 1 …
Cannot execute as the database
principal because the principal "userName" does not exist, this type
of principal cannot be impersonated, or you do not have permission.

However, I was not able to find this restriction mentioned in documentation (here and here), where the only relevant statement seems is

user_name must exist in the current database and must be a singleton
account. user_name cannot be a group, role, certificate, key, or
built-in account, such as NT AUTHORITY\LocalService, NT
AUTHORITY\NetworkService, or NT AUTHORITY\LocalSystem.

Is it possible to impersonate user (or login) mapped to certificate or not?

Best Answer

Logins and Users created from Certificates and Asymmetric Keys cannot be impersonated. They are merely proxies for a set of permissions that will be added to any module that is signed with the same Certificate or Asymmetric Key.

Also, it doesn't really make much sense to impersonate these Logins and Users because:

  1. these proxy permissions are added to the current security context (of an executing module that has been signed) instead of replacing the security context, which is what Impersonation does. It is an entirely different approach, not just a different mechanism to essentially the the same thing.
  2. module signing is not meant for ad hoc queries. Part of what makes module signing a more secure approach than Impersonation is that the person granting the elevated permissions (by signing the module to associate it with an additional set of permissions) knows what the code is doing, and that once the code is given the elevated permissions, it cannot be altered to do something else, such as something that the person doing the module signing wouldn't approve of. This is handled by signatures being dropped when any change is made to a signed module, and the fact that the text of the module (including the optional EXECUTE AS clause of the CREATE statement) is used to create the signature. If the text changes, then the signature won't match.

In the end, using Logins and/or Users created from Asymmetric Keys and Certificates replace the need for Impersonation.

Still, if looking for a more official indicator, the MSDN page for CREATE USER states:

Users that cannot authenticate These users cannot login to SQL Server or SQL Database.

  • User without a login. Cannot login but can be granted permissions. CREATE USER CustomApp WITHOUT LOGIN;
  • User based on a certificate. Cannot login but can be granted permissions and can sign modules. CREATE USER TestProcess FOR CERTIFICATE CarnationProduction50;
  • User based on an asymmetric key. Cannot login but can be granted permissions and can sign modules. CREATE User TestProcess FROM ASYMMETRIC KEY PacificSales09;

However, not being able to login/authenticate does not mean "cannot impersonate", given that Users without a login are in that list, and you can do EXECUTE AS USER='{user_without_login}';.

The MSDN page for CREATE LOGIN states:

Logins created from certificates or asymmetric keys are used only for code signing. They cannot be used to connect to SQL Server.

While the second sentence repeats what the CREATE USER documentation says, the first sentence is a bit more specific: they are only for code signing.

Finally, the statement of restrictions that you found in the documentation for both EXECUTE AS and the EXECUTE AS clause are correct, even though they are worded in a confusing manner. They state (bold emphasis added):

name must be a singleton account, and cannot be a group, role, certificate, key, or built-in account, such as NT AUTHORITY\LocalService, NT AUTHORITY\NetworkService, or NT AUTHORITY\LocalSystem.

That quote was taken from the EXECUTE AS documentation, and the EXECUTE AS Clause documentation (nearly the same wording) is quoted in the question. While both of those pages do use the terms "certificate" and "key", they are really meaning "Certificate-based Login/User" and "Asymmetric Key-based Login/User" respectively. I am assuming this based on the fact that the list of principal types given are all found in sys.server_principals (for Logins) and sys.database_principals (for Users), and neither Certificates nor Asymmetric Keys would be listed in either of those system catalog views.

Related Question