Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted May 25, 2011

Understanding SQL Server Service Broker Authentication

By Arshad Ali

SQL Server Service Broker (SSBS) is a new architecture (introduced with SQL Server 2005 and enhanced further in SQL Server 2008 and later versions) that allows you to write asynchronous, decoupled, distributed, persistent, reliable, scalable and secure queuing/message based applications within the database itself.

In my last couple of articles, I talked about different Service Broker objects and how to develop Service Broker applications, followed by an article on Service Broker Security in which I talked about setting up Transport Security and Dialog security across distributed Service Broker services. In this article we will be looking into different types of authentication modes used for communicating across different Service Broker Services.

When we talk of communicating messages back and forth between Initiator and Target, we need to ensure that communication is secure and that only authenticated and authorized users can access the services. So what are the different authentication modes available for distributed Service Broker communications?

Service Broker Authentication

Service Broker endpoint supports both Windows Authentication and Certificate Based Authentication. Windows Authentication is preferred if the distributed Service Broker endpoints are in the same windows domains, and Certificate Based Authentication is used if these endpoints are in two different windows domains.

Before I start digging into more details of these authentication modes, let me first differentiate two terms (Authentication and Encryption), as these are always confused. Authentication lets the service identify the identity of the user trying to connect to the service. When we talk of distributed Service Broker services, both of these services must agree that they can talk with each other on the specified authentication mode and it's mandatory to be defined across distributed Service Broker services. Encryption ensures that messages sent over the network are encrypted during transmission and is optional to configure.

Windows Authentication

Windows Authentication uses NTLM or Kerberos protocols (depending on how your network is configured) to authenticate Service Broker endpoints and is mainly used if both the Service Broker endpoints are in the same windows domain, though it can also be used across domains if you have set up trust relationship between these windows domains.

First you need to create one endpoint on both the servers (only one endpoint is created per SQL Server instance). By default, Service Broker endpoint listens on port 4022, though you can change it as needed. Next you need to create a login for target user (under which SQL Server service is running on the target) on the Initiator and grant CONNECT permission on the Initiator endpoint to the created login.

Initiator - Setting up Windows Authentication
--You can notice I have specified Windows authentication and hence
--SSBS will use Windows authentication connections to send messages 
CREATE ENDPOINT SSBSInitiatorEP
       STATE = STARTED
       AS TCP ( LISTENER_PORT = 4022 )
       FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS );
GO
SELECT * FROM sys.endpoints
GO
-- Create a Target login at Initiator under which 
-- Target SQL Server service is running
USE master
GO
CREATE LOGIN [DOMAIN\TargetLogin] FROM WINDOWS
GO
GRANT CONNECT ON ENDPOINT::SSBSInitiatorEP TO [DOMAIN\TargetLogin]
GO
-- Granting SEND permission on service required in case
-- anonymous dialogs or dialogs without configured dialog security
USE SSBSTargetDB
GRANT SEND ON SERVICE::[//SSBSLearning/ProductStockStatusCheck/InitiatorService] 
TO PUBLIC
GO

You need to repeat the similar process at the target also. Once the target endpoint is created, you need to create a login for Initiator user (under which the SQL Server service is running on the initiator) on the Target and grant CONNECT permission on Target endpoint to the created login.

Target - Setting up Windows Authentication
--You can notice I have specified Windows authentication and hence
--SSBS will uses Windows authentication connections to send messages 
CREATE ENDPOINT SSBSTargetEP
       STATE = STARTED
       AS TCP ( LISTENER_PORT = 4022 )
       FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS );
GO
SELECT * FROM sys.endpoints
GO
-- Create a Initiator login at Target under which 
-- Initiator SQL Server service is running
USE master
GO
CREATE LOGIN [DOMAIN\InitiatorLogin] FROM WINDOWS
GO
GRANT CONNECT ON ENDPOINT::SSBSTargetEP TO [DOMAIN\InitiatorLogin]
GO
-- Granting SEND permission on service required in case
-- anonymous dialogs or dialogs without configured dialog security
USE SSBSTargetDB
GRANT SEND ON SERVICE::[//SSBSLearning/ProductStockStatusCheck/TargetService] 
TO PUBLIC
GO

Once you are done with setting up authentication, the sending and receiving remains the same as I discussed in a previous article. Please note that creating login and granting CONNECT permission is required only if SQL Server services on both the SQL Server instances are running under different domain accounts; if they are running under same domain account then these steps are not required.

Certificate Based Authentication

As I noted, you can use Windows Authentication even if the SQL Server instances are from different windows domains, though setting up Windows Authentication mode in a such scenario might be quite difficult, complex and perform slowly. In those scenarios, you can choose Certificate Based Authentication. As opposed to NTLM or Kerberos protocols used in case of Windows Authentication mode, the Certificate Authentication mode uses TLS (Transport Layer Security) protocol for authenticating endpoints. As Certificate Based Authentication uses the certificates, it does not need to contact the domain controller and hence performs faster across windows domains.

When you use Certificate Based Authentication Mode, each endpoint will contain two certificates, one with its own private key and other ones with partners' endpoint public key. An endpoint uses the certificate with its own private key to encrypt the message before sending. On the other side, the endpoint receives the message and decrypts with the public key of the certificate of the sending endpoint. Please note a message encrypted with private key of the certificate can only be decrypted with the public key of the same certificate.

First of all you need to create the master key for you Initiator instance, then create a certificate with the CREATE CERTIFICATE command on the Initiator instance and make a backup of it. The backup of the certificate will contain the public key of the initiator certificate and will be restored on Target instance. Next create an Initiator endpoint that will use the Initiator certificate. Once you receive the backed up certificate from Target, restore it at Initiator instance (with same CREATE CERTIFICATE command, though this time using the FROM FILE clause). Finally create a target login and user at the initiator and provide CONNECT permission on the Initiator service to this target login.

Initiator - Setting up Certificate Based Authentication
USE master;
GO
--Creating master key and an user which will be used by certificate 
--for encryption and making remote connections
--Change the strong password for your master key as appropriate
--You can query sys.symmetric_keys to get information 
--about the database database master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'abcd!@#$5678';
GO
--Creating certificate that will be used for encrypting messages. 
CREATE CERTIFICATE SSBSInitiatorCert
       WITH SUBJECT = N'Certificate For Initiator',
       EXPIRY_DATE = N'12/31/2012';
GO
--The certificate needs to backed up and restored at the other communicating 
--partner so that the messages can be decrypted by the receiver of the message. 
--The location which you specify for backing up/restoring certificate must be 
--accessible by the account under which database engine is running.
BACKUP CERTIFICATE SSBSInitiatorCert
       TO FILE = N'\\MKTARALIW2K8R2\Shared\SQLCertificates\SSBSInitiatorCert.cer';
GO
--You notice I have specified Certificate based authentication and hence
--SSBS will uses Certificate based authentication connections to send messages 
CREATE ENDPOINT SSBSInitiatorEP
       STATE = STARTED
       AS TCP ( LISTENER_PORT = 4022 )
       FOR SERVICE_BROKER (CERTIFICATE = SSBSInitiatorCert);
GO
SELECT * FROM sys.endpoints
GO
--------============================================
--------============================================
--------============================================
CREATE LOGIN SSBSTargetLogin WITH PASSWORD = N'abcd!@#$5678';
CREATE USER SSBSTargetUser FOR LOGIN SSBSTargetLogin;
GO
--Resotring target certificates so that message received
--from target can be decrypted
CREATE CERTIFICATE SSBSTargetCert 
   AUTHORIZATION SSBSTargetUser
   FROM FILE = N'\\MKTARALIW2K8R2\Shared\SQLCertificates\SSBSTargetCert.cer'
GO
GRANT CONNECT ON ENDPOINT::[//SSBSLearning/ProductStockStatusCheck/InitiatorService] TO SSBSTargetLogin
GO

You need to repeat the process on the Target as well as what you did on the Initiator. First of all you need to create master key for you Target instance, next create certificate with the CREATE CERTIFICATE command on the Target instance and make a backup of it. The backup of the certificate will contain the public key of the target certificate and will be restored on Initiator instance. Next create a Target endpoint which will use Target certificate. Once you receive the backed up certificate from Initiator, restore it at Target instance (with same CREATE CERTIFICATE command though this time using the FROM FILE clause). Finally create an initiator login and user at the Target and provide CONNECT permission on the Target service to this initiator login.

Target - Setting up Certificate Based Authentication
USE master;
GO
--Creating master key and an user that will be used by certificate 
--for encryption and making remote connections
--Change the strong password for your master key as appropriate
--You can query sys.symmetric_keys to get information 
--abou the database database master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'abcd!@#$5678';
GO
--Creating certificate that will be used for encrypting messages. 
CREATE CERTIFICATE SSBSTargetCert 
       WITH SUBJECT = 'Certificate For Target',
       EXPIRY_DATE = N'12/31/2012';
GO
--The certificate needs to backed up and restored at the other communicating 
--partner so that the messages can be decrypted by the receiver of the message. 
--The location that you specify for backing up/restoring the certificate must be 
--accessible by the account under which database engine is running.
BACKUP CERTIFICATE SSBSTargetCert
       TO FILE = N'\\MKTARALIW2K8R2\Shared\SQLCertificates\SSBSTargetCert.cer';
GO
--You can notice I have specified Certificate based authentication and hence
--SSBS will uses Certificate based authentication connections to send messages 
CREATE ENDPOINT SSBSTargetEP
       STATE = STARTED
       AS TCP ( LISTENER_PORT = 4022 )
       FOR SERVICE_BROKER (CERTIFICATE = SSBSTargetCert);
GO
SELECT * FROM sys.endpoints
GO
--------============================================
--------============================================
--------============================================
CREATE LOGIN SSBSInitiatorLogin WITH PASSWORD = N'abcd!@#$5678';
CREATE USER SSBSInitiatorUser FOR LOGIN SSBSInitiatorLogin;
GO
--Restoring initiator certificates so that message received
--from initiator can be decrypted
CREATE CERTIFICATE SSBSInitiatorCert
   AUTHORIZATION SSBSInitiatorUser
   FROM FILE = N'\\MKTARALIW2K8R2\Shared\SQLCertificates\SSBSInitiatorCert.cer';
GO
GRANT CONNECT ON ENDPOINT::[//SSBSLearning/ProductStockStatusCheck/TargetService] TO SSBSInitiatorLogin
GO

Once you are done with the setting up authentication, creating ROUTE and giving SEND permission, sending/receiving messages remains the same as I discussed in a previous article.

Notes

  • If both your Service Broker endpoints are in the same windows domain, it's recommended to use Windows Authentication mode, and if both the endpoints are in different windows domains use Certificate Based Authentication.
  • If both the endpoints are in the same SQL Server instance, no encryption is done (whether you have specified or not specified), as messages don’t go outside SQL Server memory.
  • Please note that in the above example, I have created a certificate in SQL Server for simplicity, but you can use certificates from other sources (certificate authority or your organization) also.

Conclusion

In this article, I discussed Service Broker authentication modes in detail. I showed you what Windows authentication is, how to configure it and where it makes sense to use it. I also talked about Certificate Based Authentication, where to use it and how to configure it.

Resources

See all articles by Arshad Ali



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM