1

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 code listed below. 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. 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.

--initiator master db
USE master;
GO
CREATE LOGIN Login_SB_Source WITH PASSWORD = 'my_strong_pwd'
GO
CREATE USER User_SB_Source FOR LOGIN Login_SB_Source;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'my_strong_pwd'
GO
CREATE CERTIFICATE Certificate_SB_Source
WITH 
        SUBJECT = 'SB source',
    START_DATE = '11/01/2017', 
    EXPIRY_DATE = '01/01/2100' 
    ACTIVE FOR BEGIN_DIALOG = ON
GO
CREATE CERTIFICATE Certificate_SB_Target
    AUTHORIZATION [User_SB_Source]
    FROM FILE = 'C:\Certs\Certificate_SB_Target.cer'
GO
BACKUP CERTIFICATE Certificate_SB_Source 
    TO FILE = 'C:\Certs\Certificate_SB_Source.cer'
GO  
CREATE ENDPOINT SB_EndPoint
    STATE = STARTED
    AS TCP (LISTENER_PORT = 4022)
    FOR SERVICE_BROKER 
    (
        AUTHENTICATION = CERTIFICATE Certificate_SB_Source,
        ENCRYPTION = SUPPORTED
    )
GO
GRANT CONNECT ON ENDPOINT::SB_EndPoint TO Login_SB_Source

--target master db
USE master;
GO
CREATE LOGIN Login_SB_Target WITH PASSWORD = 'my_strong_pwd';
GO
CREATE USER User_SB_Target FOR LOGIN Login_SB_Target;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'my_strong_pwd'
GO
CREATE CERTIFICATE Certificate_SB_Target
WITH 
    SUBJECT = 'SB target',
    START_DATE = '11/01/2017', 
        EXPIRY_DATE = '01/01/2100' 
    ACTIVE FOR BEGIN_DIALOG = ON
GO
CREATE CERTIFICATE Certificate_SB_Source
    AUTHORIZATION User_SB_Target
    FROM FILE = 'C:\Certs\Certificate_SB_Source.cer'
GO
BACKUP CERTIFICATE Certificate_SB_Target
    TO FILE = 'C:\Certs\Certificate_SB_Target.cer'
GO  
CREATE ENDPOINT SB_EndPoint
    STATE = STARTED
    AS TCP (LISTENER_PORT = 4022)
    FOR SERVICE_BROKER 
    (
        AUTHENTICATION = CERTIFICATE Certificate_SB_Target,
        ENCRYPTION = SUPPORTED
    )
GO
GRANT CONNECT ON ENDPOINT::SB_EndPoint TO Login_SB_Target

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.