Help with Service Broker error message
I am getting this error in my sys.transmission_queue table whenever I attempt to send a SQL Service Broker message between two different SQL Server servers. (i.e. the databases are on two different physical machines)
Dialog security is unavailable for this conversation because there is no security certificate bound to the database principal (Id: 5). Either create a certificate for the principal, or specify ENCRYPTION = OFF when beginning the conversation
When this error refers to “database principal” what is it referring to? (the “master” database? dbo user?) I’ve used the CREATE CERTIFICATE command, backed up the certificate and created a same named certificate on the other server with the backup .cer file from the first server, but I keep getting this message.
Any help would be appreciated in getting me pointed in the right direction. I must be missing something obvious.
FYI, in my development environment, both the initiating and target databases were on the same physical server, and same SQL instance, and everything was working fine.
2 Solutions collect form web for “Help with Service Broker error message”
The error refers to a user in the database hosting your Service Broker service (
select name from sys.database_principals where principal_id = 5).
In addition to exporting a certificate to the target server, you also need to import target server’s certificate, associate it with a user and create a remote service binding to tell Service Broker which local user represents the remote service. The following 2 articles should help you: Service Broker Dialog Security and Securing a dialog with certificates.
In addition to what Pawel said, I’d also recommend to give the SSBDIAGNOSE tool a try. Use the CONFIGURATION option and it will tell you exactly what user needs certificates, in which database, as well as analyzing the endpoint connectivity, the routes and the permissions.