By submitting my Email address I confirm that I have read and accepted the Terms of Use and Declaration of Consent.

By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.

You also agree that your personal information may be transferred and processed in the United States, and that you have read and agree to the Terms of Use and the Privacy Policy.

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.

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.

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

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.

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.

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.

ABOUT THE AUTHOR

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.

0 comments

Register

Login

Forgot your password?

Your password has been sent to:

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