SQL Server Execution Context and Security - more on Policy Based Management principal ##MS_PolicySigningCertificate##)
In continuation to this SQL Server 2008 - Policy Based Management - usage of ##MS_PolicyEventProcessingLogin## under Security, Logins in Management Studio post in the past there is more to talk about Executing Context and Security policy within SQL Server (since 2005 version).
As you may be aware that when user connects to teh SQL Server it will have a seperate user session by allocating memory, the process by that user which executes the statement SQL Server uses that session’s user id, permission, password to execute the query, this is called execution context. Internally SQL Server manages this context with security tokens, login & user tokens.
A security token for a user or login contains the following:
- One server or database principal as the primary identity
- One or more principals as secondary identities
- Zero or more authenticators
- The privileges and permissions of the primary and secondary identities
As you can see the 2 tokens of User & Login are valid for certain period of time such as, login token is valid across the instance of SQL Server and user token is valid only for a specific database. To see what your SQL Server login token consists run: SELECT principal_id, sid, name, type, usage FROM sys.login_token; and for user token: SELECT principal_id, sid, name, type, usage FROM sys.user_token;
Finally to wrap up on this execution context topic, a session can be explicitly changed by specifying a user or login name in an EXECUTE AS statement. The execution context of a module, such as a stored procedure, trigger, or user-defined function, can be implicitly changed by specifying a user or login name in an EXECUTE AS clause in the module definition.
To steer about server prinicpals that are installed within SQL Server which are used for internal system use only, the following principals should not be deleted:
- ##MS_SQLResourceSigningCertificate##
- ##MS_SQLReplicationSigningCertificate##
- ##MS_SQLAuthenticatorCertificate##
- ##MS_AgentSigningCertificate##
- ##MS_PolicyEventProcessingLogin##
- ##MS_PolicySigningCertificate##
- ##MS_PolicyTsqlExecutionLogin##
To concentrate more on Policy Based Management framework in SQL Server 2008 you will see that the certficate ##MS_PolicySigningCertificate## is created with the necessary set of permissions. Also you can see the policy assigned to this certificate by querying the SP sys.sp_execute_policy. By design this certificate based login cannot be impersonated (EXECUTE AS) and during the policy checkout process by PBM it will have to execute the DDL trigger and the processing of the event queue as a different user, whihc is the ##MS_PolicyEventProcessingLogin## login. As talked in the blogpost above the login is newly created for the sake of policy based management framework and by default this login is only used for impersonation and should not be used to login to the system, explicitly.
Whenever this login is used by system to process a trigger is initiated to insert event data to the table (msdb.dbo.syspolicy_execution_internal), as it states this is an internal table used by PBM framework to work further that will execute the trigger syspolicy_execution_trigger that will call further SP sp_syspolicy_execute_policy. This whole execution process is managed with a certificate based authentication that means it inherits the permissions that are granted to this certificate automatically, all this process is followed during CheckOnChange: Prevent PBM process :
Further explanation from Dan Jones about the Policy Based Management framework event processing is performed using Service Broker queue methods, whenever CheckOnChange: Log is initiated the SP sp_syspolicy_events_reader is initiated which processes the Service Broker Queue messages. The event queue is setup to run as this login meaning when it calls this sp it's running as ##MS_PolicyEventProcessingLogin## which has execute permission on the SP. The SP (sp_syspolicy_events_reader) calls msdb.dbo.sp_syspolicy_dispatch_event. This then follows the same path as Check On Change: Prevent above.