SQL Server 2005 Service Broker remote security includes operations that involve more than one instance of SQL Server when those operations use either dialog security or transport security. The following example demonstrates how to use certificates for SQL Server 2005 Service Broker remote security on two instances of SQL Server 2005.

. You installed two instances of SQL Server 2005 on different servers in the same network. This example assumes that the first server is named ServerSrc and that the second server is named ServerTag.
• You connect to the two instances by using logins that are members of the SQL Server sysadmin fixed server role.
• You made sure that the TCP port 4022 is available. In this example, the port will be used by the two instances to connect to each other.

Configure SQL Server 2005 Service Broker for the instance of SQL Server on the ServerSrc server
1. Connect to the instance on the ServerSrc server by using SQL Server Management Studio.
2. Run the following Transact-SQL statements in the query editor:--Configure the transport security.
USE MASTER
go

--Create a master key in the master database.
CREATE MASTER KEY ENCRYPTION BY password = 'MasterKeyPassword'
Go

--C reate a certificate for transport security.
CREATE CERTIFICATE ctfSourceServerMaster
FROM FILE = 'C:\Certificates\SourceServer. cer'
WITH PRIVATE KEY ( FILE = 'C:\Certificates\SourceServer. pvk' , DECRYPTION BY PASSWORD = 'PrivateKeyPassword' )
ACTIVE FOR BEGIN_DIALOG = ON
GO

--Create the login and the user to own a certificate.
CREATE LOGIN remcert WITH PASSWORD = 'LoginPassword'
GO
CREATE USER remcert FOR LOGIN remcert
GO
CREATE CERTIFICATE ctftTargetServerMaster
AUTHOR IZATION remcert
FROM FILE = 'C:\Certificates\TargetServer. cer'
ACTIVE FOR BEGIN_DIALOG = ON
GO

--Create a new endpoint for SQL Server 2005 Service Broker, and set the AUTHENTICATION option to use the ctfSourceServerMaster certificate.
CREATE ENDPOINT BrokerEndpoint
STATE = STARTED
AS TCP
(
LISTENER_PORT = 4022
)
FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE ctfSourceServerMaster)
GO

--Gra nt the required permissions to the remcert login.
GRANT CONNECT TO remcert
GRANT CONNECT ON ENDPOINT::BrokerEndpoint to remcert
GO

--Create a new database for testing.
CREATE DATABASE SourceDB
GO
USE SourceDB
GO

--Configure the dialog security.

--Create a master key in the SourceDB database.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKeyPassword'

--Creat e a certificate for the SourceDB database.
CREATE CERTIFICATE ctfDlgSourceServer
FROM FILE = 'C:\certificates\DlgSourceServ er.cer'
WITH PRIVATE KEY
(FILE='C:\certificates\Dl gSo urceServer.pvk',decryption by password='PrivateKeyPassword')
ACT IVE FOR BEGIN_DIALOG = ON
GO

--Create a user for the remcert login that owns a certificate for the dialog security.
CREATE USER remcert for LOGIN remcert
GO
CREATE CERTIFICATE ctfDlgTargetServer
AUTHORIZAT ION remcert
FROM FILE = 'C:\certificates\DlgTargetServ er.cer'
ACTIVE FOR BEGIN_DIALOG = ON

--Create a message type, a contract, a queue, and a service.

CREATE MESSAGE TYPE [mymsg] VALIDATION = NONE
CREATE CONTRACT [mycon] ([mymsg] SENT BY ANY)
CREATE QUEUE [myQueue]
CREATE SERVICE [SourceService] ON QUEUE [myQueue]([mycon])
GO

--Gr ant the send permission to the user.
GRANT SEND ON SERVICE::[SourceService] TO remcert

--Create a remote service binding for the target service.
CREATE REMOTE SERVICE BINDING [Certificate_Binding_on_server ]
TO SERVICE 'TargetService'
WITH USER = remcert,
ANONYMOUS=Off

--C reate a route for the target service.
CREATE ROUTE [myRoute]
WITH
SERVICE_NAME

Answered by Arti singh , an ibibo Specialist, at 2:29 PM on June 17, 2008

Hi there

SQL Server 2005 Service Broker remote security includes operations that involve more than one instance of SQL Server when those operations use either dialog security or transport security. The following example demonstrates how to use certificates for SQL Server 2005 Service Broker remote security on two instances of SQL Server 2005.

. You installed two instances of SQL Server 2005 on different servers in the same network. This example assumes that the first server is named ServerSrc and that the second server is named ServerTag.
• You connect to the two instances by using logins that are members of the SQL Server sysadmin fixed server role.
• You made sure that the TCP port 4022 is available. In this example, the port will be used by the two instances to connect to each other.

Configure SQL Server 2005 Service Broker for the instance of SQL Server on the ServerSrc server
1. Connect to the instance on the ServerSrc server by using SQL Server Management Studio.
2. Run the following Transact-SQL statements in the query editor:--Configure the transport security.
USE MASTER
go

--Create a master key in the master database.
CREATE MASTER KEY ENCRYPTION BY password = 'MasterKeyPassword'
Go

--C reate a certificate for transport security.
CREATE CERTIFICATE ctfSourceServerMaster
FROM FILE = 'C:\Certificates\SourceServer. cer'
WITH PRIVATE KEY ( FILE = 'C:\Certificates\SourceServer. pvk' , DECRYPTION BY PASSWORD = 'PrivateKeyPassword' )
ACTIVE FOR BEGIN_DIALOG = ON
GO

--Create the login and the user to own a certificate.
CREATE LOGIN remcert WITH PASSWORD = 'LoginPassword'
GO
CREATE USER remcert FOR LOGIN remcert
GO
CREATE CERTIFICATE ctftTargetServerMaster
AUTHOR IZATION remcert
FROM FILE = 'C:\Certificates\TargetServer. cer'
ACTIVE FOR BEGIN_DIALOG = ON
GO

--Create a new endpoint for SQL Server 2005 Service Broker, and set the AUTHENTICATION option to use the ctfSourceServerMaster certificate.
CREATE ENDPOINT BrokerEndpoint
STATE = STARTED
AS TCP
(
LISTENER_PORT = 4022
)
FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE ctfSourceServerMaster)
GO

--Gra nt the required permissions to the remcert login.
GRANT CONNECT TO remcert
GRANT CONNECT ON ENDPOINT::BrokerEndpoint to remcert
GO

--Create a new database for testing.
CREATE DATABASE SourceDB
GO
USE SourceDB
GO

--Configure the dialog security.

--Create a master key in the SourceDB database.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKeyPassword'

--Creat e a certificate for the SourceDB database.
CREATE CERTIFICATE ctfDlgSourceServer
FROM FILE = 'C:\certificates\DlgSourceServ er.cer'
WITH PRIVATE KEY
(FILE='C:\certificates\Dl gSo urceServer.pvk',decryption by password='PrivateKeyPassword')
ACT IVE FOR BEGIN_DIALOG = ON
GO

--Create a user for the remcert login that owns a certificate for the dialog security.
CREATE USER remcert for LOGIN remcert
GO
CREATE CERTIFICATE ctfDlgTargetServer
AUTHORIZAT ION remcert
FROM FILE = 'C:\certificates\DlgTargetServ er.cer'
ACTIVE FOR BEGIN_DIALOG = ON

--Create a message type, a contract, a queue, and a service.

CREATE MESSAGE TYPE [mymsg] VALIDATION = NONE
CREATE CONTRACT [mycon] ([mymsg] SENT BY ANY)
CREATE QUEUE [myQueue]
CREATE SERVICE [SourceService] ON QUEUE [myQueue]([mycon])
GO

--Gr ant the send permission to the user.
GRANT SEND ON SERVICE::[SourceService] TO remcert

--Create a remote service binding for the target service.
CREATE REMOTE SERVICE BINDING [Certificate_Binding_on_server ]
TO SERVICE 'TargetService'
WITH USER = remcert,
ANONYMOUS=Off

--C reate a route for the target service.
CREATE ROUTE [myRoute]
WITH
SERVICE_NAME

Answered by Reeta K , an ibibo Master, at 11:16 AM on May 29, 2008

well Hardik,
SQL Server 2005 Service Broker remote security includes operations that involve more than one instance of SQL Server when those operations use either dialog security or transport security. The following example demonstrates how to use certificates for SQL Server 2005 Service Broker remote security on two instances of SQL Server 2005.

. You installed two instances of SQL Server 2005 on different servers in the same network. This example assumes that the first server is named ServerSrc and that the second server is named ServerTag.
• You connect to the two instances by using logins that are members of the SQL Server sysadmin fixed server role.
• You made sure that the TCP port 4022 is available. In this example, the port will be used by the two instances to connect to each other.

Configure SQL Server 2005 Service Broker for the instance of SQL Server on the ServerSrc server
1. Connect to the instance on the ServerSrc server by using SQL Server Management Studio.
2. Run the following Transact-SQL statements in the query editor:--Configure the transport security.
USE MASTER
go

--Create a master key in the master database.
CREATE MASTER KEY ENCRYPTION BY password = 'MasterKeyPassword'
Go

-C reate a certificate for transport security.
CREATE CERTIFICATE ctfSourceServerMaster
FROM FILE = 'C:\Certificates\SourceServer. cer'
WITH PRIVATE KEY ( FILE = 'C:\Certificates\SourceServer. pvk' , DECRYPTION BY PASSWORD = 'PrivateKeyPassword' )
ACTIVE FOR BEGIN_DIALOG = ON
GO

--Create the login and the user to own a certificate.
CREATE LOGIN remcert WITH PASSWORD = 'LoginPassword'
GO
CREATE USER remcert FOR LOGIN remcert
GO
CREATE CERTIFICATE ctftTargetServerMaster
AUTHOR IZATION remcert
FROM FILE = 'C:\Certificates\TargetServer. cer'
ACTIVE FOR BEGIN_DIALOG = ON
GO

--Create a new endpoint for SQL Server 2005 Service Broker, and set the AUTHENTICATION option to use the ctfSourceServerMaster certificate.
CREATE ENDPOINT BrokerEndpoint
STATE = STARTED
AS TCP
(
LISTENER_PORT = 4022
)
FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE ctfSourceServerMaster)
GO

--Gra nt the required permissions to the remcert login.
GRANT CONNECT TO remcert
GRANT CONNECT ON ENDPOINT::BrokerEndpoint to remcert
GO

--Create a new database for testing.
CREATE DATABASE SourceDB
GO
USE SourceDB
GO
Configure the dialog security.

--Create a master key in the SourceDB database.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKeyPassword'

--Creat e a certificate for the SourceDB database.
CREATE CERTIFICATE ctfDlgSourceServer
FROM FILE = 'C:\certificates\DlgSourceServ er.cer'
WITH PRIVATE KEY
(FILE='C:\certificates\Dl gSo urceServer.pvk',decryption by password='PrivateKeyPassword')
ACT IVE FOR BEGIN_DIALOG = ON
GO

--Create a user for the remcert login that owns a certificate for the dialog security.
CREATE USER remcert for LOGIN remcert
GO
CREATE CERTIFICATE ctfDlgTargetServer
AUTHORIZAT ION remcert
FROM FILE = 'C:\certificates\DlgTargetServ er.cer'
ACTIVE FOR BEGIN_DIALOG = ON

--Create a message type, a contract, a queue, and a service.

CREATE MESSAGE TYPE [mymsg] VALIDATION = NONE
CREATE CONTRACT [mycon] ([mymsg] SENT BY ANY)
CREATE QUEUE [myQueue]
CREATE SERVICE [SourceService] ON QUEUE [myQueue]([mycon])
GO

--Gr ant the send permission to the user.
GRANT SEND ON SERVICE::[SourceService] TO remcert

--Create a remote service binding for the target service.
CREATE REMOTE SERVICE BINDING [Certificate_Binding_on_server ]
TO SERVICE 'TargetService'
WITH USER = remcert,
ANONYMOUS=Off

--C reate a route for the target service.
CREATE ROUTE [myRoute]
WITH
SERVICE_NAME = 'Targ

Answered by jaivir , an ibibo Master, at 6:03 PM on May 22, 2008

SQL Server 2005 Service Broker remote security includes operations that involve more than one instance of SQL Server when those operations use either dialog security or transport security. The following example demonstrates how to use certificates for SQL Server 2005 Service Broker remote security on two instances of SQL Server 2005.

. You installed two instances of SQL Server 2005 on different servers in the same network. This example assumes that the first server is named ServerSrc and that the second server is named ServerTag.
• You connect to the two instances by using logins that are members of the SQL Server sysadmin fixed server role.
• You made sure that the TCP port 4022 is available. In this example, the port will be used by the two instances to connect to each other.

Configure SQL Server 2005 Service Broker for the instance of SQL Server on the ServerSrc server
1. Connect to the instance on the ServerSrc server by using SQL Server Management Studio.
2. Run the following Transact-SQL statements in the query editor:--Configure the transport security.
USE MASTER
go

--Create a master key in the master database.
CREATE MASTER KEY ENCRYPTION BY password = 'MasterKeyPassword'
Go

--C reate a certificate for transport security.
CREATE CERTIFICATE ctfSourceServerMaster
FROM FILE = 'C:\Certificates\SourceServer. cer'
WITH PRIVATE KEY ( FILE = 'C:\Certificates\SourceServer. pvk' , DECRYPTION BY PASSWORD = 'PrivateKeyPassword' )
ACTIVE FOR BEGIN_DIALOG = ON
GO

--Create the login and the user to own a certificate.
CREATE LOGIN remcert WITH PASSWORD = 'LoginPassword'
GO
CREATE USER remcert FOR LOGIN remcert
GO
CREATE CERTIFICATE ctftTargetServerMaster
AUTHOR IZATION remcert
FROM FILE = 'C:\Certificates\TargetServer. cer'
ACTIVE FOR BEGIN_DIALOG = ON
GO

--Create a new endpoint for SQL Server 2005 Service Broker, and set the AUTHENTICATION option to use the ctfSourceServerMaster certificate.
CREATE ENDPOINT BrokerEndpoint
STATE = STARTED
AS TCP
(
LISTENER_PORT = 4022
)
FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE ctfSourceServerMaster)
GO

--Gra nt the required permissions to the remcert login.
GRANT CONNECT TO remcert
GRANT CONNECT ON ENDPOINT::BrokerEndpoint to remcert
GO

--Create a new database for testing.
CREATE DATABASE SourceDB
GO
USE SourceDB
GO

--Configure the dialog security.

--Create a master key in the SourceDB database.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKeyPassword'

--Creat e a certificate for the SourceDB database.
CREATE CERTIFICATE ctfDlgSourceServer
FROM FILE = 'C:\certificates\DlgSourceServ er.cer'
WITH PRIVATE KEY
(FILE='C:\certificates\Dl gSo urceServer.pvk',decryption by password='PrivateKeyPassword')
ACT IVE FOR BEGIN_DIALOG = ON
GO

--Create a user for the remcert login that owns a certificate for the dialog security.
CREATE USER remcert for LOGIN remcert
GO
CREATE CERTIFICATE ctfDlgTargetServer
AUTHORIZAT ION remcert
FROM FILE = 'C:\certificates\DlgTargetServ er.cer'
ACTIVE FOR BEGIN_DIALOG = ON

--Create a message type, a contract, a queue, and a service.

CREATE MESSAGE TYPE [mymsg] VALIDATION = NONE
CREATE CONTRACT [mycon] ([mymsg] SENT BY ANY)
CREATE QUEUE [myQueue]
CREATE SERVICE [SourceService] ON QUEUE [myQueue]([mycon])
GO

--Gr ant the send permission to the user.
GRANT SEND ON SERVICE::[SourceService] TO remcert

--Create a remote service binding for the target service.
CREATE REMOTE SERVICE BINDING [Certificate_Binding_on_server ]
TO SERVICE 'TargetService'
WITH USER = remcert,
ANONYMOUS=Off

--C reate a route for the target service.
CREATE ROUTE [myRoute]
WITH
SERVICE_NAME = 'TargetServic

Answered by Nagendra , an ibibo Master, at 7:56 AM on May 22, 2008

well hardik,visit the following Microsoft Developer Network (MSDN) Web site:
http://msdn2.microsoft. com/en-us/library/bfsktky3(vs. 71).aspx (http://msdn2.microsoft.com/en -us/library/bfsktky3(vs.71).as px)
• You installed two instances of SQL Server 2005 on different servers in the same network. This example assumes that the first server is named ServerSrc and that the second server is named ServerTag.
• You connect to the two instances by using logins that are members of the SQL Server sysadmin fixed server role.
• You made sure that the TCP port 4022 is available. In this example, the port will be used by the two instances to connect to each other.
When all the previous conditions are met, use the following procedures.
Back to the top

Configure SQL Server 2005 Service Broker for the instance of SQL Server on the ServerSrc server
1. Connect to the instance on the ServerSrc server by using SQL Server Management Studio.
2. Run the following Transact-SQL statements in the query editor:--Configure the transport security.
USE MASTER
go

Answered by Romi , an ibibo Master, at 6:19 PM on May 21, 2008

well, Configure SQL Server 2005 Service Broker for the instance of SQL Server on the ServerSrc server

1. Connect to the instance on the ServerSrc server by using SQL Server Management Studio.
2. Run the following Transact-SQL statements in the query editor:

--Configure the transport security.
USE MASTER
go

--Create a master key in the master database.
CREATE MASTER KEY ENCRYPTION BY password = 'MasterKeyPassword'
Go

--C reate a certificate for transport security.
CREATE CERTIFICATE ctfSourceServerMaster
FROM FILE = 'C:\Certificates\SourceServer. cer'
WITH PRIVATE KEY ( FILE = 'C:\Certificates\SourceServer. pvk' , DECRYPTION BY PASSWORD = 'PrivateKeyPassword' )
ACTIVE FOR BEGIN_DIALOG = ON
GO

--Create the login and the user to own a certificate.
CREATE LOGIN remcert WITH PASSWORD = 'LoginPassword'
GO
CREATE USER remcert FOR LOGIN remcert
GO
CREATE CERTIFICATE ctftTargetServerMaster
AUTHOR IZATI ON remcert
FROM FILE = 'C:\Certificates\TargetServer. cer'
ACTIVE FOR BEGIN_DIALOG = ON
GO

--Create a new endpoint for SQL Server 2005 Service Broker, and set the AUTHENTICATION option to use the ctfSourceServerMaster certificate.
CREATE ENDPOINT BrokerEndpoint
STATE = STARTED
AS TCP
(
LISTENER_PORT = 4022
)
FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE ctfSourceServerMaster)
GO

--Gra nt the required permissions to the remcert login.
GRANT CONNECT TO remcert
GRANT CONNECT ON ENDPOINT::BrokerEndpoint to remcert
GO

--Create a new database for testing.
CREATE DATABASE SourceDB
GO
USE SourceDB
GO

--Configure the dialog security.

--Create a master key in the SourceDB database.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKeyPassword'

--Creat e a certificate for the SourceDB database.
CREATE CERTIFICATE ctfDlgSourceServer
FROM FILE = 'C:\certificates\DlgSourceServ er.ce r'
WITH PRIVATE KEY
(FILE='C:\certificates\Dl gSourceS erver.pvk',decryption by password='PrivateKeyPassword')
ACT IVE FOR BEGIN_DIALOG = ON
GO

--Create a user for the remcert login that owns a certificate for the dialog security.
CREATE USER remcert for LOGIN remcert
GO
CREATE CERTIFICATE ctfDlgTargetServer
AUTHORIZAT ION remcert
FROM FILE = 'C:\certificates\DlgTargetServ er.ce r'
ACTIVE FOR BEGIN_DIALOG = ON

--Create a message type, a contract, a queue, and a service.

CREATE MESSAGE TYPE [mymsg] VALIDATION = NONE
CREATE CONTRACT [mycon] ([mymsg] SENT BY ANY)
CREATE QUEUE [myQueue]
CREATE SERVICE [SourceService] ON QUEUE [myQueue]([mycon])
GO

--Gr ant the send permission to the user.
GRANT SEND ON SERVICE::[SourceService] TO remcert

--Create a remote service binding for the target service.
CREATE REMOTE SERVICE BINDING [Certificate_Binding_on_server ]
TO SERVICE 'TargetService'
WITH USER = remcert,
ANONYMOUS=Off

--C reate a route for the target service.
CREATE ROUTE [myRoute]
WITH
SERVICE_NAME = 'TargetService',
address = 'TCP://ServerTag:4022'

For more details , Please visit the source site :

http://support.microsoft. com/k b/915852

Answered by Shobhit , an ibibo Master, at 2:09 PM on May 21, 2008

hello hardik Creating an SQL Server Login ID
Login IDs are the accounts that control access to the SQL Server system. A login ID must be associated with a user ID in each database before anyone connecting with that login ID can access objects in the database. By default the login ID and the user ID are the same.

To create a SQL Server login ID

Start SQL Server Enterprise Manager from the Start menu.
Expand the Microsoft SQL Servers node, and then expand the SQL Server Group node. Your database server registration is located under this node.
Expand the <database server registration> node, and then expand the Security folder.
Double-click the Logins icon, and then choose New Login from the Action menu.
In the SQL Server Login Properties – New Login dialog box, type the user name in the Name box using the <domain\Name> format. Be sure that the Windows Authentication option is selected.
Choose OK.
for more inf check source http://msdn.microsoft.com/en-us/lib rary/ms941461.aspx

Answered by raj u , an ibibo Master, at 12:55 PM on May 21, 2008

Well Hardit, we simply cant create new login id in SQL server.

We can only configure it.

Configure SQL Server 2005 Service Broker for the instance of SQL Server on the ServerSrc server

1. Connect to the instance on the ServerSrc server by using SQL Server Management Studio.
2. Run the following Transact-SQL statements in the query editor:

--Configure the transport security.
USE MASTER
go

--Create a master key in the master database.
CREATE MASTER KEY ENCRYPTION BY password = 'MasterKeyPassword'
Go

--Create a certificate for transport security.
CREATE CERTIFICATE ctfSourceServerMaster
FROM FILE = 'C:\Certificates\SourceServer.cer'
WITH PRIVATE KEY ( FILE = 'C:\Certificates\SourceServer.pvk' , DECRYPTION BY PASSWORD = 'PrivateKeyPassword' )
ACTIVE FOR BEGIN_DIALOG = ON
GO

--Create the login and the user to own a certificate.
CREATE LOGIN remcert WITH PASSWORD = 'LoginPassword'
GO
CREATE USER remcert FOR LOGIN remcert
GO
CREATE CERTIFICATE ctftTargetServerMaster
AUTHORIZATI ON remcert
FROM FILE = 'C:\Certificates\TargetServer.cer'
ACTIVE FOR BEGIN_DIALOG = ON
GO

--Create a new endpoint for SQL Server 2005 Service Broker, and set the AUTHENTICATION option to use the ctfSourceServerMaster certificate.
CREATE ENDPOINT BrokerEndpoint
STATE = STARTED
AS TCP
(
LISTENER_PORT = 4022
)
FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE ctfSourceServerMaster)
GO

--Gra nt the required permissions to the remcert login.
GRANT CONNECT TO remcert
GRANT CONNECT ON ENDPOINT::BrokerEndpoint to remcert
GO

--Create a new database for testing.
CREATE DATABASE SourceDB
GO
USE SourceDB
GO

--Configure the dialog security.

--Create a master key in the SourceDB database.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKeyPassword'

--Create a certificate for the SourceDB database.
CREATE CERTIFICATE ctfDlgSourceServer
FROM FILE = 'C:\certificates\DlgSourceServer.ce r'
WITH PRIVATE KEY
(FILE='C:\certificates\DlgSourceS erver.pvk',decryption by password='PrivateKeyPassword')
ACT IVE FOR BEGIN_DIALOG = ON
GO

--Create a user for the remcert login that owns a certificate for the dialog security.
CREATE USER remcert for LOGIN remcert
GO
CREATE CERTIFICATE ctfDlgTargetServer
AUTHORIZATION remcert
FROM FILE = 'C:\certificates\DlgTargetServer.ce r'
ACTIVE FOR BEGIN_DIALOG = ON

--Create a message type, a contract, a queue, and a service.

CREATE MESSAGE TYPE [mymsg] VALIDATION = NONE
CREATE CONTRACT [mycon] ([mymsg] SENT BY ANY)
CREATE QUEUE [myQueue]
CREATE SERVICE [SourceService] ON QUEUE [myQueue]([mycon])
GO

--Grant the send permission to the user.
GRANT SEND ON SERVICE::[SourceService] TO remcert

--Create a remote service binding for the target service.
CREATE REMOTE SERVICE BINDING [Certificate_Binding_on_server]
TO SERVICE 'TargetService'
WITH USER = remcert,
ANONYMOUS=Off

--Create a route for the target service.
CREATE ROUTE [myRoute]
WITH
SERVICE_NAME = 'TargetService',
address = 'TCP://ServerTag:4022'

For more details , Please visit the source site :

http://support.microsoft.com/k b/915852

Answered by Alok Gupta , an ibibo Guru, at 11:01 AM on May 21, 2008

SQL Server 2005 Service Broker remote security includes operations that involve more than one instance of SQL Server when those operations use either dialog security or transport security. The following example demonstrates how to use certificates for SQL Server 2005 Service Broker remote security on two instances of SQL Server 2005.

. You installed two instances of SQL Server 2005 on different servers in the same network. This example assumes that the first server is named ServerSrc and that the second server is named ServerTag.
• You connect to the two instances by using logins that are members of the SQL Server sysadmin fixed server role.
• You made sure that the TCP port 4022 is available. In this example, the port will be used by the two instances to connect to each other.

Configure SQL Server 2005 Service Broker for the instance of SQL Server on the ServerSrc server
1. Connect to the instance on the ServerSrc server by using SQL Server Management Studio.
2. Run the following Transact-SQL statements in the query editor:--Configure the transport security.
USE MASTER
go

--Create a master key in the master database.
CREATE MASTER KEY ENCRYPTION BY password = 'MasterKeyPassword'
Go

--C reate a certificate for transport security.
CREATE CERTIFICATE ctfSourceServerMaster
FROM FILE = 'C:\Certificates\SourceServer. cer'
WITH PRIVATE KEY ( FILE = 'C:\Certificates\SourceServer. pvk' , DECRYPTION BY PASSWORD = 'PrivateKeyPassword' )
ACTIVE FOR BEGIN_DIALOG = ON
GO

--Create the login and the user to own a certificate.
CREATE LOGIN remcert WITH PASSWORD = 'LoginPassword'
GO
CREATE USER remcert FOR LOGIN remcert
GO
CREATE CERTIFICATE ctftTargetServerMaster
AUTHOR IZATION remcert
FROM FILE = 'C:\Certificates\TargetServer. cer'
ACTIVE FOR BEGIN_DIALOG = ON
GO

--Create a new endpoint for SQL Server 2005 Service Broker, and set the AUTHENTICATION option to use the ctfSourceServerMaster certificate.
CREATE ENDPOINT BrokerEndpoint
STATE = STARTED
AS TCP
(
LISTENER_PORT = 4022
)
FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE ctfSourceServerMaster)
GO

--Grant the required permissions to the remcert login.
GRANT CONNECT TO remcert
GRANT CONNECT ON ENDPOINT::BrokerEndpoint to remcert
GO

--Create a new database for testing.
CREATE DATABASE SourceDB
GO
USE SourceDB
GO

--Configure the dialog security.

--Create a master key in the SourceDB database.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKeyPassword'

--Creat e a certificate for the SourceDB database.
CREATE CERTIFICATE ctfDlgSourceServer
FROM FILE = 'C:\certificates\DlgSourceServ er.cer'
WITH PRIVATE KEY
(FILE='C:\certificates\Dl gSo urceServer.pvk',decryption by password='PrivateKeyPassword')
ACTIVE FOR BEGIN_DIALOG = ON
GO

--Create a user for the remcert login that owns a certificate for the dialog security.
CREATE USER remcert for LOGIN remcert
GO
CREATE CERTIFICATE ctfDlgTargetServer
AUTHORIZAT ION remcert
FROM FILE = 'C:\certificates\DlgTargetServ er.cer'
ACTIVE FOR BEGIN_DIALOG = ON

--Create a message type, a contract, a queue, and a service.

CREATE MESSAGE TYPE [mymsg] VALIDATION = NONE
CREATE CONTRACT [mycon] ([mymsg] SENT BY ANY)
CREATE QUEUE [myQueue]
CREATE SERVICE [SourceService] ON QUEUE [myQueue]([mycon])
GO

--Gr ant the send permission to the user.
GRANT SEND ON SERVICE::[SourceService] TO remcert

--Create a remote service binding for the target service.
CREATE REMOTE SERVICE BINDING [Certificate_Binding_on_server ]
TO SERVICE 'TargetService'
WITH USER = remcert,
ANONYMOUS=Off

--C reate a route for the target service.
CREATE ROUTE [myRoute]
WITH
SERVICE_NAME = 'TargetServic

Answered by Om Prakash , an ibibo Master, at 9:35 AM on May 21, 2008

hi hardik :-
visit the following Microsoft Developer Network (MSDN) Web site:
http://msdn2.microsoft. com/en-us/library/bfsktky3(vs. 71).aspx (http://msdn2.microsoft.com/en -us/library/bfsktky3(vs.71).as px)
• You installed two instances of SQL Server 2005 on different servers in the same network. This example assumes that the first server is named ServerSrc and that the second server is named ServerTag.
• You connect to the two instances by using logins that are members of the SQL Server sysadmin fixed server role.
• You made sure that the TCP port 4022 is available. In this example, the port will be used by the two instances to connect to each other.
When all the previous conditions are met, use the following procedures.
Back to the top

Configure SQL Server 2005 Service Broker for the instance of SQL Server on the ServerSrc server
1. Connect to the instance on the ServerSrc server by using SQL Server Management Studio.
2. Run the following Transact-SQL statements in the query editor:--Configure the transport security.
USE MASTER
go

--Create a master key in the master database.
CREATE MASTER KEY ENCRYPTION BY password = 'MasterKeyPassword'
Go

--C reate a certificate for transport security.
CREATE CERTIFICATE ctfSourceServerMaster
FROM FILE = 'C:\Certificates\SourceServer. cer'
WITH PRIVATE KEY ( FILE = 'C:\Certificates\SourceServer. pvk' , DECRYPTION BY PASSWORD = 'PrivateKeyPassword' )
ACTIVE FOR BEGIN_DIALOG = ON
GO

--Create the login and the user to own a certificate.
CREATE LOGIN remcert WITH PASSWORD = 'LoginPassword'
GO
CREATE USER remcert FOR LOGIN remcert
GO
CREATE CERTIFICATE ctftTargetServerMaster
AUTHOR IZATION remcert
FROM FILE = 'C:\Certificates\TargetServer. cer'
ACTIVE FOR BEGIN_DIALOG = ON
GO

--Create a new endpoint for SQL Server 2005 Service Broker, and set the AUTHENTICATION option to use the ctfSourceServerMaster certificate.
CREATE ENDPOINT BrokerEndpoint
STATE = STARTED
AS TCP
(
LISTENER_PORT = 4022
)
FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE ctfSourceServerMaster)
GO

--Grant the required permissions to the remcert login.
GRANT CONNECT TO remcert
GRANT CONNECT ON ENDPOINT::BrokerEndpoint to remcert
GO

--Create a new database for testing.
CREATE DATABASE SourceDB
GO
USE SourceDB
GO

--Configure the dialog security.

--Create a master key in the SourceDB database.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKeyPassword'

--Creat e a certificate for the SourceDB database.
CREATE CERTIFICATE ctfDlgSourceServer
FROM FILE = 'C:\certificates\DlgSourceServ er.cer'
WITH PRIVATE KEY
(FILE='C:\certificates\Dl gSo urceServer.pvk',decryption by password='PrivateKeyPassword')
ACTIVE FOR BEGIN_DIALOG = ON
GO

--Create a user for the remcert login that owns a certificate for the dialog security.
CREATE USER remcert for LOGIN remcert
GO
CREATE CERTIFICATE ctfDlgTargetServer
AUTHORIZAT ION remcert
FROM FILE = 'C:\certificates\DlgTargetServ er.cer'
ACTIVE FOR BEGIN_DIALOG = ON

--Create a message type, a contract, a queue, and a service.

CREATE MESSAGE TYPE [mymsg] VALIDATION = NONE
CREATE CONTRACT [mycon] ([mymsg] SENT BY ANY)
CREATE QUEUE [myQueue]
CREATE SERVICE [SourceService] ON QUEUE [myQueue]([mycon])
GO

--Gr ant the send permission to the user.
GRANT SEND ON SERVICE::[SourceService] TO remcert

--Create a remote service binding for the target service.
CREATE REMOTE SERVICE BINDING [Certificate_Binding_on_server ]
TO SERVICE 'TargetService'
WITH USER = remcert,
ANONYMOUS=Off

--C reate a route for the target service.
CREATE ROUTE [myRoute]<

Answered by shepherd , an ibibo Master, at 8:26 AM on May 21, 2008