Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am setting up Service Broker communication between 2 servers, A and B we will call them. I used this article as an example:

http://blogs.msdn.com/b/sql_service_broker/archive/2008/08/26/securing-a-dialog-with-certificates.aspx?Redirected=true

A is SQL 2005, B is SQL 2012. It doesn’t work, and I have not been able to track down a good reason why. What I can see is:

The Profiler on the receiving end doesn’t even show any Service Broker events, indicating nothing is getting to it.

But using ssbdiagnose as below, it says congratulations, you have no errors:

C:\Users\me>ssbdiagnose -level info -E configuration from service ServerAServiceTest -S ServerA -d MyDB to Service ServerBServiceTest -S ServerB -d MyDB on Contract myContract

Microsoft SQL Server 11.0.2100.60
Service Broker Diagnostic Utility
0 Errors, 0 Warnings

Also in the transmission queue, the only thing I see that seems particularly off is ‘to_broker_instance’ is null, though I explicitly specified that info when setting up the route.

Further, no errors are showing up in transmission_status.

Also, SQL Server error logs are shedding no light.

As for firewall issues, well, these are test servers not accessible from the outside so I tried turning the firewalls off altogether.

One thing that is bothersome, I will get these:

An exception occurred while enqueueing a message in the target queue. Error: 15581 State: 7. Please create a master key in the database or open the master key in the session before performing this operation.

I do open the key and these go away. But I shouldn't have to repeatedly open the key everytime I want to do something, should I? I suspect this is part of the problem even though as mentioned the errors go away.

Sorry for the somewhat open question - even some help identifying where to get more informative errors or debugging info would be great. This is new territory for me.

share|improve this question

migrated from stackoverflow.com Apr 13 '13 at 0:20

This question came from our site for professional and enthusiast programmers.

1 Answer 1

up vote 1 down vote accepted

Is the database master key encrypted by the service master key? If not, the db engine has no way to automatically open it. To check, take a look in sys.asymmetric_keys, specifically the pvt_key_encryption_type_desc column. If you find that it's not, you can make it so by doing

alter master key add encryption by service master key
share|improve this answer
    
Thanks for the response. I have looked, and as I may have said(?) we have things working 2005<->2005, but in this 2005<->2012 case, no luck. Anyhow, the column in question (2012 server, master database) has the value 'NO_PRIVATE_KEY' same story after running the above. I also notice there are no rows in the table for either the database where SB is configured, or anywhere on the 2012 box. So this is an extra step with 2012 perhaps? –  sdcharle Apr 15 '13 at 15:08
    
Could be, though that would be strange to me that it would work in the homogeneous situation and not the heterogeneous. If it were me, I'd give it a shot. It's really unlikely to break anything and if it doesn't fix the problem, you can do alter master key drop encryption by service master key –  Ben Thul Apr 15 '13 at 18:33
    
Hi Ben, thanks again. As it turns out I ended up going with the patented 'blow everything away and start over' approach, and now they're talking. That is some good information to keep in mind in the future. –  sdcharle Apr 29 '13 at 21:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.