service broker message on remote instance not working
I was trying to send a message on a different instance in the network using service broker. I have created the endpoint and route both the side. I could see that the messages are in the transmission_queue in the sender side with no error in the status.
What could be the reason.
I am doing the following:
CREATE ROUTE inst02
WITH
SERVICE_NAME = 'SERVICE2',
ADDRESS = 'tcp://10.14.43.149:2341'
in the send script i am using this:
BEGIN DIALOG CONVERSATION @dialog_handle
FROM SERVICE [SERVICE1]
TO SERVICE 'SERVICE2'
ON CONTRACT [MainContract] WITH ENCRYPTION = OFF ;
in the sending side if i change the endpoint's authentication as windows kerberos , then i get thefollowing error in the transmission queue:
"Connection handshake failed. An OS call failed: (8009030e) 0x8009030e(No credentials are available in the security package). State 66."
I also have given access to the remote user on this endpoint(on the recever side) using this :
GRANT CONNECT ON ENDPOINT::Endpoint_test to paras
I am executing the sending side send script using the same user wich has access to the remote endpoint.
Can some one resolve this issue.
Thanks
[1457 byte] By [
pranka] at [2008-2-23]
 pranka wrote: |
| If i change the endpoint's authentication as windows kerberos , then i get thefollowing error in the transmission queue: "Connection handshake failed. An OS call failed: (8009030e) 0x8009030e(No credentials are available in the security package). State 66." |
|
For troubleshooting Kerberos errors, I highly recommend this guide at http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerberr.mspx One thing required for Windows Kerberos authentication is to register with the Active Directory the Service Principal Names involved. Use a tool like setspn.exe (available at http://www.microsoft.com/downloads/details.aspx?FamilyID=5fd831fd-ab77-46a3-9cfe-ff01d29e5c46&DisplayLang=en) to register the SPNs. The SPNs used by Service Broker ar of form MSSQLSvc/HostName:Port. The <HostName> and <Port> part are deduced from the address used in the route.
Personally, I find certificate based authentication easier to implement, as described here: http://msdn2.microsoft.com/en-us/library/ms166077.aspx
| pranka wrote: |
| I was trying to send a message on a different instance in the network using service broker. I have created the endpoint and route both the side. I could see that the messages are in the transmission_queue in the sender side with no error in the status. |
|
Try following the troubleshooting steps from http://blogs.msdn.com/remusrusanu/archive/2005/12/20/506221.aspx . Post back here if the steps don't get you to the actual cause of the problem.
In the case you describe, the next step would be to look at profiler events on the target machine. See the mentioned blog article for details.
HTH,
~ Remus
Hi ,
Thanks for your reply. I tried the troubleshooting steps by running the profiler on the target machine.
I got the trace for Broker:Connection event which says "An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)' "
I did not get any other trace.
I understood that this error is caused by the security reasons. I also came to know that for windows authentication to work both the machines should be in same domain. is that right?
I tried the approach of certificate based security but still had some problmes.
I did the following things:
use master
--drop master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'paras123#'
BACKUP MASTER KEY TO FILE = 'c:\Certificates\pk.pvk'
ENCRYPTION BY PASSWORD = 'paras123#'
--drop certificate pranka
CREATE CERTIFICATE pranka
WITH SUBJECT = 'pranka Certificate',
EXPIRY_DATE = '10/31/2009' ;
BACKUP CERTIFICATE pranka
TO FILE = 'C:\Certificates\pranka.cer' ;
now i saved these two files (pranka.cer and pk.pvk) on the target machine and tried the following thing on the remote machine:
use master
create login paras with password = 'paras123#'
create user paras for login paras
create certificate pranka
AUTHORIZATION paras
FROM FILE='C:\Certificates\pranka.cer'
WITH PRIVATE KEY (FILE = 'C:\Certificates\pk.pvk')
GO
while creting the certificate,I always get the error Msg 15208 The certificate, asymmetric key, or private key file does not exist or has invalid format.
so i am unable to create the certificate.
my next step would be
GRANT CONNECT ON ENDPOINT::Endpoint_test to paras
Then in the sending side if i execute the script as user 'Paras' then it should work right?
Please correct me.
Thanks
| pranka wrote: |
| I got the trace for Broker:Connection event which says "An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)' " |
|
You need to profile both machines involved. When this event occurs, the peer machine should trace a corresponding Broker:Connection event with an explanation why the connection was closed.
| pranka wrote: |
| I understood that this error is caused by the security reasons. I also came to know that for windows authentication to work both the machines should be in same domain. is that right? |
|
Is not required for the two machines to be in the same domain, but the domains must have a trust relationship. Basically, the Windows accounts running the SQL Server instance must be recognized by each other.
| pranka wrote: |
| CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'paras123#' BACKUP MASTER KEY TO FILE = 'c:\Certificates\pk.pvk'
ENCRYPTION BY PASSWORD = 'paras123#' BACKUP CERTIFICATE pranka
TO FILE = 'C:\Certificates\pranka.cer' ; ... create certificate pranka
AUTHORIZATION paras
FROM FILE='C:\Certificates\pranka.cer'
WITH PRIVATE KEY (FILE = 'C:\Certificates\pk.pvk') error Msg 15208 The certificate, asymmetric key, or private key file does not exist or has invalid format. |
|
You are backing up the database master key in the pk.pvk file, but try to restore it as a certificate private key. The correct way to do this is to use the WITH PRIVATE KEY clause of BACKUP CERTIFICATE:
BACKUP CERTIFICATE pranka
TO FILE = 'C:\Certificates\pranka.cer'
WITH PRIVATE KEY (
FILE = 'C:\Certificates\pk.pvk',
ENCRYPTION BY PASSWORD = 'paras123#');
But you never have to export the private keys when a certificates based authentication is used. The private key is really private, it should never be exported. Just export the .cer file and import only the .cer file:
create certificate pranka
AUTHORIZATION paras
FROM FILE='C:\Certificates\pranka.cer' ;
Both machines involved have to do this step (exchange certificates).
BTW, I forgot to mention the posibility of using these stored procedures from this post at http://rushi.desai.name/Blog/tabid/54/EntryID/6/Default.aspx.
HTH,
~ Remus
Ok, I have ran the profiler both the side and and get the following errors:
On the sender side the Broker:Message Undeliverable event says:
This message could not be delivered because the destination queue has been disabled. Queue ID: 5575058.
in the target side the same event says:
This message could not be delivered because the user with ID 0 in database ID 9 does not have permission to send to the service. Service name:
'SERVICE2'.
I am confused about this message as I am running the send script to both the side as the same user which i am authorising while loading the certificate.
here is is the script for endpoint and certificates:
use [master]
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'paras123#'
CREATE CERTIFICATE cerhsin01
WITH SUBJECT = 'hsin01 Certificate',
start_DATE = '01/01/2006' ;
BACKUP CERTIFICATE cerhsin01
TO FILE = 'C:\Certificates\cerhsin01pub.cer' ;
GO
CREATE ENDPOINT Endpoint_test
STATE = STARTED
AS TCP ( LISTENER_PORT = 2341 )
FOR SERVICE_BROKER ( AUTHENTICATION = certificate cerhsin01 ,encryption = required) ;
ALTER ENDPOINT Endpoint_test
FOR SERVICE_BROKER ( MESSAGE_FORWARDING = ENABLED,
MESSAGE_FORWARD_SIZE = 10 ) ;
GO
create login paras with password = 'paras123#'
create user paras for login paras
GRANT CONNECT ON ENDPOINT::Endpoint_test to paras
Now the smilar step i also use for creating the certificate and endpoint on the target side.
Later I copy the cerhsin01pub.cer on the target and copy the certificate from there to the sender.
After this I create the certificate on the sender side from the target server's back up file
--STEP 2
use master
CREATE CERTIFICATE cerhsin02Pub
AUTHORIZATION paras
FROM FILE='C:\Certificates\cerhsin02pub.cer'
-- WITH PRIVATE KEY (FILE = 'C:\Certificates\dlgyuk1.pvk')
GO
use localsb_db1
grant send on service::SERVICE1 to paras
the similar step2 i also repeat on the target using the back up file from sender side.
I am executing the send script using the login paras only.
I don't know what is wrong happening on the target server which does not allow to send on service2.
Let me know if you need more info.
Hello Remusu,
Thanks for your reply. I could finally get it worked by setting up the dialog security.I was under impression that since i don't want to use the encryption of the messages on the dialog, i don't need the dialog security or the remote service binding. so in the Begin DIALOG i was setting encryption = off. I thought this will be enough to send message without using dialog security.
So is it compulsory to have dialog security(certifcate based) for sending message on different instance?
sending the message on a different database on the same instance worked fine without any dialog security.
Thanks a lot again.
Dialog security is not required. What is required is for the initiator service to have SEND permission on the target service. You can achieve this either by setting up security and explicitly granting SEND to one user that will be authenticated through the certificate, or you could grant SEND permission to [Public]. Granting SEND to [Public] basically opens the service to any incomming message.
Within the same instance across databases the user can be authenticated (user->login->user in target database), if the initiator database is marked trustworthy. Since you probably owned both initiator and target service, the SEND permission was implicit.
Within the same database there is no need for trustworthy bit, the user can be directly authenticated and the SEND permission verified.
HTH,
~ Remus
