SQL Server Service Broker (SSBS) is a new architecture (introduced with SQL Server 2005 and enhanced further in SQL Server 2008 and later versions) that allows you to write asynchronous, decoupled, distributed, persistent, reliable, scalable and secure queuing/message based applications within the database itself.
In my last couple of articles I talked about different Service Broker objects and how to develop Service Broker applications. In this article we will be looking into different types and different layers of security that Service Broker provides during communication and while accessing the Service Broker objects.
Problem Statement
When we talk of communicating messages back and forth between Initiator and Target we need to ensure that communication is secure and no one on the way can temper with messages being sent. So what are the security features available for Service Broker to ensure secured communications?
Service Broker Security
Service Broker provides a robust infrastructure for security. At first level, SQL Server itself ensures that only authorized users can access the Service Broker objects and can send messages to the Service Broker services. Whatever operations each user performs it occurs under a specific security context of the security/database principal.
On second level, applications while sending messages may use either transport security, dialog security or both across instances. Both of these security methods provide a different and distinct level of security.
Dialog Security
Service Broker Dialog Security ensures authenticated and encrypted messages communication between two services (as opposed to between instances as in case of Transport Security). It uses end-to-end encryption of messages (encrypts each individual message when it leaves from the sending instance until it reaches the destination instance) and does the message integrity check at the other end, this way it detects any tempering or alteration in the messages in transit. Service Broker Dialog security helps the participant in the conversation to identify the other participant in the conversation. Dialog Security is used in the scenario where confidential or sensitive data needs to be transmitted over an untrusted network.
Now you might be wondering where this end-to-end encryption is different from point-to-point encryption provided by Transport Security (discussed in detail in the next section). Well there might be a situation where your one SQL Server instance would not be able to communicate directly with another SQL Server instance (might be a reason that another instance is in another domain) and hence you need to have a forwarder instance in between. Now in this scenario if you use point-to-point security the forwarder will be able to intercept the message which you would not want and hence end-to-end encryption is required in this scenario. In this end-to-end encryption only the initiating service database and target service database need to have the access on the certificate used for encryption. That is to say, the forwarder does not need to have it as it simply forwards the messages and no decryption/encryption happens at forwarder instance.
Service Broker uses Dialog Security by defaults for its conversation; though it can be explicitly changed to not use it by adding ENCRYPTION = OFF clause on the BEGIN DIALOG CONVERSATION statement. Please note, if a remote service binding exists for the target service then ENCRYPTION = OFF clause on the BEGIN DIALOG CONVERSATION statement will have no impact and Service Broker will still be using dialog security.
Dialog Security works either in Full security or Anonymous security types. In the Full security, the initiator of the dialog must trust the target and must be able to verify the identity of the target and vice versa. This happens as certificate from each side is shared with other side along with its public key (by retaining private key). While sending messages, the Initiator uses the public key provided by target for encryption and on the target the Service Broker uses the private key for decryption. The same applies when the messages come from the other side. However, in Anonymous security type, Service Broker identifies the target service to the initiating service but does not identify the initiating service to the target service as certificate from target is shared with Initiating service and not vice versa.
As the target service cannot verify the identity of the initiating service, operations on behalf of the initiating service run as a member of the fixed database role [Public] in the target database. To learn more about it click here.
Please note messages that are to be transmitted within a SQL Server instance are never encrypted as it remains in the same memory space used by SQL Server. It means Service Broker uses dialog security for encrypting messages only when they are sent outside current SQL Server instance.
Transport Security
Service Broker Transport Security establishes and authenticates network connection between two instances (instance as a whole instead of controlling the access to individual services within an instance). It helps to restrict network connections to a database. It detects and prevents unauthorized network connections, detects any tempering in the message in the transit. It also provides point-to-point (as opposed to end-to-end conversation provided by Dialog Security) encryption of your messages whenever needed. Transport Security applies to network connections (secures the TCP/IP connection) and hence it does not provide authentication for each individual conversation rather it is applicable to all the conversations between said transport endpoints. In other words, the difference between transport security and dialog security is, transport security works between network/transport endpoints whereas dialog security works between dialog/service endpoints.
Notes
· How is message integrity ensured?
Service Broker creates an MD5 signature of the message body. Then it encrypts the signature with session key that is generated for that particular conversation and includes the signature in the message headers. On the destination, Service Broker decrypts the messages and then compares the signature in the message header with a new signature computed over the actual content received. If there is any mismatch in the comparison it indicates integrity check failure (message has been damaged during transmission), in that case it discards the message and does not send the acknowledgement back to sender. You can use below query to check which all conversations are being encrypted and decrypted. If the values are other than 00000000-0000-0000-0000-000000000000 it indicates encryption is being used:
SELECT inbound_session_key_identifier, outbound_session_key_identifier
FROM sys.conversation_endpoints
· Service Broker service endpoint supports both certificate-based authentication (used mainly in the scenario where you have endpoints in different domains) and Windows authentication. See my last article for more on Service Broker authentication.
· If you want to maintain the highest level of security, you can use both transport and dialog security together. Though it will have overhead because of the fact the messages will be encrypted twice.
Conclusion
In this article I talked about Service Broker security in details. I showed you how transport and dialog security work and how can they be used in your applications development.
Resources
MSDN Service Broker Communication Protocols
MSDN Certificates and Service Broker
MSDN Service Broker Transport Security
MSDN Service Broker Dialog Security
