0

I have MS SQL Server 2019 where encrypted connections have been enabled. A certificate has been issued and installed on the server. In addition, I have also enabled Force Protocol Encryption in SQL Server Configuration Manager for 32-bit and 64-bit clients.

All clients can connect without a single issue.

Problems arise when I try to run a Maintenance Plan with Back Up Database Task.

It errors out with:

Could not load package "Maintenance Plans\MaintenancePlan" because of error 0xC0014062.
Description: The LoadFromSQLServer method has encountered OLE DB error code 0x80004005 (Client unable to establish connection).
The SQL statement that was issued has failed.

Source: Started: 0.00.00 Finished: 0.00.00 Elapsed: 0.109 seconds.
The package could not be loaded. The step failed.

In addition, Event Viewer has following error in it.

The certificate received from the remote server does not contain the expected name. It is therefore not possible to determine whether we are connecting to the correct server. The server name we were expecting is . The TLS connection request has failed. The attached data contains the server certificate.

As soon as I disable the aforementioned Force Protocol Encryption in SQL Server Configuration Manager for clients, the Maintenance Plan works. Or if I enable Trust Server Certificate in the same place, the Maintenance Plan works.

I am quite baffled by this issue, because all normal client connections do work even with Force Protocol Encryption enabled and Trust Server Certificate disabled.

Even SQL Server Management Studio works when run locally on the SQL Server, regardless of whether I have enabled Encrypt connection under Connection Properties.

Apparently this is not a common issue, or configuration, because Google search results found no similar cases to mine.

So my my question is, why is it only the Maintenance Plans that are unable to connect and how do I solve the problem without disabling Force Protocol Encryption, or enabling Trust Server Certificate?

Improve this question
6
  • Seems like a certificate issue... what does the certificate you used on your SQL (for SSL) looks like ? (what are the info it contains)
    – Dominique Boucher
    Aug 3, 2020 at 13:06
  • Probably redundant - would you mind making sure you enabled the force protocol encryption for both 32 and 64 bits client drivers? If you didn't, it's either the clients can connect because they're using the unencrypted driver, or maintenance plan is jumping between the two without using encryption.
    – GMassDBA
    Aug 3, 2020 at 13:33
  • @Dominique Boucher The certificate should be just fine because all clients can connect without issues. At the very least, I do not know what would cause only maintenance plan to fail if this was a certificate issue. What would I be looking for?
    – KeeperB5
    Aug 4, 2020 at 11:39
  • @GMassDBA Yes, both 32-bit and 64-bit clients were covered.
    – KeeperB5
    Aug 4, 2020 at 11:40
  • It seems that the maintenance plan connect to the instance using "." instead of the name (The server name we were expecting is .). I guess the CN in your certificate is your server name (long and short name). I wonder if turning on named pipe would fix your issue...
    – Dominique Boucher
    Aug 4, 2020 at 12:49

1 Answer 1

Reset to default
1

Updating with our solution in the event someone else comes across this post.

We had to make registry changes on these two fields:

  • HKLM\SOFTWARE\Microsoft\MSSQLSERVER\Client\SNI18.0\GeneralFlags\Flag1

  • HKLM\SOFTWARE\Wow6432Node\Microsoft\MSSQLSERVER\Client\SNI18.0\GeneralFlags\Flag1

Change the value from 1 to 0, then restart SQL Services.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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