How to: Allow Service Broker Network Access by Using Certificates (Transact-SQL)

Switch View

How to: Allow Service Broker Networ...

Community Content

In this section

Statistics Annotations (0)

Click here to expand

Collapse All

Language Filter

Other versions are also available for the following:

SQL Server 2005

SQL Server 2008 Books Online (October 2009)

How to: Allow Service Broker Network Access by Using Certificates (Transact-SQL)

To allow another instance to send messages using certificate-based Service Broker transport security, you create a user for the other instance and install the certificate for the other instance.

To permit access from another instance using certificates

Obtain the certificate for the other instance from a trusted source. Typically, this involves sending the certificate using encrypted e-mail or transferring the certificate on physical media such as a floppy disk.

Security Note:
Only install certificates from trusted sources.
Create a login.
Create a user for the login in the master database.
Install the certificate for the other instance in the master database. The user created in step 3 owns the certificate.
Grant the login CONNECT access to the Service Broker endpoint.
Dump the certificate that is used for Service Broker transport security in the local instance.

Security Note:
Only dump the certificate used for transport security. Do not dump or distribute the private key associated with the certificate.
Provide the certificate to the administrator of the other database. The administrator of the remote database installs this certificate using steps 1-4 above.

Security Note:
Only install certificates from trusted sources.

Security Note:
Only dump the certificate used for transport security. Do not dump or distribute the private key associated with the certificate.

Once access is configured in each instance, then communications between the two instances use Service Broker transport security when the endpoints for both instances are configured to allow transport security.

Example

USE master ;
GO

-- Create a login for the remote instance.

CREATE LOGIN RemoteInstanceLogin
    WITH PASSWORD = '#gh!3A%!1@f' ;
GO

-- Create a user for the login in the master database.

CREATE USER RemoteInstanceUser
    FOR LOGIN RemoteInstanceLogin ;
GO

-- Load the certificate from the file system. Notice that
-- the login owns the certificate.

CREATE CERTIFICATE RemoteInstanceCertificate
    AUTHORIZATION RemoteInstanceUser
    FROM FILE='C:\Certificates\AceBikeComponentsCertificate.cer' ;
GO
GRANT CONNECT ON ENDPOINT::ThisInstanceEndpoint to RemoteInstanceLogin ;
GO
-- Write the certificate from this instance
-- to the file system. This command assumes
-- that the certificate used by the Service Broker
-- endpoint is named TransportSecurity.

BACKUP CERTIFICATE TransportSecurity
    TO FILE = 'C:\Certificates\ThisInstanceCertificate.cer' ;
GO

Tasks

Other Resources

Help and Information