Certificate not found

Hello I haw trouble getting the service broker to work I have 3 instances of SQL servers:

1 “Sender”SQL 2005 Server

2 “Receiver 1” SQLEXPRESS 2005

3 “Recevier 2” SQLEXPRESS 2005

What I wont is to be abele to do is to send a message from “Sender” to “Receiver 1” or “Recevier 2”.

I am abele to send a message from “Sender” to “Receiver 1” but if I send a message to “Receiver 2” I get a dialog security problem I think. If I use profiler I can se in “Receiver 2” the events:

Broker:Connection

Audit Broker Login

Broker:Message Classify

Audit Broker Conversation = Certificate not found

Broker:Message Undeliverable

And I cant find what′s wrong, this Is my scripts for etch instance.

“Sender”

USE master

CREATE CERTIFICATE Cert_ROBOTSRV

WITH SUBJECT = 'Cert_ROBOTSRV_auth',

START_DATE = '02/15/2007',

EXPIRY_DATE = '02/15/2015'

GO

BACKUP CERTIFICATE Cert_ROBOTSRV TO FILE = 'C:\Cert_ROBOTSRV'

GO

CREATE ENDPOINT SBEndpointServer STATE = STARTED

AS TCP (LISTENER_PORT = 5723)

FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE Cert_ROBOTSRV)

GO

CREATE USER andon

CREATE CERTIFICATE Cert_sevapc311_pub AUTHORIZATION andon

FROM FILE = 'C:\Cert_sevapc311'

--DROP CERTIFICATE Cert_Andonpc017_Trans

CREATE CERTIFICATE Cert_Andonpc017_Trans AUTHORIZATION andon

FROM FILE = 'C:\Cert_andonpc017_Trans'

--DROP LOGIN sbLogin

CREATE LOGIN sbLogin

FROM CERTIFICATE Cert_Andonpc017_Trans;

GO

GRANT CONNECT ON ENDPOINT::SBEndpointServer TO [public]

GRANT CONNECT ON ENDPOINT::SBEndpointServer TO andon

GO

-

USE AndonDB

CREATE ROUTE Grafik_sevapc311

WITH SERVICE_NAME = 'Grafik_Service_Recive_sevapc311',

BROKER_INSTANCE = '7C737F42-2DF6-46E7-A6B6-89D1A9608DE2',

ADDRESS = 'TCP://sevapc311:5723'

GO

--DROP ROUTE Grafik_andonpc017

CREATE ROUTE Grafik_Andonpc017

WITH SERVICE_NAME = 'Grafik_Service_Recive_Andonpc017',

BROKER_INSTANCE = 'AE2B294A-B02E-4709-A51E-CFBFD0E478C1',

ADDRESS = 'TCP://192.168.20.106:5723'

GO

CREATE CERTIFICATE Cert_ROBOTSRV_Dialog

WITH SUBJECT = 'Cert_ROBOTSRV_auth',

START_DATE = '02/15/2007',

EXPIRY_DATE = '02/15/2015'

GO

BACKUP CERTIFICATE Cert_ROBOTSRV_Dialog TO FILE = 'C:\Cert_ROBOTSRV_Dialog'

GO

CREATE CERTIFICATE Cert_sevapc311_pub_Dialog AUTHORIZATION andon

FROM FILE = 'C:\Cert_sevapc311_Dialog'

--DROP CERTIFICATE Cert_andonpc017_Dialog

CREATE CERTIFICATE Cert_Andonpc017_Dialog AUTHORIZATION andon

FROM FILE = 'C:\Cert_andonpc017_Dialog'

GRANT SEND ON SERVICE::[Grafik_Service_Send_ROBOTSRV]

TO andon

CREATE USER sbLogin

GRANT SEND ON SERVICE::[Grafik_Service_Send_ROBOTSRV]

TO sbLogin

GO

--GRANT SEND ON SERVICE::[Grafik_Service_Send_ROBOTSRV]

--TO [VADERSTAD\vrobot]

--GO

-- Grant RECEIVE permission on the queue.

GRANT RECEIVE ON [Grafik_Queue]

TO andon

GO

GRANT CONTROL ON SERVICE::[Grafik_Service_Send_ROBOTSRV]

TO andon

GO

--DROP REMOTE SERVICE BINDING Grafik_sevap

CREATE REMOTE SERVICE BINDING Grafik_sevap

TO SERVICE 'Grafik_Service_Recive_sevapc311'

WITH USER = andon

GO

--DROP REMOTE SERVICE BINDING Grafik_andonpc017

CREATE REMOTE SERVICE BINDING Grafik_andonpc017

TO SERVICE 'Grafik_Service_Recive_Andonpc017'

WITH USER = andon

“Receiver 1”

use master

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'andonANDON'

CREATE CERTIFICATE Cert_sevapc311

WITH SUBJECT = 'Cert_sevapc311_Auth',

START_DATE = '02/15/2007',

EXPIRY_DATE = '02/15/2015'

GO

BACKUP CERTIFICATE Cert_sevapc311 To FILE = 'C:\Cert_sevapc311'

CREATE ENDPOINT SBEndpointklient STATE = STARTED

AS TCP (LISTENER_PORT = 5723)

FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE Cert_sevapc311)

CREATE CERTIFICATE Cert_ROBOTSRV_pub AUTHORIZATION andon

FROM FILE = 'C:\Cert_ROBOTSRV';

GRANT CONNECT ON ENDPOINT::SBEndpointklient to andon

-

use KlientDB

GRANT SEND ON SERVICE::[Grafik_Service_Recive_sevapc311]

TO andon

GO

GRANT CONTROL ON SERVICE::[Grafik_Service_Recive_sevapc311]

TO andon

GO

GRANT RECEIVE ON [Grafik_Queue]

TO andon

GO

CREATE Route Grafik_ROBOTSRV

WITH

SERVICE_NAME = 'Grafik_Service_Send_ROBOTSRV',

BROKER_INSTANCE = '2BA192F8-0BA3-4237-A156-21AFF7C65481',

ADDRESS = 'TCP://ROBOTSRV:5723'

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'andonANDON'

CREATE CERTIFICATE Cert_sevapc311_Dialog

WITH SUBJECT = 'Cert_sevapc311_Auth',

START_DATE = '02/15/2007',

EXPIRY_DATE = '02/15/2015'

GO

BACKUP CERTIFICATE Cert_sevapc311_Dialog To FILE = 'C:\Cert_sevapc311_Dialog'

CREATE CERTIFICATE Cert_ROBOTSRV_pub_Dialog AUTHORIZATION andon

FROM FILE = 'C:\Cert_ROBOTSRV_Dialog';

“Receiver 2”

use master

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'andonANDON'

--DROP CERTIFICATE Cert_Andonpc017_Trans

CREATE CERTIFICATE Cert_Andonpc017_Trans

WITH SUBJECT = 'Cert_Andonpc017_Auth',

START_DATE = '02/15/2007',

EXPIRY_DATE = '02/15/2015'

GO

BACKUP CERTIFICATE Cert_Andonpc017_Trans To FILE = 'F:\Cert_Andonpc017_Trans'

CREATE ENDPOINT SBEndpointklient STATE = STARTED

AS TCP (LISTENER_PORT = 5723)

FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE Cert_Andonpc017_Trans)

--DROP USER andon

CREATE USER andon

--DROP CERTIFICATE Cert_ROBOTSRV

CREATE CERTIFICATE Cert_ROBOTSRV AUTHORIZATION andon

FROM FILE = 'C:\Cert_ROBOTSRV';

CREATE LOGIN sbLogin

FROM CERTIFICATE Cert_ROBOTSRV;

GO

GRANT CONNECT ON ENDPOINT::SBEndpointklient TO [public]

--Select * from sys.certificates

use KlientDB

create user andon

Grant SEND ON SERVICE::[Grafik_Service_Recive_Andonpc017] to [Public]

GRANT SEND ON SERVICE::[Grafik_Service_Recive_Andonpc017]

TO andon

GO

GRANT CONTROL ON SERVICE::[Grafik_Service_Recive_Andonpc017]

TO andon

GO

GRANT RECEIVE ON [Grafik_Queue]

TO andon

GO

--DROP Route Grafik_ROBOTSRV

CREATE Route Grafik_ROBOTSRV

WITH

SERVICE_NAME = 'Grafik_Service_Send_ROBOTSRV',

BROKER_INSTANCE = '2BA192F8-0BA3-4237-A156-21AFF7C65481',

ADDRESS = 'TCP://ROBOTSRV:5723'

--Dialog S?kerhet

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'andonANDON'

--Drop CERTIFICATE Cert_Andonpc017_Dialog

CREATE CERTIFICATE Cert_Andonpc017_Dialog

WITH SUBJECT = 'Cert_Andonpc017_Auth',

START_DATE = '02/15/2007',

EXPIRY_DATE = '02/15/2015'

GO

BACKUP CERTIFICATE Cert_Andonpc017_Dialog To FILE = 'F:\Cert_Andonpc017_Dialog'

--Drop CERTIFICATE Cert_ROBOTSRV_Dialog

CREATE CERTIFICATE Cert_ROBOTSRV_Dialog AUTHORIZATION andon

FROM FILE = 'C:\Cert_ROBOTSRV_Dialog';

The Security Audit:Audit Broker Conversation event details will contain the Issuer Name and Serial Number of the certificate not found (I think they are the UserName and RemoteLogin columns). You can then look up sys.certificates on the sender side to make sure the sender is using the certificates you expect.

HTH,
~ Remus

|||

Ok thanks.

I will look if I can find what′s wrong.

I got it to work when I turned of the dialog security by deleting RSB.