Security Considerations for the Service Broker

Configuring security is probably the most complex piece of the Service Broker application. Nevertheless, the Service Broker is designed to run enterprise applications in very secure environments. In fact, by default, the Service Broker can only send messages within the database. Recall from the first example that in order to send the message to a different database on the same SQL Server instance, you had to set the TRUSTWORTHY bit to 1 for both the databases.

The Service Broker provides two distinct levels of security:

• Transport security: Secures the TCP/IP connection between two SQL Server instances on different servers

• Dialog Security: Secures each individual dialog between the two dialog endpoints. This ensures that the services exchanging the messages are who they say they are.

In cases where the highest level of security is required, using both transport and dialog security is appropriate. In addition, the Service Broker uses regular SQL Server security to assign permissions to users to enable them to create and use the various Service Broker objects such as message types, contracts, and services.

Transport Security

Transport security secures the TCP/IP connection. There are two parts to transport security: authentication (whereby the two instances of SQL server determine that they are willing to talk) and encryption of the data over the network. Note that authentication is not optional, but encryption is. In order for two SQL Server instances to exchange Service Broker messages, you must create an endpoint on each server using the CREATE ENDPOINT or ALTER ENDPOINT statements.

The Service Broker offers two types of authentication: Windows and Certificate. Windows authentication uses the normal Windows authentication protocols - NTLM or Kerberos - to establish a connection between two endpoints. For simplicity, consider an endpoint as an instance of SQL Server. Certificatebased authentication uses the TLS (Transport Layer Security) authentication protocol (SChannel) to authenticate the two endpoints. To establish either of these authentications, you have to use the CREATE ENDPOINT statement on each instance of the SQL Server. More details about this are discussed in the following sections.

Windows Authentication

Suppose you want two instances of SQL Server to exchange Service Broker messages. The first thing you have to do is create an endpoint on each SQL Server instance. At this point, you can start the second example and learn how to create endpoints. Open the folder Sample2 under the Chapter 8 folder and double-click the Sample2 solution file. You need two instances of SQL Server connected on the same domain.

Open the CreateEndPointOnInitiator.sql script and connect it to the initiator SQL Server:

 CREATE ENDPOINT InitiatorSSBEndPoint STATE = STARTED AS TCP(LISTENER_PORT = 5040) FOR SERVICE_BROKER(AUTHENTICATION = WINDOWS, ENCRYPTION = REQUIRED) 

You can create only one endpoint for the Service Broker for each instance of SQL Server. This endpoint listens on a specific TCP port. In this example it is port 5040. The default port for the Service Broker is 4022. You can specify any available port number above 1024. Use the netstat -a command in a command window to determine which ports are available on your system.

This script specified WINDOWS for AUTHENTICATION, so the Service Broker will either use NTLM or Kerberos depending on how your network is configured and how the endpoint is configured (Kerberos, NTLM, or both) in the endpoint configuration. Suppose you have two servers, InitiatorServer and TargerServer. If your SQL Server service account is "local system," in order to use Windows authentication, you have to make sure that your network has Kerberos authentication. If not, you will have to change the SQL Server service account to a domain user such as MYDOM\ketanp. If you have permission to run the SQL Server service under the "local system" account and if your network does not have Kerberos, then you must use certificate-based authentication, which is covered in the section "Certificate-Based Authentication." For this exercise, we assume that you are running the SQL Server service using a domain account.

Run the CreateEndPointOnInitiator.sql script. After you run the script, run the following SQL statement to view the TCP endpoint you just created:

 SELECT * FROM sys.tcp_endpoints 

Open the CreateEndPointOnTarget.sql script, connect it to the target machine, and run it. Now you have established endpoints on both ends.

The next step is to grant CONNECT permission to these endpoints so that remote machines can connect to them. To do that, first you have to add the login under which the SQL Server service runs on InitiatorServer to TargerServer, and vice versa.

Open the AddRemoteLoginOnTarget.sql script and connect to the TargetServer machine:

 CREATE LOGIN [MYDOM\InitiatorLogin] FROM WINDOWS GRANT CONNECT ON ENDPOINT::TargetSSBEndPoint TO [MYDOM\InitiatorLogin] 

You are adding the account MYDOM\InitiatorLogin (which runs the SQL Server service on InitiatorServer) to TargetServer, and you are granting CONNECT permission on the endpoint you created on the TargetServer to this account. Please note that if you are using the "local system" service account to run the SQL Server, then the login name would be something like [MYDOM\InitiatorServer$].

You need to perform a similar operation on the InitiatorServer machine to introduce the MYDOM\TargetLogin account and grant CONNECT permission. Open the AddRemoteLoginOnInitiator.sql script, connect to the InitiatorServer machine, and run it:

 CREATE LOGIN [MYDOM\TargetLogin] FROM WINDOWS GRANT CONNECT ON ENDPOINT::InitiatorSSBEndPoint TO [MYDOM\TargetLogin] 

Again, if you are using the "local system" service account to run SQL Server, the login name would be something like [MYDOM\TargetServer$].

Note

If you are running the SQL Server service under the same the domain account on both machines, you do not need to run the AddRemoteLoginOnTarget.sql and AddRemoteLoginOnInitiator.sql scripts.

You're done! You have set up the transport security using Windows authentication. You are not yet ready to send the message, however. First you need to understand certificate-based authentication.

Certificate-Based Authentication

In this section, we assume that you know what public and private keys are and how they work. If the endpoints are in different domains, Windows authentication can be complex and slow. In this case, you may choose to use certificate-based authentication. Each endpoint needs two certificates, one with its own private key and another with the opposite endpoint's public key. When one endpoint encrypts the data with the private key, it can only be decrypted with the corresponding public key at the opposite endpoint. The advantage of certificate-based authentication is that authentication only requires certificates. There is no need for the endpoints to contact a domain controller, as there is with Windows authentication, so the endpoints can be in different domains, which is one of the most compelling reasons to use certificates. For more information about how certificates work, please refer to Books Online.

Now you'll use sample2 to create and install certificates on InitiatorServer and TargerServer. Open the script CreateCertOnInitiator.sql and connect to InitiatorServer:

 USE MASTER GO IF NOT EXISTS(SELECT 1 FROM sys.symmetric_keys where name = '##MS_DatabaseMasterKey##') CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23%&weq^yzYu2005!' GO IF NOT EXISTS (select 1 from sys.databases where [is_master_key_encrypted_by_server] = 1) ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY GO IF NOT EXISTS (SELECT 1 FROM sys.certificates WHERE name = 'InitiatorDBCert') CREATE CERTIFICATE InitiatorDBCert WITH SUBJECT = 'Initiator Server Certificate' GO BACKUP CERTIFICATE InitiatorDBCert TO FILE = 'C:\InitiatorDBCert.cer' 

For simplicity, here we are using the certificate created by SQL Server, but other ways of creating and distributing certificates will work equally well.

You have created a master key in this script because when you create a certificate, you have to specify the encryption by password. If you don't specify it, the private key will be encrypted using the database master key. This script creates a certificate in the master database. The BACKUP CERTIFICATE statement backs up the public key certificate for this private key.

Now open the script CreateEndPointWithCertOnInitiator.sql and connect to InitiatorServer:

 CREATE ENDPOINT InitiatorSSBEndPoint STATE = STARTED AS TCP(LISTENER_PORT = 5040) FOR SERVICE_BROKER(AUTHENTICATION = CERTIFICATE InitiatorDBCert, ENCRYPTION = REQUIRED) 

This script creates an endpoint with CERTIFICATE authentication using the certificate you just created on InitiatorServer.

Let's do the same on TargetServer. Open CreateCertOnTarget.sql on TargetServer and run it. Then open CreateEndPointWithCertOnTarget.sql on TargetServer and run it.

So far you have created certificates on both InitiatorServer and TargetServer and have associated the endpoint on each server with certificates. Now you have to introduce the endpoints to each other by exchanging the public keys. Copy the certificate you backed up using CreateCertOnInitiator.sql to TargetServer on C:\. Also copy the certificate backed up using CreateCertOnTarget.sql to InitiatorServer on C:\.

Once the certificates have been exchanged, all you have to do is associate them with a login that has CONNECT permissions for the endpoint. Open the CreateLoginOnInitiator.sql script and connect to InitiatorServer:

 CREATE LOGIN TargetServerUser WITH PASSWORD = '32sdgsgy^%$!' CREATE USER TargetServerUser; CREATE CERTIFICATE TargetDBCertPub AUTHORIZATION TargetServerUser FROM FILE = 'C:\TargetDBCert.cer' GRANT CONNECT ON ENDPOINT::InitiatorSSBEndPoint TO TargetServerUser 

This script creates a SQL Server login and creates a user in the master database. You are also importing the certificate you just copied from TargetServer and authorizing the user you created. At the end, you are granting CONNECT permission to the endpoint InitiatorSSBEndPoint to the user TargetServerUser. You still have to do the same on the TargetServer, so open the script CreateLoginOnTarget.sql, connect to TargetServer, and run it.

You have now configured endpoints on both the InitiatorServer and TargetServer to use certificate-based authentication.

Our general recommendation is that if both endpoints are in the same Windows domain, then you should use Windows authentication; if both endpoints are in different domains, then you should use certificate authentication instead. These are recommendations, though, and not rules.

Encryption

When you configured an endpoint on each SQL Server instance, you specified ENCRYPTION = REQUIRED in the CREATE ENDPOINT statement:

 CREATE ENDPOINT InitiatorSSBEndPoint STATE = STARTED AS TCP(LISTENER_PORT = 5040) FOR SERVICE_BROKER(AUTHENTICATION = WINDOWS, ENCRYPTION = REQUIRED) 

By default, encryption is required for all transport connections. Along with authentication, all transport messages are checksummed and signed to ensure that the messages are not altered on the wire. Message encryption is required by default, so the endpoints you configured earlier will send the messages encrypted. What if both endpoints are not configured with the same option in the ENCRYPTION clause? The clause has three options: REQUIRED, SUPPORTED, and DIABLED. The following table indicates whether data will be encrypted or not when endpoints are configured with different ENCRYPTION options.

Open table as spreadsheet

EndpointA	EndpointB	Data Encrypted?
REQUIRED	REQUIRED	Yes
REQUIRED	SUPPORTED	Yes
DISABLED	DISABLED	No
DISABLED	SUPPORTED	No
SUPPORTED	SUPPORTED	Yes

Note

When both endpoints of the dialog are in the same SQL Server instance - either in the same database or different databases (as in Sample1) in the same instance - encryption is never done. The Service Broker message never leaves the server's memory, so there is no reason to encrypt them.

Routing

You are almost ready to send the message from InitiatorServer to TargetServer. Before we talk about dialog security, we need to introduce routing. This is a required component to send the messages between two instances of SQL Server. Routing isn't really related to the security configuration, but you need to know about it before you can send the message.

When messages are sent, the Service Broker needs to know where to send them. A Service Broker route maps a service to a destination where messages can be sent. Four routes are involved in delivering a message to its destination:

1. When the SEND command is executed, a route from the local sys.routes table is used to determine where the message is sent.

2. When the message arrives at the destination SQL Server instance, a route from the sys.routes table in the msdb database is used to determine which database will receive the message. Note that whenever a message comes from a remote SQL Server instance, it always goes to the msdb database first. Make sure that the is_broker_enabled bit is set to 1 in msdb, or your messages will not be delivered. Once the message has been successfully committed in the proper queue in the destination database, an acknowledgment is sent back to the sender.

3. A route from the sys.routes table is used to determine where to send the acknowledgment message. When the message is sent from the sender, it always goes to the sys.transmission_queue table on the sender, and will stay there until the receiver sends the acknowledgment that the message has been received. It is important that you specify the return route.

4. When the acknowledgment message arrives at the sender's SQL Server instance, a route in the msdb database is used to route the message to the sending service's queue in the sending database.

You need to create a route on InitiatorServer and a return route on TargetServer in order to send the messages between these two instances. Let's do that now.

Open SetupInitiator.sql on InitiatorServer and run it, and then open SetupTarget.sql on TargetServer and run it. Both of these scripts are taken from sample1. Now you can create the route and bind that route to a service. Open the CreateRouteOnInitiator.sql script and connect to InitiatorServer:

 USE InitiatorDB GO IF EXISTS(SELECT * FROM sys.routes WHERE name = 'RouteToTargetServer') DROP ROUTE RouteToTargetServer GO CREATE ROUTE RouteToTargetServer WITH  SERVICE_NAME = 'TargetService' ,ADDRESS = 'TCP://TargetServer:5040' 

Notice that the route is created in the database from which we are sending the message. In addition, the SERVICE_NAME specified is the TargetService, which is on the TargetServer in the TargetDB database. The ADDRESS argument specifies the name of the server, which is TargetServer, and the port on which it is listening. Remember that when you created the endpoint on TargetServer, you specified the port number. That's the port number you use here.

Now run the script CreateRouteOnTarget.sql.

Remember the BEGIN DIALOG statement you used to send the message? Here's part of it:

       BEGIN DIALOG CONVERSATION @Conversation_Handle       FROM SERVICE [InitiatorService]       TO SERVICE 'TargetService'       ON CONTRACT [//www.wrox.com/CTGeneralInfo/ProductCustomer]       WITH ENCRYPTION = OFF 

This tells the Service Broker that the message is being sent to TargetService, but where is TargetService? Remember that Figure 8-2 shows the four points needed to deliver messages successfully. In this case, when the message is sent, the Service Broker on InitiatorServer checks for routes in sys.routes. Because you have set SERVICE_NAME = 'TargetService' in the CREATE ROUTE statement, this says that this service resides on TargetServer, so the message will be sent to TargetServer. Then, on TargetServer, the Service Broker will check in the msdb database route table which database the TargetService exists in, and send the message to the RequestQueue, which is the endpoint for TargetService.

You have to create the route on TargetServer to complete the communication loop. Open the script CreateRouteOnTarget.sql and connect to TargetServer:

 USE TargetDB GO IF EXISTS(SELECT * FROM sys.routes WHERE name = 'RouteToInitiatorServer') DROP ROUTE RouteToInitiatorServer GO CREATE ROUTE RouteToInitiatorServer WITH  SERVICE_NAME = 'InitiatorService' ,ADDRESS = 'TCP://InitiatorServer:5040' 

Here you are defining the route to InitiatorServer, which resides on InitiatorServer.

Now you are ready to send the message. Open the SendMessageFromInitiator.sql script, connect to InitiatorServer, and run it. You can check the status on TargetServer using the ViewInfo OnTarget.sql script, and on InitiatorServer using ViewInfoOnInitiator.sql.

Two addresses have special meaning for Service Broker routes. When the route for a service name has an address of LOCAL, the message classifier will look for the service name in the local instance to find which queue to put the message in. The first priority is the current database where the route exists. If the service does not exist there, the classifier checks the service list, which contains all the services that exist in the local SQL Server instance. The following SQL statement creates a local route:

 CREATE ROUTE MyLocalRoute WITH SERVICE_NAME = 'MyService'     ,ADDRESS = 'LOCAL' 

If you have services with the same name (in this case MyService) in different databases on the local SQL Server instance, the classifier will pick one of the services as the target service and send the messages to that service. To avoid this, use the BROKER_INSTANCE parameter in the CREATE ROUTE statement to ensure that messages are delivered to the specific database (broker).

When you create a new database, you will find a route called AutoCreatedLocal in sys.routes. This route has no service name (remote_service_name column in sys.routes view) or broker identifier (broker_instance column in sys.routes view), and an address of LOCAL (address column in sys.routes view). Remember that in the first example in this chapter, you were sending messages from InitiatorDB to TargetDB without creating a route. AutoCreatedLocal is a wildcard route, so the message classifier will use this route for any service that does not have another route available. This route sends messages to local services, so in the first example in this chapter you were able to send the message to TargetService using AutoCreatedLocal. In a distributed application, it is better to drop this AutoCreatedLocal route, because without a wildcard route, you can be sure that messages only go where you intend them to go.

The other special address is called TRANSPORT. When you send the message to the target, you need the response from the target back to the initiator, so a route must exist on the target to specify the address of the initiator in order to send the message back. Consider a scenario in which you want to send messages from 100 initiators to one target. You will need 100 routes on the target to send the acknowledgment or messages back to the initiator. Instead, you can use a shortcut with the TRANSPORT address, so you just need to create one route on the target instead of 100. When messages are sent back from the target to the initiator, the classifier uses the service name from the dialog as an address.

Here's a small example. Suppose you want to send the message from InitiatorServer, which has an endpoint defined on port 4040, to TargetServer. On InitiatorServer, create this service:

 CREATE QUEUE [InitiatorQueue] CREATE SERVICE [TCP://InitiatorServer:4040/ResponseService] ON QUEUE InitiatorQueue 

When you send the message to TargetServer, you issue the BEGIN DIALOG statement as follows:

 BEGIN DIALOG @Conversation_handle FROM SERVICE [TCP://InitiatorServer:4040/ResponseService] TO SERVICE 'TargetService' ON CONTRACT 'AppContract' 

You have already created the route on InitiatorServer. Now on TargerServer, create a wildcard route as follows:

 CREATE ROUTE RouteToInitiatorServer WITH ADDRESS = 'TRANSPORT' 

Now when the message needs to be sent back to InitiatorServer from TargetServer, because no specific route is created for the service [TCP://InitiatorServer:4040/ResponseService] on TargetServer, the classifier will look for a wildcard route with the TRANSPORT address and attempt to open a connection to TCP://InitiatorServer:4040. In this example, it will succeed and the message will be returned successfully to InitiatorServer.

The biggest disadvantage of using TRANSPORT routes is that the address is hardcoded in the service name, so to move an application to another server you have to destroy all the existing dialogs to the service. You also have to re-create the service with new server name in the address, and you must change your BEGIN DIALOG statement if you are not using dymanic SQL in BEGIN DIALOG. We don't recommend using this method of routing unless this disadvantage is acceptable in your environment.

Dialog Security

Dialog security is used to secure the dialogs from the initiator of the dialog to the target of the dialog. You have just learned that the data from one endpoint to another endpoint will be encrypted by default, so why do you need dialog security? The difference is that dialog security works between dialog endpoints instead of transport endpoints (see Figure 8-9).

Figure 8-9

What is the Forwarder box in the diagram? The Service Broker uses a concept of message forwarding, so a SQL Server instance can be configured to forward the messages using the following statement:

 CREATE ENDPOINT ForwarderSSBEndPoint STATE = STARTED AS TCP(LISTENER_PORT = 5040) FOR SERVICE_BROKER (    AUTHENTICATION = WINDOWS   ,MESSAGE_FORWARDING = ENABLED   ,MESSAGE_FORWARD_SIZE = 40 ) 

This enables message forwarding and sets the maximum amount of memory to use for forwarding, in this case 40MB. The messages on the forwarding server always stay in memory and aren't persisted in the database. In some scenarios, message forwarding is required. One of those scenarios is the case when two endpoints cannot connect directly, so some kind of gateway is needed to connect the two endpoints. Please refer to Books Online for more details about message forwarding.

Returning to Figure 8-9, when a message is initiated, it travels through two forwarders before it reaches the final destination. If you have used transport security, you know that the messages are decrypted and encrypted at each forwarder, so the message would be encrypted and decrypted three times en route. If you had used dialog security instead, the messages going from the initiator to the target would be encrypted at the initiator and decrypted at the target, rather than at each forwarder. The extra overhead in the first case can cause significant delay and increase processing time. Dialog security has the advantage of encrypting and decrypting a message once, no matter how many Service Brokers the message is forwarded through, so if you have a message forwarder in your network, then you may want to use dialog security. If your network is secure enough that encryption on the wire is not required, or if transport security is sufficient, you may want to turn off dialog security. In the previous examples, we set ENCRYPTION = OFF in the BEGIN DIALOG statement. That's how you turn off dialog security.

Configuring dialog security is similar to configuring certificate-based authentication. Open the script ConfigureDialogSecurityOnTarget.sql and connect to TargetServer:

 USE TargetDB GO IF NOT EXISTS(SELECT 1 FROM sys.symmetric_keys where name = '##MS_DatabaseMasterKey##') CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23%&weq^yzYu2005!' GO IF NOT EXISTS (select 1 from sys.databases where [is_master_key_encrypted_by_server] = 1) ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY GO --Create a USER TO own the TargetService. IF NOT EXISTS(SELECT * FROM sys.sysusers WHERE name = 'TargetServiceOwner') CREATE USER TargetServiceOwner WITHOUT LOGIN GO GRANT CONTROL ON SERVICE::TargetService TO TargetServiceOwner GO IF NOT EXISTS (SELECT 1 FROM sys.certificates WHERE name = 'TargetDBDialogCert') CREATE CERTIFICATE TargetDBDialogCert AUTHORIZATION TargetServiceOwner WITH SUBJECT = 'Target Server Certificate for Dialog Security' GO BACKUP CERTIFICATE TargetDBDialogCert TO FILE = 'C:\TargetDBDialogCert.cer' 

This script creates a user and grants CONTROL permission on TargetService to that user. The script also creates a certificate and makes the TargetServiceOwner user the owner of that certificate. It also backs up the certificate to C:\. Note that you are creating a certificate in the database in which the service resides, and not in the master database.

Now open the script ConfigureDialogSecurityOnInitiator.sql and connect to InitiatorServer:

 USE InitiatorDB GO IF NOT EXISTS(SELECT 1 FROM sys.symmetric_keys where name = '##MS_DatabaseMasterKey##') CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23%&weq^yzYu2005!' GO IF NOT EXISTS (select 1 from sys.databases where [is_master_key_encrypted_by_server] = 1) ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY GO --Create a USER TO own the InitiatorService. IF NOT EXISTS(SELECT * FROM sys.sysusers WHERE name = 'InitiatorServiceOwner') CREATE USER InitiatorServiceOwner WITHOUT LOGIN GO --Make this user the owner of the InitiatorService. ALTER AUTHORIZATION ON SERVICE::InitiatorService TO InitiatorServiceOwner GO IF NOT EXISTS (SELECT 1 FROM sys.certificates WHERE name = 'InitiatorDBDialogCert') CREATE CERTIFICATE InitiatorDBDialogCert AUTHORIZATION  InitiatorServiceOwner WITH SUBJECT = 'Initiator Server Certificate for Dialog Security' GO BACKUP CERTIFICATE InitiatorDBDialogCert TO FILE = 'C:\InitiatorDBDialogCert.cer' 

This script creates a user and makes that user the owner of the InitiatorService. The script also creates a certificate and makes the InitiatorServiceOwner user the owner of that certificate. It also backs up the certificate to C:\, just as before.

Now copy the certificate TargetDBDialogCert.cer from C:\ on TargetServer to C:\ on InitiatorServer. Similarly, copy the certificate InitiatorDBDialogCert.cer from C:\ on InitiatorServer to C:\ on TargerServer.

Now open the script DialogImportCertOnInitiator.sql and connect to InitiatorServer:

 USE InitiatorDB GO --Create a USER TO own the TargetService. IF NOT EXISTS(SELECT * FROM sys.sysusers WHERE name = 'TargetServiceOwner') CREATE USER TargetServiceOwner WITHOUT LOGIN --Import the certificate owned by above user IF NOT EXISTS(SELECT * FROM sys.certificates WHERE name = 'TargetDBDialogCertPub') CREATE CERTIFICATE TargetDBDialogCertPub AUTHORIZATION TargetServiceOwner FROM FILE = 'C:\TargetDBDialogCert.cer' 

This script creates the user TargetServiceOwner, the same user you created on TargetServer. The script also imports the certificate that you copied from TargetServer and made TargetServiceOwner the owner of that certificate.

Next, open the script DialogImportCertOnTarget.sql on TargetServer:

 USE TargetDB GO IF NOT EXISTS(SELECT * FROM sys.sysusers WHERE name = 'InitiatorServiceOwner') CREATE USER InitiatorServiceOwner WITHOUT LOGIN --Import the certificate owned by above user IF NOT EXISTS(SELECT * FROM sys.certificates WHERE name = 'InitiatorDBDialogCertPub') CREATE CERTIFICATE InitiatorDBDialogCertPub AUTHORIZATION InitiatorServiceOwner FROM FILE = 'C:\InitiatorDBDialogCert.cer' GRANT SEND ON SERVICE::TargetService TO InitiatorServiceOwner 

This script does the same thing on TargerServer that you just did on InitiatorServer.

The last thing you have to do is create a REMOTE SERVICE BINDING to bind the user TargetServiceOwner you created on InitiatorServer to the TO SERVICE used in the BEGIN DIALOG command. The TO SERVICE in this case is TargetService. Open the CreateRemoteBindingOnInitiator.sql script and connect to InitiatorServer:

 USE InitiatorDB GO IF NOT EXISTS(SELECT * FROM sys.remote_service_bindings WHERE name = 'ToTargetService') CREATE REMOTE SERVICE BINDING ToTargetService TO SERVICE  'TargetService' WITH USER = TargetServiceOwner 

Now the BEGIN DIALOG command knows which two certificates to use to authenticate the dialog: the private key certificate associated with the owner of the FROM SERVICE (user InitiatorServiceOwner) and the certificate owned by the user bound to the TO SERVICE (user TargetServiceOwner) with the REMOTE SERVICE BINDING.

You are now ready to send the first encrypted message to the TargetServer. Open the SendMessage WithEncryption.sql script. This script is similar to SendMessageFromInitiator.sql with a small difference in the BEGIN DIALOG statement, as you can see:

       BEGIN DIALOG CONVERSATION @Conversation_Handle       FROM SERVICE [InitiatorService]       TO SERVICE 'TargetService'       ON CONTRACT [//www.wrox.com/CTGeneralInfo/ProductCustomer]       WITH ENCRYPTION = ON 

You have turned on encryption, but how do you know the messages are encrypted? Run the script SendMessageWithEncryption.sql. Then open ViewInfoOnTarget.sql, connect to TargetServer, and run it.

Check the output of SELECT * FROM sys.conversation_endpoints. You will notice that the outbound_session_key_identifier and inbound_session_key_identifier columns in the sys.conversation_endpoints view have values other than 0, which shows that the messages were encrypted.

Note than when you specify ENCRYPTION = OFF, it doesn't mean that encryption is disabled for a dialog. ENCRYPTION = OFF means that encryption is not required. If the TO SERVICE has the REMOTE SERVICE BINDING, the dialog will be secured even when ENCRYPTION = OFF is used. Change the ENCRYPTION = OFF statement in the script SendMessageWithEncryption.sql and run it. You will see that the messages are still encrypted.

If ENCRYPTION = ON is used and REMOTE SERVICE BINDING is not present, or the dialog security is not configured properly, the BEGIN DIALOG statement will fail.