Service Broker dialog security should be configured for event notifications that send messages to a service broker on a remote server. Dialog security must be manually configured according to the Service Broker dialog full-security model. The full-security model enables encryption and decryption of messages that are sent to and from remote servers. Although event notifications are sent in one direction, other messages, such as errors, are also returned in the opposite direction.
Configuring Dialog Security for Event Notifications
The process required to implement dialog security for event notification is described in the following steps. These steps include actions to take on both the source server and the target server. The source server is the server on which the event notification is being created. The target server is the server that receives the event notification message. You must complete the actions in each step for both the source server and the target server before you continue to the next step.
Важливо
All certificates must be created with valid start and expiration dates.
Step 1: Establish a TCP port number and target service name.
Establish the TCP port on which both the source server and the target server will receive messages. You must also determine the name of the target service.
Step 2: Configure encryption and certificate sharing for database-level authentication.
Complete the following actions on both the source and target servers.
Source server
Target server
Choose or create a database to hold the event notification and master key.
Choose or create a database to hold the master key.
If no master key exists for the source database, create a master key. A master key is required on both the source and target databases to help secure their respective certificates.
If no master key exists for the target database, create a master key.
Back up the certificate to a file that can be accessed by the source server.
Create a user, specifying the user of the target database, and WITHOUT LOGIN. This user will own the target database certificate to be created from the backup file. The user does not have to be mapped to a login, because the only purpose of this user is to own the target database certificate created in step 3 that follows.
Create a user, specifying the user of the source database, and WITHOUT LOGIN. This user will own the source database certificate to be created from the backup file. The user does not have to be mapped to a login, because the only purpose of this user is to own the source database certificate created in step 3 that follows.
Step 3: Share certificates and grant permissions for database-level authentication.
Complete the following actions on both the source and target servers.
Source server
Target server
Create a certificate from the backup file of the target certificate, specifying the target database user as the owner.
Create a certificate from the backup file of the source certificate, specifying the source database user as the owner.
Grant REFERENCES permission to the target database user on the existing event notifications Service Broker contract: https://schemas.microsoft.com/SQL/Notifications/PostEventNotification.
Create a remote service binding to the target service and specify the credentials of the target database user. The remote service binding guarantees that the public key in the certificate owned by the source database user will authenticate messages that are sent to the target server.
Grant CREATE QUEUE, CREATE SERVICE, and CREATE SCHEMA permissions to the target database user.
If not already connected to the database as the target database user, do so now.
Provide the service broker identifier of the source database to the target server. This identifier can be obtained by querying the service_broker_guid column of the sys.databases catalog view. For a server-level event notification, use the service broker identifier of msdb.
Provide the service broker identifier of the target database to the source server.
Step 4: Create routes and set up server-level authentication.
Complete the following actions on both the source and target servers.
Source server
Target server
Create a route to the target service, and specify the service broker identifier of the target database and the agreed-upon TCP port number.
Create a route to the source service, and specify the service broker identifier of the source database and the agreed-upon TCP port number. To specify the source service, use the following supplied service: https://schemas.microsoft.com/SQL/Notifications/EventNotificationService.
Switch to the master database to configure server-level authentication.
Switch to the master database to configure server-level authentication.
Back up the certificate to a file that can be accessed by the source server.
Create an endpoint, and specify the agreed-upon TCP port number, FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE certificate_name), and the name of the authenticating certificate.
Create an endpoint, and specify the agreed-upon TCP port number, FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE certificate_name), and the name of the authenticating certificate.
Create a login, and specify the login of the target server.
Create a login, and specify the login of the source server.
Administer an SQL Server database infrastructure for cloud, on-premises and hybrid relational databases using the Microsoft PaaS relational database offerings.