Como utilizar certificados para SQL Server 2005 Service Broker segurança remota várias instâncias do SQL Server 2005

• Criados quatro certificados e quatro ficheiros de chaves privados utilizando a ferramenta de criação de certificados (makecert.exe). Este exemplo assume que os ficheiros foram copiados para a pasta C:\Certificates em ambos os servidores e que os ficheiros são denominados o seguinte:
  • SourceServer.cer
  • SourceServer.pvk
  • TargetServer.cer
  • TargetServer.pvk
  • DlgSourceServer.cer
  • DlgSourceServer.pvk
  • DlgTargetServer.cer
  • DlgTargetServer.pvk
  Para obter mais informações sobre como criar certificados para testar, visite o seguinte Web site da Microsoft Developer Network (MSDN):
  http://msdn2.microsoft.com/en-us/library/bfsktky3(vs.71).aspx (http://msdn2.microsoft.com/en-us/library/bfsktky3(vs.71).aspx)
• Instalou duas instâncias do SQL Server 2005 em servidores diferentes na mesma rede. Este exemplo assume que o primeiro servidor chama ServerSrc e que o segundo servidor chama ServerTag.
• Ligar a duas instâncias utilizando inícios de sessão que façam parte de sysadmin o SQL Server fixo a função de servidor.
• Efectuou-se de que a porta TCP 4022 está disponível. Neste exemplo, a porta será utilizada por duas instâncias para ligar entre si.

Voltar ao topo

Configurar o SQL Server 2005 Service Broker para a instância do SQL Server no servidor ServerSrc

1. Ligar a instância no servidor ServerSrc utilizando o SQL Server Management Studio.
2. Execute as seguintes instruções de Transact-SQL no editor de consulta:

   --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
   AUTHORIZATION 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'
   
   --Create a certificate for the SourceDB database.
   CREATE CERTIFICATE ctfDlgSourceServer
   FROM FILE = 'C:\certificates\DlgSourceServer.cer'
   WITH PRIVATE KEY 
   (FILE='C:\certificates\DlgSourceServer.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
   AUTHORIZATION remcert
   FROM FILE = 'C:\certificates\DlgTargetServer.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
   
   --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';
   
   

   Nota MasterKeyPassword é um marcador para a palavra-passe da chave principal que tem de especificar para a base de dados. PrivateKeyPassword é um marcador de posição para a palavra-passe da chave privada que especificou para o ficheiro de chave privada .pvk utilizando a ferramenta de criação de certificados. LoginPassword é um marcador de posição para a palavra-passe de início de sessão recém-criado.

Voltar ao topo

Configurar o SQL Server 2005 Service Broker para a instância do SQL Server no servidor ServerTag

1. Ligar a instância no servidor ServerTag utilizando o SQL Server Management Studio.
2. Execute as seguintes instruções de Transact-SQL no editor de consulta:

   --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 ctfTargetServerMaster
   FROM FILE = 'c:\certificates\TargetServer.cer'
   WITH PRIVATE KEY (FILE='c:\certificates\TargetServer.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 ctfSourceServerMaster
   AUTHORIZATION remcert
   FROM FILE = 'c:\certificates\SourceServer.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 ctfTargetServerMaster)
   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 TargetDB
   GO
   USE TargetDB
   GO
   
   --Configure the dialog security.
   
   --Create a master key in the TargetDB database.
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKeyPassword'
   
   --Create a certificate for the TargetDB database.
   CREATE CERTIFICATE ctfDlgTargetServer
   FROM FILE = 'c:\certificates\DlgTargetServer.cer'
   WITH PRIVATE KEY 
   (FILE='c:\certificates\DlgTargetServer.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 ctfDlgSourceServer
   AUTHORIZATION remcert
   FROM FILE = 'C:\certificates\DlgSourceServer.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 [TargetService] ON QUEUE [myQueue]([mycon])
   GO
   
   --Grant the send permission to the user.
   GRANT SEND ON SERVICE::[TargetService] TO remcert
   GO
   
   --Create a remote service binding for the target service. 
   CREATE REMOTE SERVICE BINDING [Certificate_Binding_on_server]
      TO SERVICE 'SourceService'
      WITH  USER = remcert,
      ANONYMOUS=Off  
   --Create a route for the target service.
   CREATE ROUTE [myRoute]
       WITH 
       SERVICE_NAME = 'SourceService',
       address = 'TCP://ServerSrc:4022';

Voltar ao topo

Testar a segurança remota Broker de serviço do SQL Server 2005

USE SourceDB
SET NOCOUNT ON
DECLARE @conversationHandle uniqueidentifier
BEGIN TRANSACTION
	-- Start dialog.
	BEGIN DIALOG  @conversationHandle
	FROM SERVICE    [SourceService]
	TO SERVICE      'TargetService'
	ON CONTRACT     [mycon]
	WITH ENCRYPTION = ON, LIFETIME = 600;

	-- Send message.
	SEND ON CONVERSATION @conversationHandle 
	MESSAGE TYPE [mymsg] (N'Hi, from '+@@ServerName)
COMMIT

SELECT CONVERT(NVARCHAR(MAX),message_body) FROM myQueue
GO

Hi, from ServerSrc

Voltar ao topo

• Gerir a segurança (serviço Broker)
• Segurança de rede e remota
• Serviço Broker diálogo segurança
• Determinar o tipo de segurança de diálogo
• Enlaces de serviços remoto

Voltar ao topo