How to: Configure Initiating 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. If the database that hosts the target service does not contain a user that corresponds to the user that created the dialog, the dialog uses anonymous security.

Security Note:
Only install certificates from trusted sources.

To make sure that an initiating service uses dialog security

1. Obtain a certificate for a user in the remote database from a trusted source.

2. Create a user without a login.

3. Install the certificate for the remote service. The user created in step 3 owns the certificate. By default the certificate is active for BEGIN DIALOG.

4. Create a remote service binding that specifies the user and the target service. For anonymous dialog security, the remote service binding specifies ANONYMOUS = ON.

Example

This example configures anonymous dialog security for conversations between the service named OrderParts in the current instance and the service named SupplierOrders in the remote instance.

USE AdventureWorks ;
GO

-- Given a certificate for a remote user for the remote service
-- SupplierOrders, create a remote service binding for
-- the service.  The remote user will be granted permission
-- to send messages to the local service OrderParts. 
-- This example assumes that the certificate for the service 
-- is saved in the file'C:\Certificates\SupplierOrders.cer' and that
-- the initiating service already exists.


-- Create a user without a login.

CREATE USER [SupplierOrdersUser]
    WITHOUT LOGIN ;
GO

-- Install a certificate for the owner of the service
-- in the remote database. The certificate is
-- provided by the owner of the remote service. The
-- user for the remote service owns the certificate.

CREATE CERTIFICATE [SupplierOrdersCertificate]
    AUTHORIZATION [SupplierOrdersUser]
    FROM FILE='C:\Certificates\SupplierOrders.cer' ;
GO

-- Create the remote service binding. Notice
-- that the user specified in the binding
-- does not own the binding itself.

-- Creating this binding specifies that messages from
-- this database are secured using the certificate for
-- the [SupplierOrdersUser] user.

-- Since anonymous is ON, the credentials for the user
-- that begins the conversation are not used for the
-- conversation.

CREATE REMOTE SERVICE BINDING [SupplierOrdersBinding]
    TO SERVICE 'SupplierOrders'
    WITH USER = [SupplierOrdersUser],
         ANONYMOUS = ON ;
GO

See Also

Tasks

How to: Configure Permissions for a Local Service (Transact-SQL)

Other Resources

CREATE CERTIFICATE (Transact-SQL)
CREATE USER (Transact-SQL)
CREATE REMOTE SERVICE BINDING (Transact-SQL)
OPEN MASTER KEY (Transact-SQL)
CLOSE MASTER KEY (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance