This post is part of a series on this blog that will explore SQL Server Service Broker, a native messaging and queueing technology built into the SQL Server Database Engine.
- SQL Server Service Broker – Introduction
- SQL Server Service Broker – Service Architecture
- SQL Server Service Broker – Conversation Architecture
- SQL Server Service Broker – Sending and Receiving
- SQL Server Service Broker – Error Handling
- SQL Server Service Broker – Internal Activation
- SQL Server Service Broker – Networking
I kind of left you hanging, didn’t I? I brought you right to the edge of having a working Service Broker implementation and then… nada. Yeah. Sorry ’bout that. But my OCD side has brought me back to finish what we started here. So, without further ado, let’s talk about 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.
--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
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.
--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
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:
--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
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
--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
And that’s it. You are now ready to send Service Broker messages. Whew!
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. Good luck!