December 4, 2017 at 5:27 am
I'm trying to set up a test environment for Service Broker communication between 2 SQL Server 2016 SP1 instances.
I've created db master keys, certificates and endpoints in the master db for each instance, as in the attached scripts.
For now my goal is to synchronize data between 2 tables. On the initiator side, I'm inserting a row in a table. A "after insert" trigger sends data on conversation to the target instance.
Unfortunately the message remains in the sys.transmission_queue on initiator. The sys.transmission_queue.transmission_status column displays this message:
"Connection handshake failed. An OS call failed: (0) (null). State 53."
In the SQL Server error log and in the Windows Application event viewer there are a few messages:
Error: 9650, Severity: 16, State: 3.
A system cryptographic call failed during a Service Broker or Database Mirroring operation: system error '5(Access is denied.)'.
Error: 9641, Severity: 16, State: 1.
A cryptographic operation failed. This error indicates a serious problem with SQL Server. Check the SQL Server error log and the Windows event logs for further information.
Service Broker login attempt failed with error: 'Connection handshake failed. An OS call failed: (0) (null). State 53.'. [SERVER: <target ip number>]
I've followed the advice from this stackoverflow post, but the problem persists.
https://stackoverflow.com/questions/10839974/cryptographic-error-using-sql-server-service-broker
I've audited access to C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys, C:\ProgramData\Microsoft\Crypto\ and HKEY_LOCAL_MACHINE registry keys on the target side, but I couldn't "catch" any "access denied" event.
Can anyone of you help?
Initiator:
Windows 10 Enterprise version 1703 OS Build 15063.7026 (my dev physical machine)
SQL 2016 SP1 version 13.0.4206.0
TCP/IP protocol is enabled.
Target
Windows Server 2012 R2 Standard (virtual machine)
SQL 2016 SP1 version 13.0.4001.0
Ports 1433 and 4022 are open.
TCP/IP protocol is enabled.
December 8, 2017 at 11:18 am
When you run these scripts, do you run the entire SB_Target_master.txt on the target server and then run the entire SB_Source_master.txt or even in the reverse order?
The problem I am seeing is with the certificate creation scripts.
Lets say you run the Target script first then the Source script:
You are dropping objects to make it a clean setup (this looks OK).
You create the certificate Certificate_SB_Target (this looks OK).
You create the certificate Certificate_SB_Source from file (this is the red flag).
you backup the certificate Certificate_SB_Target (this looks OK).
at this point I stopped reading as that looked a bit off and I went to the other script and you do the same thing. So when you backup the certificate in SB_Source_master.txt, you would be overriting the certificate you created from file in SB_Target_master.txt. So, if the scripts you provided are the scripts you run, your certificate for one of the 2 would be bad.
I would recommend picking one of the 2 scripts (whichever one you run FIRST) and changing your order of things to be:
1 - drop objects
2 - create certificate (just the 1)
3 - backup the certificate
Then your second script should go happily along.
Then have a 3rd script that restores the backup of the certificate from the second script.
But the scripts are missing a lot of objects that are required for service broker such as queues, routes, message types, contracts, services and remote service bindings... but I am assuming that you are just providing a small subset of your setup scripts, no?
If that isn't the case, and you are creating things like how I proposed above, are you granting SEND on the service to the user?
Could you post your entire service broker setup scripts? Preferrably with the order you run them in and an indication of which instance and database you are running these against? I personally would NOT recommend running them against master, but to have a USER database for running these against. That could also be part of the problem you are having.
Microsoft has a tutorial on how to set things up at https://technet.microsoft.com/en-us/library/bb839492(v=sql.105).aspx. If you have not set up service broker before, I would recommend reading that. It helped me a lot with my initial setup of service broker. I tweaked things from their scripts to suit my needs and in the end, for my setup, I needed to build a total of 26 scripts, but a lot of those were to test if messages could go from server A to server B, then if they could go from server B to server A, then B to C, then C to B, then A to C though B and finally C to A through B. Not counting my test scripts, for a 3 instance setup, I have 11 scripts for building all of the required objects to ensure they get built in the correct order on each server. Some of these could be reduced as some were setup->test->setup on same server, but I wanted to test things at each phase of deployment.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
December 12, 2022 at 4:27 pm
This was removed by the editor as SPAM
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply