Security Context of Service Broker Internal Activation

In the previous installment of our series dedicated to the most prominent features of SQL Server 2005 Express Edition, we discussed one of the Service Broker-related technologies known as internal activation. As we have explained, its primary purpose is to trigger execution of an arbitrary stored procedure associated with a Service Broker queue, whenever there is the need to process incoming messages. While such capability offers meaningful benefits, allowing you to automate communication between initiator and target, there are some caveats related to its security context that you should be aware of. In this article, we will describe their specifics and provide a couple of methods, which allow you to eliminate undesired side effects they introduce.

If you decided to customize our example illustrating the new functionality and modified the sample stored procedure, such that its scope extended beyond the database where the Service Broker constructs reside (for example, by referencing objects located in other databases or querying server-level entities, such as linked servers, master system tables, or dynamic management views), you likely to discovered that the results were different, depending on whether you executed it interactively or invoked through internal activation. Interestingly, this somewhat surprising inconsistency does not relate in any way to Service Broker characteristics, but instead is a side effect of the EXECUTE AS clause incorporated into CREATE QUEUE and ALTER QUEUE statements, used to apply activation (via WITH ACTIVATION clause) to initiator and target queues. In short (a more detailed explanation of the underlying cause is provided in the Extending Database Impersonation by Using EXECUTE AS article posted on the MSDN Web site), such behavior is intentional and put into place in order to prevent potential privilege elevation exploits. Impersonation of another user is limited to the local database, with actions that involve other databases and server-wide access defaulting to permissions granted to the guest role.

There are, however, scenarios in which cross-database access is required. In general, you have two options that allow you to work around the built-in restrictions described above. The first one involves designating the database hosting Service Broker objects (including the activated stored procedure) as trustworthy, which in our case, would be accomplished by running the following (note that you must be a member of the sysadmin fixed server role, in order to successfully execute this statement):

ALTER DATABASE dbSBEnt01
SET TRUSTWORTHY ON

In addition, you would need to ensure that a database user responsible for authenticating internally activated stored procedures (typically the database owner) gets associated with a security principal (through a designated SQL Server login), which serves an equivalent role in the database containing cross-referenced objects. Furthermore, that principal should be granted AUTHENTICATE privilege on its database (or AUTHENTICATE SERVER, if server-wide access is required). While this approach is fairly straightforward and relatively simple to implement, it lacks granularity that would allow control to both the level of access and a mechanism used to facilitate it (such as solely via a specific internally activated stored procedures). If such security exposure is not acceptable, you should consider the alternative approach, which leverages digital certificates.

The second, considerably more flexible method of mitigating shortcomings imposed by the EXECUTE AS statement is also significantly more complex. In short, it involves signing a stored procedure assigned to the target Service Broker queue with a locally generated certificate, which also gets associated with a user in the referenced database (in case of cross-database interaction) or a SQL Server login (if access to server-level objects is required). This security principal provides security context in which the activated stored procedure operates outside of its local database (and hence, it also needs to be granted appropriate object level permissions, as well as the AUTHENTICATE or AUTHENTICATE SERVER privilege). However, this extra complexity might be warranted if you take into account the benefits it yields. Since there is one-to-one relationship between the private key and its public counterpart (and the corresponding security principal), you not only have the ability to restrict permissions to absolute minimum, but also ensure that they are available exclusively via the signed stored procedure. In addition, digital signatures provide the guarantee that a stored procedure has not been modified after its signing.

This entire process can be broken into the following individual steps:

• designating the security context, in which the internally activated stored procedure will be executing by adding WITH EXECUTE AS OWNER clause, following directly the CREATE PROCEDURE or ALTER PROCEDURE, as in the following example (note that, for the sake of brevity, we have omitted the full declaration):

   CREATE PROCEDURE dbo.cspProcessqRecv
    WITH EXECUTE AS OWNER
    AS (...)
  

• creating a certificate in the database, where activation stored procedure and other Service Broker objects are located (we are assuming that the database master key already exists, since we created it in the course of one of our earlier examples. If that is not the case, you can generate it with the CREATE MASTER KEY ENCRYPTION statement or use ENCRYPTION BY PASSWORD clause in the CREATE CERTIFICATEstatement):

   -- on srvEnt01
   USE dbSBEnt01
   GO
   CREATE CERTIFICATE cert_cspProcessqRecv 
    WITH SUBJECT='cspProcessqRecv Signing Certificate'
  

• signing the activation stored procedure using the newly created certificate

   ADD SIGNATURE TO dbo.cspProcessqRecv
    BY CERTIFICATE cert_cspProcessqRecv
  

• backing up the certificate (along with its public key) into a file in order to import it afterwards into the target database

   BACKUP CERTIFICATE cert_cspProcessqRecv
    TO FILE = 'c:Tempcert_cspProcessqRecv.cer'
  

• removing the private key from the certificate to prevent its potential exploit (you can also export it to a file, using BACKUP CERTIFICATE statement including WITH PRIVATE KEYclause, and store it in a secure location)

   ALTER CERTIFICATE cert_cspProcessqRecv
    REMOVE PRIVATE KEY
  

• transferring certificate to the target database (this could be either the master database – in case system level privileges are required – or another user database if the stored procedure needs to be able to access its objects). This is accomplished by importing it from the previously generated file.

   USE anotherDB
   GO
   CREATE CERTIFICATE cert_cspProcessqRecv
    FROM FILE = 'c:Tempcert_cspProcessqRecv.cer'
  

• creating a user (or a login, if appropriate) in the target database associated with the newly imported certificate and granting AUTHENTICATE or AUTHENTICATE SERVERprivilege to it. As mentioned earlier, you will also need to grant to this security principals appropriate permissions on the target objects (specifics depend solely on the type of action that is to be performed by the activated stored procedure, so they are not included in the statements listed below).

   USE anotherDB
   GO
   CREATE USER u_cspProcessqRecv
    FROM CERTIFICATE cert_cspProcessqRecv
   GO
   GRANT AUTHENTICATE TO u_cspProcessqRecv
  

Note that you should be able to discover any potential problems caused by the EXECUTE AS statement by simply using it to interactively invoke any stored procedures you are planning to employ in Service Broker activation solutions. We are hoping that the information presented in this article will help you deal with any issues you might run into during such tests. In our next article, we will cover transactional characteristics of Service Broker conversations.

» See All Articles by Columnist Marcin Policht