SSMS 20 can't show conflict viewer

Nebojsa Ilic 35

Hi!

Clicking on View conflicts in Object Explorer open Connect to Server dialog form instead of Conflict Viewer. If I fill in form to connect to the same server, it creates very strange error:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) (Framework Microsoft SqlClient Data Provider)

Regards,

Nebojsa

Lloyd @ Binderholz 0 Reputation points

2024-04-10T08:18:55.8233333+00:00

Here is a screenshot to perhaps help clarify what steps are taken that lead to the error.
Nebojsa Ilic 35 Reputation points

2024-04-10T08:24:55.3566667+00:00

Exactly like that! In addition, the same error is produced using any value for connection security.
Lloyd @ Binderholz 0 Reputation points

2024-04-10T10:26:09.8633333+00:00

And even setting additional connection parameters manually has no effect.
Nebojsa Ilic 35 Reputation points

2024-04-10T11:35:31.8133333+00:00

Yes, I can confirm that additional connection parameters can't solve the problem.
Erland Sommarskog 102.3K Reputation points

2024-04-10T19:57:58.2633333+00:00

Lloyd, what OS are you using? Nebosja's OS seems to be a bit mouldy.
Nebojsa Ilic 35 Reputation points

2024-04-11T05:57:29.69+00:00

You are right, 2012 is very old. But what make me confused is following:

Nothing has changed on server.

SSMS 19.3 works fine on desktop.

SSMS 20 produces error on the same desktop.
Lloyd @ Binderholz 0 Reputation points

2024-04-11T06:16:18.31+00:00

I am using relatively current software. SSMS 20.1.10.0 running under Windows 10 22H2 and connecting to SQL Server 2022 running on Windows Server 2022.

My hunch is that the problem lays within SSMS as it honors connection settings when initially connecting to the SQL server, but then seemingly ignores or uses other connection settings when launching the replication conflict resolver.
LucyChenMSFT-4874 1,355 Reputation points

2024-04-11T06:26:51.5133333+00:00

Hi,

The values accepted are yes/no, if true/false doesn't seem to work. The default value is "no", which means that the server certificate will be validated.

Hope it helps.

Regards
Lloyd @ Binderholz 0 Reputation points

2024-04-11T06:34:43.4833333+00:00

Tried that in different combinations. It makes no difference and the problem remains.

Encrypt=no;TrustServerCertificate=true;
Encrypt=yes;TrustServerCertificate=true;
Encrypt=yes;TrustServerCertificate=yes;
Encrypt=no;TrustServerCertificate=yes;
Encrypt=no;
Nebojsa Ilic 35 Reputation points

2024-04-11T06:46:10.1366667+00:00

I can't find combination that works for us too.

I also found the same connection problem when I edit job step which is type of SSIS Package and package is on different server.
LucyChenMSFT-4874 1,355 Reputation points

2024-04-11T06:52:24.2666667+00:00

Hi @Nebojsa Ilic,

Have you seen errorlog and other messages? Link that you sent is not working for us because server is not configured to use certificate.

Just like what Erland said:

SSMS 20 changed the default for encryption, so that encryption is mandatory.

And I said in my first answer:

there are breaking changes in EF Core 7.0. SqlClient connection strings use Encrypt=False by default. This means that: The server must be configured with a valid ceritificate.

Maybe this can answer your confused.

Regards
Erland Sommarskog 102.3K Reputation points

2024-04-11T06:57:57.37+00:00

SSMS 20.1 was released yesterday. There is nothing in the Release Notes that mentions the Conflict Viewer, so the issue may not have been fixed. But I would still suggest that you give it a try.
Nebojsa Ilic 35 Reputation points

2024-04-11T07:01:15.03+00:00

Hi @LucyChenMSFT-4874

There is nothing in server errorlog. As I said, it is very difficult to do any server changes. We do not have full control over old applications. Can I configure server with certificate without any changes on application side?
Lloyd @ Binderholz 0 Reputation points

2024-04-11T07:04:50.1+00:00
The workaround for the EF Core changes mentioned in the article are:

There are three ways to proceed:

Install a valid certificate on the server. Note that this is an involved process and requires obtaining a certificate and ensuring it is signed by an authority trusted by the client.

If the server has a certificate, but it is not trusted by the client, then TrustServerCertificate=True to allow bypassing the normal trust mechanims.

Explicitly add Encrypt=False to the connection string.

Unfortunately option 1 is not an option in my case and options 2 and 3 do not work!

Nebojsa Ilic 35

Hi @Erland Sommarskog

Thanks for info about new version. It is helpful!

As mentioned before, I found problem with SSIS Job step. And in release notes you can find this:

SSIS	When creating or modifying an SSIS job step in a SQL Agent job, you receive the error "A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) (Framework Microsoft SqlClient Data Provider)" regardless of whether Optional or Mandatory is selected for the Encryption property.	Use SSMS 19.3 to create or modify SSIS job steps.
SSIS	When creating or modifying an SSIS job step in a SQL Agent job, you receive the error "A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) (Framework Microsoft SqlClient Data Provider)" regardless of whether Optional or Mandatory is selected for the Encryption property.	Use SSMS 19.3 to create or modify SSIS job steps.

I think this is the same problem as we have with conflict viewer!

LucyChenMSFT-4874 1,355 Reputation points

2024-04-11T07:24:48.7366667+00:00

Wow, good. Glad to hear you resolve the confused. :)
Nebojsa Ilic 35 Reputation points

2024-04-11T07:43:47.4066667+00:00

The same with 20.1. Hope Microsoft will find solution so we can uninstall old SSMS.
LucyChenMSFT-4874 1,355 Reputation points

2024-04-16T09:44:15.14+00:00

Hi @Nebojsa Ilic,

Is this issue solved now? If you have any questions, please feel free to share with us.

And don't forget to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thanks for your understanding!
Nebojsa Ilic 35 Reputation points

2024-04-16T10:38:59.3033333+00:00

Hi @LucyChenMSFT-4874

As we can see there are lot of issues where Microsoft recommend using SSMS 19.3. I think that this problem belongs to the same group.

Thanks for your effort and @Erland Sommarskog too!

Accepted answer

LucyChenMSFT-4874 1,355 Reputation points

2024-04-08T05:25:40.07+00:00
Hi @Nebojsa Ilic,

Thanks for your information. Please check out the article, there are breaking changes in EF Core 7.0. SqlClient connection strings use Encrypt=False by default. This means that:

The server must be configured with a valid ceritificate.

The client must trust this certificate.

A SqlException will be thrown if these conditions are not met:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)

There are three ways to proceed:

Install a valid certificate on the server. Hope this article can help you well.

If the server has a certificate, but it is not trusted by the client, then TrustServerCertificate = True to allow bypassing the normal trust mechanism.

Explicitly add Encrypt=False to the connection string.

Best regards,

Lucy Chen

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

https://docs.microsoft.com/en-us/answers/support/email-notifications
Please sign in to rate this answer.
Nebojsa Ilic 35 Reputation points

2024-04-08T06:33:32.0866667+00:00

Hi,

I am talking about production server. Changing configuration is not an option for the moment. I tried to do a change in the connection properties (Option 3 - Encrypt=False) in SSMS but it doesn't work. Actually, I successfully connect object explorer to the server but try to open conflict viewer (Replication\Local Publication\View Conflicts) opens "Connect to server" dialog. Even if I put the same connection properties it produces error I mentioned before.

Nebojsa Ilic 35 Reputation points

2024-04-08T06:50:42.8466667+00:00

Hi @Lucy Chen 陳巧盈 (lucychen)

It is connection to the production server so any change on the server is not an option for the moment. I already tried with Option 3 to force encryption to false, but the same problem exists.

Fact is that I can connect with object explorer, I can use almost all functionalities from SSMS but starting conflict viewers produces error (Replication\Local Publication\View Conflicts). There is no connection property option that work for me.

Any other idea?

LucyChenMSFT-4874 1,355 Reputation points

2024-04-08T07:23:25.54+00:00

Hi @Nebojsa Ilic,

Could you please provide us the error log in SSMS? I need detail information to narrow down the issue. https://learn.microsoft.com/en-us/sql/relational-databases/performance/view-the-sql-server-error-log-sql-server-management-studio?view=sql-server-ver16. Thank you!

Nebojsa Ilic 35 Reputation points

2024-04-08T09:17:34.7066667+00:00

Hi @LucyChenMSFT-4874

Error log on that server has no any error. Do you want me to send you sql error log file from the server to see server configuration or you need client error details from SSMS that I'm getting calling viewer?

LucyChenMSFT-4874 1,355 Reputation points

2024-04-08T09:25:37.09+00:00

Yeah, please upload the logs here. Thank you!

In addition, here is a known issue, you can try the steps in this article, hope it can help you well. https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/connect/error-message-when-you-connect

Nebojsa Ilic 35 Reputation points

2024-04-08T10:48:33.6033333+00:00

Hi @LucyChenMSFT-4874

Please find Errorlog file attached. I had to remove all entries specific for our environment. In addition, error produced on server is:

Network error code 0x2746 occurred while establishing a connection; the connection has been closed.

This may have been caused by client or server login timeout expiration.

Time spent during login: total 2083 ms, enqueued 0 ms, network writes 0 ms, network reads 0 ms, establishing SSL 2083 ms, network reads during SSL 2062 ms, network writes during SSL 0 ms, secure calls during SSL 20 ms, enqueued during SSL 0 ms, negotiating SSPI 0 ms, network reads during SSPI 0 ms, network writes during SSPI 0 ms, secure calls during SSPI 0 ms, enqueued during SSPI 0 ms, validating login 0 ms, including user-defined login processing 0 ms. [CLIENT: xx.xx.xx.xxx]

Client is receiving following error (details):

===================================

Could not show Conflict Viewer.

For help, click: https://go.microsoft.com:80/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=20.0.70.0&EvtSrc=Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.Replication.ReplicationMenuItem&EvtID=CantShowConflictViewer&LinkId=20476

===================================

Exception has been thrown by the target of an invocation. (mscorlib)

Program Location:

at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)

at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)

at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)

at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.Replication.ReplicationMenuItem.OnCreateAndShowConflictViewer(IServiceProvider sp, XmlDocument doc)

===================================

A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) (Framework Microsoft SqlClient Data Provider)

For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver--2146893019-database-engine-error

Server Name: xxx.xxx.xxx.xxx

Error Number: -2146893019

Severity: 20

State: 0

Program Location:

at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

at Microsoft.Data.SqlClient.TdsParserStateObject.SNIWritePacket(SNIHandle handle, SNIPacket packet, UInt32& sniError, Boolean canAccumulate, Boolean callerHasConnectionLock, Boolean asyncClose)

at Microsoft.Data.SqlClient.TdsParserStateObject.WriteSni(Boolean canAccumulate)

at Microsoft.Data.SqlClient.TdsParserStateObject.WritePacket(Byte flushMode, Boolean canAccumulate)

at Microsoft.Data.SqlClient.TdsParser.TdsLogin(SqlLogin rec, FeatureExtension requestedFeatures, SessionData recoverySessionData, FederatedAuthenticationFeatureExtensionData fedAuthFeatureExtensionData, SqlClientOriginalNetworkAddressInfo originalNetworkAddressInfo, SqlConnectionEncryptOption encrypt)

at Microsoft.Data.SqlClient.SqlInternalConnectionTds.Login(ServerInfo server, TimeoutTimer timeout, String newPassword, SecureString newSecurePassword, SqlConnectionEncryptOption encrypt)

at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover, Boolean isFirstTransparentAttempt, Boolean disableTnir)

at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)

at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)

at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, ServerCertificateValidationCallback serverCallback, ClientCertificateRetrievalCallback clientCallback, DbConnectionPool pool, String accessToken, SqlClientOriginalNetworkAddressInfo originalNetworkAddressInfo, Boolean applyTransientFaultHandling)

at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)

at Microsoft.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)

at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)

at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)

at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)

at Microsoft.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)

at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides)

at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides)

at Microsoft.Data.SqlClient.SqlConnection.Open()

at Microsoft.SqlServer.Management.UI.ConflictViewer.PublicationForm.InitForm(String servername, Boolean ntAuth, String username, String password, ViewerForm viewform)

at Microsoft.SqlServer.Management.UI.ConflictViewer.PublicationForm.InitFormFromOE(SqlConnectionInfo sqlConn, ViewerForm viewform)

===================================

The certificate chain was issued by an authority that is not trusted

Nebojsa Ilic 35 Reputation points

2024-04-08T10:49:34.54+00:00

ERRORLOG.txt

Nebojsa Ilic 35 Reputation points

2024-04-08T10:50:48.6633333+00:00

My answer was deleted. Please let me know if you can open errorlog file

Nebojsa Ilic 35 Reputation points

2024-04-08T10:56:03.2733333+00:00

In addition, I have extended event setup on that server to collect failed queries and it shows following error:

Network error code 0x2746 occurred while establishing a connection; the connection has been closed.

This may have been caused by client or server login timeout expiration.

Time spent during login: total 2083 ms, enqueued 0 ms, network writes 0 ms, network reads 0 ms, establishing SSL 2083 ms, network reads during SSL 2062 ms, network writes during SSL 0 ms, secure calls during SSL 20 ms, enqueued during SSL 0 ms, negotiating SSPI 0 ms, network reads during SSPI 0 ms, network writes during SSPI 0 ms, secure calls during SSPI 0 ms, enqueued during SSPI 0 ms, validating login 0 ms, including user-defined login processing 0 ms. [CLIENT: xx.xx.xx.xxx]

Nebojsa Ilic 35 Reputation points

2024-04-08T12:43:59.3333333+00:00

Hi @LucyChenMSFT-4874

Have you seen errorlog and other messages? Link that you sent is not working for us because server is not configured to use certificate.

Erland Sommarskog 102.3K Reputation points

2024-04-08T17:02:30.48+00:00

My answer was deleted. Please let me know if you can open errorlog file

As a moderator, I have restored your deleted message. I've also converted your Answers to Comments. You should only use Answers to post, well, answer. So if you found the solution yourself, you post an Answer, else not.

LucyChenMSFT-4874 1,355 Reputation points

2024-04-09T02:46:20.8066667+00:00

Hi @Nebojsa Ilic,

Thanks for your information! I can see the errorlog and other messages. Please don't worry. I will try my best to help you.

Nebojsa Ilic 35 Reputation points

2024-04-09T05:30:03.33+00:00

Hi @Erland Sommarskog

I was using answer instead of comment by mistake. Sorry
Sign in to comment

3 additional answers

Erland Sommarskog 102.3K Reputation points

2024-04-05T21:01:18.9166667+00:00

SSMS 20 changed the default for encryption, so that encryption is mandatory. In the best of worlds, this means that SQL Server should use a "real" certificate for encryption of the traffic, and this certificate should be installed in the Trusted Certificate store on the client. But in less sensitive environments you can let it suffice with checking "Trust server certificate". In the new connection dialog in SSMS, this option is one the first page.

Or, at least, the normal dialog you get when you open a query window or Object Explorer. If you don't get this window when you open the Conflict Viewer, this seems like an oversight. (I'm not sure, but Conflict Viewer relates to Replication right? I don't have any replication set up, so that I can try it myself.) But in that case, you should find Trust Server Certificate on the second page.

Can you show a screenshot of the dialog you get?
Please sign in to rate this answer.

0 comments No comments
Sign in to comment
LucyChenMSFT-4874 1,355 Reputation points

2024-04-09T06:36:15.8633333+00:00
Hi @Nebojsa Ilic,

Thanks for your information. This error message '0x2746' indicates that there is a network error, an existing connection was forcibly closed by the remote host.

From the error ('0x2746'), it looks like something local on the server attempting to connect address being hit by a firewall. Examine any firewall or security software settings that might be blocking or interrupting the TCP connection. Ensure that the necessary ports are open and that SQL Server is allowed to communicate through the firewall.

In addition, note that you are using Windows Server 2012 R2, it looks like changing the cipher suite on Windows Server 2012 R2 solves the issue, please check this article. However, I find this article, this issue has been fixed in the latest Windows 10 and Windows Server 2016 updates.

The error '0x2746' can be a challenging issue to resolve, but with a systematic approach to troubleshooting, it can be managed effectively. Please feel free to share your issue here.

Regards,

Lucy Chen

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

https://docs.microsoft.com/en-us/answers/support/email-notifications
Please sign in to rate this answer.
Nebojsa Ilic 35 Reputation points

2024-04-09T08:15:10.3066667+00:00

Hi @LucyChenMSFT-4874

Thank you for your effort. Yes, our server is 2012 and it is difficult to find the root of the problem. Hope we will upgrade it soon. But until then I should maintain this server with minimal changes in configuration to reduce risk.

I still can use SSMS 19.3 because it works fine providing me all functionalities. SSMS 20 I will use only for newer servers on which I have full control.

LucyChenMSFT-4874 1,355 Reputation points

2024-04-09T08:40:28.41+00:00

Hi @Nebojsa Ilic,

Thanks for your information. I didn't know you cannot upgrade your server easily before. How about this method?

it looks like changing the cipher suite on Windows Server 2012 R2 solves the issue, please check this article.

In addition, hope the following preventive measures can help you minimize the chances of encountering the error in SSMS20:

Regular Updates: Keep your SQL Server and ODBC drivers up to date with the latest patches and versions.

Consistent Configuration: Standardize TLS settings across all servers and clients to ensure compatibility.

Network Monitoring: Implement network monitoring tools to detect and address connectivity issues promptly.

Security Best Practices: Follow security best practices for configuring firewalls and other network security measures.

Best regards

Lucy Chen

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

https://docs.microsoft.com/en-us/answers/support/email-notifications
Sign in to comment
Erin Stellato 486 Reputation points Microsoft Employee

2024-05-10T19:15:23.4166667+00:00

Hey all-

It looks like this is the same issue logged here: https://feedback.azure.com/d365community/idea/55c6a655-3aec-ee11-a73d-000d3adc65a4

Can you confirm? Please tag me in any reply so I can track this properly. Thank you,

Erin
Please sign in to rate this answer.
Lloyd @ Binderholz 0 Reputation points

2024-05-11T13:37:55.7266667+00:00

@Erin Stellato yes, that issue covers this one. The accepted answer in this thread "Install a valid certificate on the server" alone is not an acceptable solution. The suggested "TrustServerCertificate" and "Encrypt=False" solutions do not work. Looking forward to a fix for this issue. Many thanks!

Nebojsa Ilic 35 Reputation points

2024-05-13T07:49:43.6633333+00:00

@Erin Stellato I can confirm too!

Erin Stellato 486 Reputation points Microsoft Employee

2024-05-13T15:28:14.19+00:00

Lloyd @ Binderholz and Nebojsa Ilic thank you for confirming. We don't have a solution at this time, but are working with the replication internally. I will likely update the issue on the feedback site when there's news to share, but will try to remember to post here also. Thanks again for following up.
Sign in to comment

Share via

SSMS 20 can't show conflict viewer

3 additional answers