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.
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.
CREATE MASTER KEY ENCRYPTION BY PASSWORD ='YourVeryLongSecurePassword!'
GO
ALTER MASTER KEY
ADD ENCRYPTION BY SERVICE MASTER KEY
GO
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.
CREATE CERTIFICATE ServiceBrokerCert_SQL1
WITH SUBJECT = 'Service Broker Certificate...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
',
START_DATE = '1/1/2009',
EXPIRY_DATE = '12/31/2099'
GO
Next create another certificate with a different name within the database on the second instance.
CREATE CERTIFICATE ServiceBrokerCert_SQL2
WITH SUBJECT = 'Service Broker Certificate',
START_DATE = '1/1/2009',
EXPIRY_DATE = '12/31/2099'
GO
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]
AUTHORIZATION [sa]
STATE=STARTED
AS TCP (LISTENER_PORT = 1234, ;LISTENER_IP = ALL)
FOR SERVICE_BROKER(MESSAGE_FORWARDING = DISABLED,
MESSAGE_FORWARD_SIZE = 10,
AUTHENTICATION = CERTIFICATE ServiceBrokerCert_Cart,
ENCRYPTION = REQUIRED ALGORITHM RC4)
GO
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.
BACKUP CERTIFICATE ServiceBrokerCert_SQL1 TO
FILE='C:\ServiceBrokerCert_SQL1.cer'
We then backup the ServiceBrokerCert_SQL2 certificate to a file on the C drive of the SQL2 server.
BACKUP CERTIFICATE ServiceBrokerCert_SQL2 TO
FILE='C:\ServiceBrokerCert_SQL2.cer'
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.
CREATE CERTIFICATE ServiceBrokerCert_SQL1
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)
[IMAGE]
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.
ABOUT THE AUTHOR:
[IMAGE]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. Denny uses these skills on a regular basis in his current role as a Sr. Database Administrator and Architect at Awareness Technologies. Denny currently holds several Microsoft certifications related to SQL Server and is a Microsoft MVP. He is a member of PASS and Quest Software's Association of SQL Server Experts and has written numerous technical articles on SQL Server management.
Check out his blog:
SQL Server with Mr. Denny
