SQL Server Service Broker – Security

Dialog Security vs Transport Security

There are 2 types of security in Service Broker: dialog and transport. Dialog security establishes a secure, authenticated connection between Service Broker Services or dialog endpoints. Transport security establishes an authenticated network connection between SQL Server instances or Service Broker endpoints. Clear as mud, right? Don’t worry, these are easily mixed up by both novice and experienced Service Broker admins. To illustrate, let’s go back to our taxes scenario. You’ve completed your forms, stamped your envelope and you’re ready to mail it in. You drop it in your nearest mailbox and what happens next? A postal worker will pick it up, it gets loaded into a truck and shipped between various sorting facilities (as you might have noticed I have no clue how the USPS works) until it is finally delivered to the IRS via yet another postal worker. Now, those postal workers all have the authority to transport your tax return from point to point. However, they do not have the authority to open up and read your return. That’s what transport security is. The IRS agent on the other end, though, he does have the authority to read your return. That’s dialog security.
It’s also worth noting that transport security is only needed in a distributed environment. Just like if the IRS agent lived with you, you wouldn’t need to go through the USPS. But that’s just weird.
In a Service Broker architecture, security would look like this.

Configuring Transport Security

So now that we understand the difference between dialog and transport security, let’s configure it for our solution. We’ll start with transport security. If both servers are in the same domain, we can use Windows Authentication, which makes things easy. To do this, we’ll create a login on each instance for the service account of the other instance. And then we’ll grant that login connect permission to the local Service Broker endpoint.

[sourcecode language="sql"]
--On our Taxpayer instance
USE master
GO
create login [LAB\SQLServerSB2] from WINDOWS;
GRANT CONNECT ON ENDPOINT::TaxpayerEndpoint to [LAB\SQLServerSB2];
GO
--On our IRS instance
USE master
GO
create login [LAB\SQLServerSB1] from WINDOWS;
GRANT CONNECT ON ENDPOINT::IRSEndpoint to [LAB\SQLServerSB1];
GO
[/sourcecode]

What if we weren’t in the same domain? Then, my friend, you’ll need to configure your transport security using certificates, which also means your endpoints need to be configured to use certificates for authentication.

Configuring Dialog Security

At this point, if we were to try to send a message, it probably wouldn’t go through. And you’ll throw your stressball at the wall and start screaming that this Service Broker crap is stupid and no wonder nobody uses it. And no one would blame you. Take a moment. Breathe deeply. When you’re calmer start a Profiler trace, you’ll probably see a message like this:
This message could not be delivered because the user with ID 0 in database ID 6 does not have permission to send to the service. Service name: ‘//SBDemo/Taxes/IRSService’.
The issue here is that dialog security isn’t configured yet. And there are a couple ways to fix this. The first is to not configure dialog security at all and just give everyone permission to send messages on the service.

[sourcecode language="sql"]
--On the Taxpayer server
USE [SBDemoDB1]
GO
GRANT SEND ON SERVICE::[//SBDemo/Taxes/TaxpayerService] to PUBLIC
--On the IRS server
USE [SBDemoDB2]
GO
GRANT SEND ON SERVICE::[//SBDemo/Taxes/IRSService] to PUBLIC
[/sourcecode]

Of course, if you don’t want just anyone to be allowed to send messages, you’ll want to configure dialog security properly. And if you thought you had avoided messing with certificates, think again. Yup, we’ll be using them here. The basic steps to configure dialog security are as follows:

1. Create a master key in your user database, if you haven’t already.
2. Create a user certificate
3. Take a backup of that user certificate and copy it to the remote server where that remote SQL Server can access it.
4. Create a user for the remote server, without a login.

Steps 1-4 need to be done on both servers. Here’s the code:

[sourcecode language="sql"]
--On our Taxpayer server
Use [SBDemoDB1]
GO
CREATE MASTER KEY ENCRYPTION BY
PASSWORD = 'SomePassword123#'
GO
CREATE CERTIFICATE TaxpayerCertificate
WITH SUBJECT = 'Taypayer Cert',
START_DATE = '01/01/2017',
EXPIRY_DATE = '01/01/2053'
ACTIVE FOR BEGIN_DIALOG = ON;
GO
BACKUP CERTIFICATE TaxpayerCertificate TO FILE=
'C:\Certificates\TaxpayerCertificate.cer';
GO
Create User IRSUser WITHOUT LOGIN
GO
--On our IRS Server
Use [SBDemoDB2]
GO
CREATE MASTER KEY ENCRYPTION BY
PASSWORD = 'SomePassword123#'
GO
CREATE CERTIFICATE IRSCertificate
WITH SUBJECT = 'IRS Cert',
START_DATE = '01/01/2017',
EXPIRY_DATE = '01/01/2053'
ACTIVE FOR BEGIN_DIALOG = ON;
GO
BACKUP CERTIFICATE IRSCertificate TO
FILE='C:\Certificates\IRSCertificate.cer';
GO
Create User TaxpayerUser WITHOUT LOGIN
GO
[/sourcecode]

Stop here and copy those certificates before proceeding. Ready? Ok, let’s finish it.
5. Create a user certificate from the user certificate backup file we copied from the other server, with authorization to the user created in Step 4
6. Grant connect to that user
7. Grant send permissions on the local service to the user
8. Create a remote service binding using that user

[sourcecode language="sql"]
--On our Taxpayer server
Use [SBDemoDB1]
GO
CREATE CERTIFICATE IRSCertificate
AUTHORIZATION IRSUser
FROM FILE = 'C:\Certificates\IRSCertificate.cer';
GO
GRANT CONNECT TO IRSUser;
GRANT SEND ON SERVICE::[//SBDemo/Taxes/TaxpayerService] To IRSUser;
GO
CREATE REMOTE SERVICE BINDING IRSServiceBinding
TO SERVICE '//SBDemo/Taxes/IRSService'
WITH USER = IRSUser
--On our IRS server
Use [SBDemoDB2]
GO
CREATE CERTIFICATE TaxpayerCertificate
AUTHORIZATION TaxpayerUser
FROM FILE = 'C:\Certificates\TaxpayerCertificate.cer';
GO
GRANT CONNECT TO TaxpayerUser;
GRANT SEND ON SERVICE::[//SBDemo/Taxes/IRSService] To TaxpayerUser;
GO
CREATE REMOTE SERVICE BINDING TaxpayerServiceBinding
TO SERVICE '//SBDemo/Taxes/TaxpayerService'
WITH USER = TaxpayerUser
[/sourcecode]

And that’s it. You are now ready to send Service Broker messages. Whew!

Conclusion

I hope you found this series helpful for getting started configuring Service Broker solutions. Granted, there is so much more to Service Broker than what I covered here, but this should at least get you started down the road to implementing your own application. To gain more knowledge and for help configuring your database – contact Fortified Data. Good luck!