SQL Server Service Broker for secure communication

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.

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 KEYstatement. 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',
     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_SQL2TO
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 CERTIFICATEstatement, 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) 

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.

==> Setting up Transparent Data Encryption, or Backup Encryption, or basically anything else where you need to restore a certificate to another SQL Server instance can get tricky as soon as you run the instances under different service accounts.  The problem that people will usually run into is that all the example code assumes that all the instances are running under the same service account, but this isn’t always the case in the real world.  

/* Server 1 */
/* Create the master key */
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘SomeRandomSecureString’;
GO

/* Create the certificate to be used for backups */
CREATE CERTIFICATE BackupCert
WITH SUBJECT = ‘Backup Encryption Certificate’;
GO

/* Backup the master key */
BACKUP MASTER KEY TO FILE = ‘\\FileShare\DatabaseMasterKey_Master.key’
ENCRYPTION BY PASSWORD = ‘SomeRandomPwd’;

BACKUP CERTIFICATE BackupCert TO FILE = ‘\\FileShare\BackupCert.cer’
WITH PRIVATE KEY (FILE = ‘\\FileShareBackupCert.pvk’,
ENCRYPTION BY PASSWORD = ‘RandomEncryptionPwd’);
GO

/* Server 2 */
/* Create master key */
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘SomeRandomSecureString’;
GO

/* Restore the cert */
CREATE CERTIFICATE BackupCert FROM FILE = ‘\\FileShare\BackupCert.cer’
WITH PRIVATE KEY (FILE = ‘\\FileShare\BackupCert.pvk’,
DECRYPTION BY PASSWORD = ‘RandomEncryptionPwd’);

–Msg 15208, Level 16, State 6, Line 32
–The certificate, asymmetric key, or private key file is not valid or does not exist; or you do not have permissions for it.

/* Try restoring the master key instead */
DROP MASTER KEY;

RESTORE MASTER KEY FROM FILE = ‘\\FileShare\DatabaseMasterKey_Master.key’
DECRYPTION BY PASSWORD = ‘RandomEncryptionPwd’
ENCRYPTION BY PASSWORD = ‘RandomEncryptionPwd’;

–Msg 15317, Level 16, State 2, Line 39
–The master key file does not exist or has invalid format.

Now as we can see, the creation of the certificate failed, but only when running each server under its own service account. The question becomes why.

The answer is depressingly simple, NTFS permissions. By default SQL Server will make it so that the backup of the certificate is only available to the account that is running the instance that creates the certificate backup. So if you need to restore the certificate using the CREATE CERTIFICATE syntax shown in the example you’ll get an error. If you change the permissions so that the second SQL Account can read the certificate backup file you created then the restore will work.