How to resolve “Encryption mismatch” between Excel and legacy SQL Server versions.

The error “Encryption mismatch” (frequently appearing as “A connection was successfully established with the server, but then an error occurred during the pre-login handshake” or “The client and server cannot communicate, because they do not possess a common algorithm”) occurs when Excel attempts to establish an encrypted session with an older SQL Server instance, but the client computer and the database server cannot agree on a mutual Transport Layer Security (TLS) protocol or cryptographic cipher suite.

Quick Answer

To resolve an encryption mismatch between modern Excel and legacy SQL Server versions, set Encrypt=Optional; (or Encrypt=False;) and TrustServerCertificate=True; in your Excel connection string. To permanently fix the underlying security break, install the official Microsoft TLS 1.2 cumulative update patch on the legacy SQL Server host.

---

Why Modern Excel Breaks on Legacy SQL Servers

Windows 10, Windows 11, and modern Microsoft 365 Office updates have disabled outdated security protocols—specifically SSL 3.0, TLS 1.0, and TLS 1.1—at the operating system level via the Windows Schannel security provider. Furthermore, modern SQL client drivers (such as ODBC Driver 18 for SQL Server and Microsoft OLE DB Driver 19 for SQL Server) require encryption by default.

In contrast, unpatched legacy database servers (such as SQL Server 2008, 2008 R2, 2012, and early builds of 2014) were designed when TLS 1.0 was standard. Out of the box, these older SQL engines cannot perform a TLS 1.2 handshake.

When 64-bit Excel opens a connection:

1. Excel sends a Client Hello requesting an encrypted TLS 1.2 or TLS 1.3 handshake.
2. The legacy SQL Server responds that it only supports TLS 1.0.
3. Windows Schannel on your client desktop immediately terminates the connection with a pre-login handshake failure.

To review general connection failure categories across Excel, consult Troubleshooting SQL Connection Failures: Fixing ODBC and Provider Errors.

---

Step-by-Step Solutions

Method 1: Modify the Excel Connection String (Client-Side Workaround)

If you do not have administrative access to patch the database server, adjust your Excel workbook connection settings so the driver does not enforce strict encryption.

In Power Query:

1. In Excel, go to Data > Get Data > Data Source Settings.
2. Select your SQL Server connection and click Edit Permissions….
3. Under the Credentials settings, locate the encryption toggle.
4. Uncheck Encrypt connection, or set encryption to Optional.
5. Save and refresh the query.

In Workbook Connection Strings / VBA:

If connecting via an ODC file, Data Connection Wizard, or VBA ADODB:

1. Open Data > Queries & Connections > Properties > Definition.
2. In the Connection String box, locate the Encrypt and TrustServerCertificate arguments.
3. Change or append:
   Encrypt=Optional;TrustServerCertificate=True;(Note: For older OLE DB providers, use Encrypt=False;).
4. Click OK.

' VBA ADODB legacy compatibility string
Dim cn As Object
Set cn = CreateObject("ADODB.Connection")

cn.ConnectionString = "Provider=MSOLEDBSQL;" & _
                      "Data Source=LegacyServer01;" & _
                      "Initial Catalog=ProductionArchive;" & _
                      "Integrated Security=SSPI;" & _
                      "Encrypt=Optional;" & _
                      "TrustServerCertificate=True;"
cn.Open

If certificate validation warnings continue after relaxing encryption, follow the steps in Solving SSL Provider errors: “The certificate chain was issued by an untrusted authority.”.

Method 2: Patch the Legacy SQL Server for TLS 1.2 Support (Permanent Fix)

The proper, secure enterprise fix is updating the SQL Server instance so it natively supports TLS 1.2. Microsoft released out-of-band updates providing TLS 1.2 support for all legacy editions down to SQL Server 2008.

1. Identify your exact SQL Server version and build number by running: SELECT @@VERSION;
2. Verify if your server build meets the minimum TLS 1.2 support threshold:
   • SQL Server 2014: Service Pack 1 CU5 (12.0.4422.0) or SP2+
   • SQL Server 2012: Service Pack 3 CU1 (11.0.6518.0) or SP4+
   • SQL Server 2008 R2: Service Pack 3 (10.50.6000.34) + TLS 1.2 Update
   • SQL Server 2008: Service Pack 4 (10.00.6000.29) + TLS 1.2 Update
3. Download and apply the corresponding Service Pack and cumulative update from the official Microsoft Update Catalog.
4. Update the server’s .NET Framework and SQL Native Client on the host machine to support TLS 1.2.
5. Reboot the server. Modern Excel clients can now connect with full encryption enabled.

Method 3: Use an Intermediate Client Driver Version

If you cannot patch the database and your client computer runs modern Windows 11 (which rejects connections when modern drivers like MSOLEDBSQL 19 are forced), install an intermediate driver version such as Microsoft OLE DB Driver 18 for SQL Server or ODBC Driver 17 for SQL Server.

Unlike version 19/20, driver version 18 does not enforce mandatory encryption by default.

1. Install ODBC Driver 17 for SQL Server on the client PC.
2. If Excel throws provider-missing errors when switching drivers in legacy workbooks, see Fixing “The provider ‘SQLNCLI11’ is not registered” on 64-bit systems..
3. Update your connection string provider to use the installed driver version.

---

Diagnostic Checklist for Persisting Handshake Failures

If you have adjusted connection strings and verified service packs, but pre-login handshake errors continue:

• Check Windows Schannel Cipher Suites: Use the free tool IISCrypto or inspect HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols on both client and server. If the server only supports 3DES or RC4 ciphers, and the client desktop has deprecated those algorithms, the TLS handshake will fail before user authentication begins.
• Query Timeout Drops During Handshake: High-latency wide-area network links can trigger connection drops if the cryptographic negotiation takes longer than the driver’s default login timeout (15 seconds). Learn how to increase execution boundaries in Handling Refresh Timeouts: Setting the CommandTimeout parameter..