I'm trying to set transactional replication between 2 SQL Server 2012 named instances. I've followed a step by step manual and created a Publication. But once I look into Replication Monitor I Get the following error:

2013-12-05 13:08:23.29 Copyright (c) 2008 Microsoft Corporation.
2013-12-05 13:08:23.29 The timestamps prepended to the output lines are expressed in terms of UTC time.
2013-12-05 13:08:23.29 User-specified agent parameter values:
2013-12-05 13:08:23.29 --------------------------------------
2013-12-05 13:08:23.29 -Publisher SRV1\SQL2012
2013-12-05 13:08:23.29 -PublisherDB DB_PUB
2013-12-05 13:08:23.29 -Publication DB_Pub
2013-12-05 13:08:23.29 -Distributor SRV1\SQL2012
2013-12-05 13:08:23.29 -DistributorSecurityMode 1
2013-12-05 13:08:23.29 -XJOBID 0xE8D827CC232A234FAC049095C03EBA66
2013-12-05 13:08:23.29 --------------------------------------
2013-12-05 13:08:23.29 Connecting to Distributor 'SRV1\SQL2012'
2013-12-05 13:08:23.35 The replication agent had encountered an exception.
2013-12-05 13:08:23.35 Source: Replication
2013-12-05 13:08:23.35 Exception Type: Microsoft.SqlServer.Replication.ConnectionFailureException
2013-12-05 13:08:23.35 Exception Message: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)

I wonder what may cause this type of error?


It sounds like you may have set Force Encryption on the instance. In SQL Server Configuration Manager, you can see this setting (server-side encrypting enforcement) by expanding SQL Server Network Configuration and then right-clicking on Protocols for YOURINSTANCENAME, and selecting Properties. You will see the option there to toggle Force Encryption, as well as a tab for Certificate config.

enter image description here

I am by no means saying to disable forced encryption (if that is indeed your problem, I'm guessing somebody set that for a reason), but you will need to ensure that the communication between those two is properly configure. Here is a good KB article that seems to explain what you're running into.

  • As my issue wan't related to encryption I've posted another asnwer, but thanks to your reply. I changes settings as you proposed, still no effect.
    – Johnny_D
    Dec 6 '13 at 8:57

I assume this might help somebody, as I've found what caused my problem. In our local company active directory controller was using self signed certificates, I'm not faminial with details of AD, but I've found too many errors in Server's event viewer manager.

So once I've stopped using Windows Authentication for operations in SQL Server Management Studio for creation of replication, and used SQL credentials with SA level - error disappeared. Hope this will help somebody.

  • an alternative would have been to import the root CA your AD was using as a trusted CA on the box? Dec 8 '13 at 12:39

Your Answer

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

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