Setting up SQL Server Service Broker for secure communication

The most secure way to send SQL Server Service Broker messages from instance to instance is to secure the communication using certificates. While this is the most secure method, it is quite a bit more complex to configure than normal SQL Server or Windows authentication techniques.

For the purposes of this article, we'll be sending messages from our shopping cart database to our ticketing system's database. The shopping cart database is stored on the server SQL1, while the ticketing system is running on SQL2, with each located on a different server. All of these statements should be run on the master database of each instance.

    Requires Free Membership to View

More on SQL Server security

The keys to database backup protection for SQL Server

Understanding transparent data encryption in SQL Server 2008

The first thing to configure would be the master key within each instance. This is done with the CREATE MASTER KEY statement. This password is the key to your encryption within the instance – so use a strong one. After you have created the master key you will need to modify and configure it to be encrypted by the service master key. This service master key is generated and maintained by the SQL Server instance. This allows the SQL Server instance to open the database master key for the master database without having the password configured anywhere within the instance.


After you have created the master key you can build your certificate within each database using the CREATE CERTIFICATE statement. You'll want to specify the name, subject, start date and expiration date of the certificate. This will create a certificate based on the master key of the database rather than a password. You should use more generic names if the instances are shared with more than one application on them.

WITH SUBJECT = 'Service Broker Certificate',
     START_DATE = '1/1/2009',
     EXPIRY_DATE = '12/31/2099'

Next create another certificate with a different name within the database on the second instance.

WITH SUBJECT = 'Service Broker Certificate',
     START_DATE = '1/1/2009',
     EXPIRY_DATE = '12/31/2099'

Once the certificates are done, you'll need to create the Service Broker endpoint and configure it for authentication. This is done using the CREATE ENDPOINT statement while specifying the FOR SERVICE_BROKER option and giving it a specific certificate. In this example, encryption of the communication is required, and all communications are encrypted using the RC4 encryption algorithm.

There are four different encryption schemes to select from:

  • RC4 - configures the endpoint to require the use of RC4 encryption between the instances
  • AES - configures the endpoint to require the use of AES encryption between the instances
  • AES RC4 - configures the endpoint to negotiate for an encryption algorithm using AES first, then RC4
  • RC4 AES - configures the endpoint to negotiate for an encryption algorithm using RC4 first, then AES

CREATE ENDPOINT [ServiceBrokerEndpoint]
           MESSAGE_FORWARD_SIZE = 10,
          AUTHENTICATION = CERTIFICATE ServiceBrokerCert_Cart,

Though RC4 is a weaker algorithm than AES, it is much faster. This means that it requires less CPU resources to encrypt and decrypt the data. Knowing the difference is important because using AES in a high-load environment will cost you more CPU cycles, while RC4 shouldn't be used in a high risk system where the data is extremely valuable unless it is absolutely necessary.

Once you have configured the endpoints for each server, you can then handle the certificate exchange. This is done by backing up the certificate's public key to a file and then importing the public key into the remote server. You'll first need to back up the certificate using the BACKUP CERTIFICATE statement.

In this example, we've backed up the ServiceBrokerCert_SQL1 certificate to a file on the C drive of the SQL1 server.


We then backup the ServiceBrokerCert_SQL2 certificate to a file on the C drive of the SQL2 server.


Once the files are backed up, it's time to move them to the C drive of the other server. You then need to import the certificate into the other server. This is done with the CREATE CERTIFICATE statement, but this time using the FROM FILE parameter instead of the SUBJECT, START_DATE and EXPIRY_DATE parameters that were used before.

FROM FILE='C:\ServiceBrokerCert_SQL1.cer'

Then connect back to SQL1 and import the certificate from the SQL2 server.

You can see that the certificates have been imported by querying the sys.certificates system catalog view. When you do this, you should see something similar to Figure 1.

Figure 1 (click to enlarge)

Once the certificates have been imported, you should be ready to send messages between the instances over an encrypted link using the normal SEND statements and Service Broker routes.


Denny Cherry has over a decade of experience managing SQL Server, including MySpace.com's over 175-million-user installation, one of the largest in the world. Denny's areas of expertise include system architecture, performance tuning, replication and troubleshooting. He currently holds several Microsoft certifications related to SQL Server and is a Microsoft MVP.

This was first published in July 2009

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.