Export (0) Print
Expand All

How to: Configure Target Services for Anonymous Dialog Security (Transact-SQL)

SQL Server uses dialog security for any conversation to a service for which a remote service binding exists in the database that hosts the initiating service. If the remote service binding specifies ANONYMOUS = ON, the dialog uses anonymous security. In this case, there is no need for the target database to contain a user for the initiating service. The initiating service acts as public in the target database.

  1. Create a user without a login.

  2. Create a certificate for the user.

    ms166108.note(en-US,SQL.90).gifNote:
    The certificate must be encrypted with the master key. For more information, see CREATE MASTER KEY (Transact-SQL).

  3. Back up the certificate to a file.

    ms166108.security(en-US,SQL.90).gifSecurity Note:
    Only back up the certificate for this user. Do not back up or distribute the private key associated with the certificate.

  4. Grant permission for the target service user to receive messages from the queue that the target service uses.

  5. Grant permission for PUBLIC to send messages to the target service.

  6. Provide the certificate and the name of the target service to the database administrator for the remote database.

USE AdventureWorks ;
GO

--------------------------------------------------------------------
-- This script configures security for a local user in the database.
-- The script creates a user in this database, creates a certificate
-- for the user, writes the certificate to the file system, and
-- grants permissions to the user. Since this service is a target
-- service, no remote service binding is necessary.

-- Create a user without a login. For convenience,
-- the name of the user is based on the name of the
-- the remote service.

CREATE USER [SupplierOrdersUser]
    WITHOUT LOGIN;
GO

-- Create a certificate for the initiating service
-- to use to send messages to the target service.

CREATE CERTIFICATE [SupplierOrdersCertificate]
    AUTHORIZATION [SupplierOrdersUser]
    WITH SUBJECT = 'Certificate for the SupplierOrders service user.';
GO

-- Backup the certificate. Provide the certificate file
-- to the administrator for the database that hosts
-- the other service.

BACKUP CERTIFICATE [SupplierOrdersCertificate]
   TO FILE = 'C:\Certificates\SupplierOrders.cer';
GO

-- Grant receive on the orders queue to the local user.

GRANT RECEIVE ON SupplierOrdersQueue
    TO [SupplierOrdersUser];
GO

-- Grant send on the service to public.

GRANT SEND ON SERVICE::[SupplierOrders] TO public ;

Community Additions

ADD
Show:
© 2014 Microsoft