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.

Certificate loading issue - when creating certificate from SQL Server to SQL Server Express on t

Hi, We are trying to implement Service Broker between SQL Server Express and SQL Server on the Same machine and we are having problems with certificates. We are creating a certificate on SQL Server, backing up the certificate on a file system and then loading certificate on the SQL Server Express from the file and we are keep getting the following error: Msg 15208, Level 16, State 1, Line 1 The certificate, asymmetric key, or private key file does not exist or has invalid format.

Following script runs fine on SQL Server.

Code Snippet

use master

Create Master Key Encryption BY Password = '45Gme*3^&fwu';

BACKUP MASTER KEY TO FILE = 'C:\ServiceBroker\PrivateKeyMasterB.pvk'

ENCRYPTION BY PASSWORD = '45Gme*3^&fwu'

Create Certificate EndPointCertificateC

WITH Subject = 'C.Server.Local',

START_DATE = '06/01/2006',

EXPIRY_DATE = '01/01/2008'

ACTIVE FOR BEGIN_DIALOG = ON;

BACKUP CERTIFICATE EndPointCertificateC

TO FILE = 'C:\ServiceBroker\EndPointCertificateC.cer'

Following script runs on SQL Server Express:

Code Snippet

Create Certificate EndPointCertificateC

From FILE = 'C:\ServiceBroker\EndPointCertificateC.cer'

WITH PRIVATE KEY (

FILE = 'C:\ServiceBroker\PrivateKeyMasterB.pvk',

DECRYPTION BY PASSWORD = '45Gme*3^&fwu'

);

If we run the script other way around, it works fine. If we use the SQL Server on some other machine, the script works fine. But only on the same machine, it throws this error. We made sure the permissions and everything. Let us know if there is any work around or what are we doing wrong.

Any help is appreciated. Thank you,

This must be a permisssion issue. The SQL Server Express service account does not have access to the .cer and/or .pvk file.|||

We did give permission to $SQLServerExpress to the folders where we generate the files. And all we are trying to perform this using sa account, I am not sure which other permission we are missing.

|||

Folder permissions are not enough. The files created (*.cer and *.pvk) will be ACL-ed by the creator instance to prevent any other account access, overwriting any permsision inherited from the folder permissions. So if for instance the SQL Express is running as NETWORK SERVICE, or as a (domain) user account, then it will not be able to see the files. You must explicitly grant read permissions on the two created files. Why this work on all other machines is probably because on all other machines the SQL Express and the Enterprise instance run as the same account, but not on this machine.

The account you log in as ('sa') has no relevance, all that matter here is the service account.

Certificate loading issue - when creating certificate from SQL Server to SQL Server Express on t

Hi, We are trying to implement Service Broker between SQL Server Express and SQL Server on the Same machine and we are having problems with certificates. We are creating a certificate on SQL Server, backing up the certificate on a file system and then loading certificate on the SQL Server Express from the file and we are keep getting the following error: Msg 15208, Level 16, State 1, Line 1 The certificate, asymmetric key, or private key file does not exist or has invalid format.

Following script runs fine on SQL Server.

Code Snippet

use master

Create Master Key Encryption BY Password ='45Gme*3^&fwu';

BACKUP MASTER KEYTOFILE='C:\ServiceBroker\PrivateKeyMasterB.pvk'

ENCRYPTION BY PASSWORD ='45Gme*3^&fwu'

CreateCertificate EndPointCertificateC

WITH Subject ='C.Server.Local',

START_DATE ='06/01/2006',

EXPIRY_DATE ='01/01/2008'

ACTIVE FOR BEGIN_DIALOG =ON;

BACKUPCERTIFICATE EndPointCertificateC

TOFILE='C:\ServiceBroker\EndPointCertificateC.cer'

Following script runs on SQL Server Express:

Code Snippet

CreateCertificate EndPointCertificateC

FromFILE='C:\ServiceBroker\EndPointCertificateC.cer'

WITH PRIVATE KEY(

FILE='C:\ServiceBroker\PrivateKeyMasterB.pvk',

DECRYPTION BY PASSWORD ='45Gme*3^&fwu'

);

If we run the script other way around, it works fine. If we use the SQL Server on some other machine, the script works fine. But only on the same machine, it throws this error. We made sure the permissions and everything. Let us know if there is any work around or what are we doing wrong.

Any help is appreciated. Thank you,

This must be a permisssion issue. The SQL Server Express service account does not have access to the .cer and/or .pvk file.|||

We did give permission to $SQLServerExpress to the folders where we generate the files. And all we are trying to perform this using sa account, I am not sure which other permission we are missing.

|||

Folder permissions are not enough. The files created (*.cer and *.pvk) will be ACL-ed by the creator instance to prevent any other account access, overwriting any permsision inherited from the folder permissions. So if for instance the SQL Express is running as NETWORK SERVICE, or as a (domain) user account, then it will not be able to see the files. You must explicitly grant read permissions on the two created files. Why this work on all other machines is probably because on all other machines the SQL Express and the Enterprise instance run as the same account, but not on this machine.

The account you log in as ('sa') has no relevance, all that matter here is the service account.