In the recent installments of our series dedicated to the most prominent features of SQL Sever 2005 Express Edition, we have been discussing mechanisms that facilitate authentication of distributed systems participating in a Service Broker conversation. As we have explained, such goal can be accomplished by employing either Windows-based protocols (NTLM or Kerberos) or digital certificates. The latter of these two technologies can also be leveraged in order to encrypt either the entire communication channel or individual dialogs. We will explore this capability in more detail starting with this article.
The Authentication process not only ensures that the identity of participants of Service Broker conversations can be verified, but also prevents the compromise of data integrity by applying checksums and signatures to messages exchanged on the transport level. However, these provisions fail to protect transmitted data from unauthorized viewing while in transit. Anyone with access to intermediary devices, along the communication path between initiator and target, can potentially capture network packets passing through and analyze their content. If you deal with confidential information and are concerned about such a possibility, you should consider incorporating encryption into your Service Broker environment.
In general, encryption is a mechanism for securing data, which applies a specially designed algorithm to it, effectively obfuscating its content by making it significantly different from the original. Use of the algorithm involves a component known as the encryption key. The process can be reversed by applying an appropriate decryption key along with a corresponding algorithm. The complexity of both algorithms and length of each key are directly proportional to the resulting protection level, but inversely proportional to the computational power necessary to complete either of these operations. Encryption can be symmetric or asymmetric, depending on whether the key used to scramble data is identical to the one used to recover it. In scenarios where data is transferred between two locations, the symmetric approach becomes problematic, since it requires transfer of the common key (which introduces the possibility of its compromise). While this can be avoided by switching to asymmetric encryption (where each of the participants has its own, separate key), the resulting performance impact makes such an arrangement unsuitable for scenarios involving larger amounts of data. There is, however, a solution that addresses both of these shortcomings (and, at the same time, takes advantage of the strengths of both methods) that relies on asymmetric keys to positively identify communication partners and securely exchange symmetric keys between them, which can subsequently be used to encrypt transferred data.
This hybrid mechanism is employed to guard confidentiality of Service Broker-related network traffic. However, depending on circumstances surrounding the origin and configuration of asymmetric keys, the resulting encryption can take place on either transport or dialog level. In the first case, keys and their respective certificates get associated with endpoints. Effectively, the obfuscation applies to all traffic originating there (which might include multiple, simultaneous dialogs, and encompass both their message headers and data). As a result, such an approach does not provide the ability to distinguish between individual services or (in particular, if message forwarding is used) guarantee end-to-end protection (at least not without encrypting and decrypting all transport-level communication for each intermediary pair of SQL Server instances, which quickly becomes computationally expensive and inefficient). With the dialog-level encryption, keys are associated with individual services, which not only allows for protecting the content of the entire conversation between every initiator and target pair with a distinct session-specific key, but it also makes it possible to uniquely identify all of them. (This, in turn, facilitates restricting permissions on SEND for each service to designated SQL Server 2005-based principals). On the other hand, in this case, only the message body is protected, with message headers (which contain such information as names of both Service Broker services, message types, or contracts) being sent in clear text. If this constitutes a security concern, you have the option of combining both encryption methods.
We will postpone discussion of the dialog-level security until the next article, focusing first on the transport-level encryption, since its implementation is considerably simpler. In all of the examples presented so far, we have been consistently avoiding its use, by disabling it at the time of endpoint creation (by assigning DISABLED value to the ENCRYPTION parameter of the CREATE ENDPOINT statement). To alter this setting, we simply have to change its value on our SQL Server 2005 instances participating in Service Broker conversations to either SUPPORTED or REQUIRED, as in the following T-SQL code:
— on srvExp01
ALTER ENDPOINT EP_SB_01_Express
FOR SERVICE_BROKER (
ENCRYPTION = REQUIRED);
— on srvEnt01
ALTER ENDPOINT EP_SB_02_Enterprise
FOR SERVICE_BROKER (
ENCRYPTION = REQUIRED);
The ENCRYPTION parameter of CREATE ENDPOINT and ALTER ENDPOINT statements can take (in addition to DISABLED, which you are already familiar with) either SUPPORTED or REQUIRED value. The actual outcome depends on a combination of settings on two endpoints, between which the Service Broker dialog is established. Having either SUPPORTED or REQUIRED on both will enforce encryption, while the presence of DISABLED on at least one side (as you realize based on the behavior we described earlier) will result in unencrypted traffic (note, however, that pairing it up with REQUIRED will trigger an error condition). In addition, you also have the option to explicitly designate the encryption algorithm (the default RC4 is significantly faster, but weaker, than its AES counterpart is). Note that any of these settings can be used with both Windows and certificate-based authentication.
With all other Service Broker components and certificate-based authentication in place, you should be able at this point to initiate a dialog using the same procedure, which we presented previously. While it is unlikely that you will notice any difference in its progress or outcome, you can verify that communication is encrypted by capturing network traffic delivered to either of the endpoints (you can use the freely downloadable Wireshark for this purpose) and examining its content.
In some scenarios, it might not be possible or practical to distribute certificates to all communication partners (for example, in an environment consisting of a large number of SQL Server 2005 Express Edition systems sending data to a central SQL Server 2005 Enterprise Edition instance). In such cases, you might want to take advantage of anonymous transport level security. While its naming has oxymoronic overtones, the underlying technology does offer a considerable degree of security (be encrypting all communication between endpoints). Its configuration is similar to the one we presented when describing full level security, since it involves generating a certificate in the master database (protected by its master key) and associating it with the certificate authentication of the local endpoint on both target and initiator side. There are however, two primary differences that distinguish the anonymous setup. Most importantly, it is no longer necessary to copy each certificate to the respective communication partner and associate it with a local security principal. In addition, you are required to grant CONNECT permissions on each endpoint to Public, which in our case takes the form:
— on srvExp01
USE master
GO
GRANT CONNECT ON ENDPOINT::EP_SB_01_Express
TO Public
— on srvEnt01
USE master
GO
GRANT CONNECT ON ENDPOINT::EP_SB_02_Enterprise
TO Public
While this configuration change means that any remote Service Broker connection will be accepted by both endpoints, you might be able to remediate potential security implications of such an arrangement by applying network level security (especially if all communication partners reside in a managed environment that allows you to restrict the flow of network traffic). The most significant benefit of this configuration is the ability to use transport level encryption to protect the confidentiality of messages transmitted between each pair of endpoints.
In the next article of this series, we will turn our attention to dialog level security, which provides considerably more flexibility in regard to authorization and encryption than its transport-level counterpart does.
