5

A legacy piece of software using the provider "sqloledb.1" via the OleDbConnection Class (System.Data.OleDb.dll) is still working on Windows Server 2019 with TLS1.2. Whereas on Server 2016 or 2012 R2 with TSL1.2 it is not?

Windows Server 2016 gets the following error, which is expected as it is using the sqloledb which doesn't support TLS1.2. This is well known as seen here.

[DBNETLIB][ConnectionOpen (SECCreateCredentials()).]SSL Security error.

Windows Server 2019 works and I do not know why. Is it struggling to find the sqloledb and defaulting to the new driver (MSOLEDBSQL) that supports TLS1.2?

All servers have the same version of the Microsoft OLE DB Driver for SQL Server installed, 18.5.

Tried different versions of SQL server, 2017 and 2019.

Update

sqloledb.dll properties showing the version information

Server 2019

sqloledb Server 2019

Server 2016

enter image description here

RickWeb
  • 1,765
  • 2
  • 25
  • 40

3 Answers3

6

SQL Server OLE DB Providers

OLE DB Provider Description Available with Minimum SQL Server Max TLS Installed with Status (As of Q3 2022)
SQLOLEDB "Microsoft OLE DB Provider for SQL Server" Windows 2000 SQL Server 7.0 (70) TLS 1.0 Windows (MDAC) Deprecated
SQLOLEDB
(2021 update)		 "Microsoft OLE DB Provider for SQL Server" Windows 10 1809+ SQL Server 7.0 (70)

(SQL Server 2008+ needed for TLS 1.2)		 TLS 1.2 Windows (MDAC) Deprecated
SQLNCLI "SQL Server Native Client" ("SNAC") SQL Server 2005 SQL Server 7.0 (70) TLS 1.1Note 1 Manually Deprecated
SQLNCLI10 "SQL Server Native Client 10.0" SQL Server 2008 SQL Server 2000 (80) TLS 1.2 Manually Deprecated
SQLNCLI10
(2020 update)		 "SQL Server Native Client 10.0" SQL Server 2000 (80)

(SQL Server 2008+ needed for TLS 1.2)		 TLS 1.2 Manually Deprecated
SQLNCLI11 "SQL Server Native Client 11.0" SQL Server 2012 SQL Server 2005 (90) TLS 1.2 Manually Deprecated
SQLNCLI11
(2018 update)		 "SQL Server Native Client 11.0" SQL Server 2005 (90)

(SQL Server 2008+ needed for TLS 1.2)		 TLS 1.2 Manually Deprecated
MSOLEDBSQL "Microsoft OLE DB Driver for SQL Server" 2017 Announcement SQL Server 2012 (110)

(SQL Server 2016+ needed for TLS 1.3)		 TLS 1.3 Manually Current
MSOLEDBSQL19 "Microsoft OLE DB Driver 19 for SQL Server" SQL Server 2019 SQL Server 2012 (110)

(SQL Server 2016+ needed for TLS 1.3)		 TLS 1.3 Manually Current

Note 1: The original SQLNCLI for SQL Server 2005 supports TLS 1.1 "for free" when the SChannel system on both the Server and Client machines supports TLS 1.1, even though TLS 1.1 was released in 2006 after SQL Server 2005.

Dai
  • 141,631
  • 28
  • 261
  • 374
Ian Boyd
  • 246,734
  • 253
  • 869
  • 1,219
  • Thanks for input, I am going to swap it over to the sql native client, but I can confirm that Windows 10 & Windows Server 2019, with the updates detailed by David Browne, work using the SQLOLEDB provider with TLS1.2. SCHANNEL was used to only allow TLS1.2. – RickWeb Jun 15 '21 at 09:33
  • 4
    There's no point in using the *"native clients"* (SQLNCLI, SQLN10, SQLNCLI11). If anything: use the currently supported one `MSOLEDBSQL`. There is a whole backstory saga: Microsoft deprecated all OLEDB drivers for SQL Server, and instead were only going to maintain the ODBC driver. A year and a half later they reversed themselves, and decided they *would* continue to have OLEDB drivers, and created **MSOLEDBSQL**. Our (desktop) apps all use SQLOLEDB (because it comes with, and is serviced by, Windows). And now that `SQLOLEDB` supports TLS 1.2, we will not bother switching to MSOLEDBSQL to all. – Ian Boyd Jun 15 '21 at 22:00
5

sqloledb is part of Windows, and it was recently updated to support TLS 1.2. See KB4580390:

Adds support for the Transport Layer Security (TLS) 1.1 and 1.2 protocols when connecting to SQL Server using the data providers in Microsoft Data Access Components (MDAC).

So as of "Windows 10, version 1809, Windows Server version 1809" (OS Build 17763.1554) this should work.

Testing locally on Windows 10 20H2 running

static void Main(string[] args)
{

    using (var con = new OleDbConnection("Provider=sqloledb;data source=localhost;trusted_connection=yes;Network Library=DBMSSOCN"))
    {
        con.Open();
        Console.WriteLine("Hello");
        Console.ReadKey();
    }
}

Where protocol encryption is forced on the server:

enter image description here

And capturing this XEvent:

CREATE EVENT SESSION [tls] ON SERVER 
ADD EVENT sqlsni.sni_trace(
    WHERE ([sqlserver].[like_i_sql_unicode_string]([text],N'%Handshake%')))

Captured this debug message:

text    SNISecurity Handshake Handshake succeeded. Protocol: TLS1.2 (1024), Cipher: AES 256 (26128), Cipher Strength: 256, Hash: SHA 384 (32781), Hash Strength: 0, PeerAddr: 127.0.0.1
Ian Boyd
  • 246,734
  • 253
  • 869
  • 1,219
David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • Ok that would explain why its suddenly started working, comparing the properties from Server 2019 to Server 2016 you can see the increase in version number. Do you know if server 2016 will receive an update at any point? or if I can simply more the dll across? – RickWeb Jun 14 '21 at 10:51
  • 1
    I don't know if it will be back-ported, and it's a Windows component, so simply copying the DLL is not supported. – David Browne - Microsoft Jun 14 '21 at 12:04
  • I am going to swap it out for the sql native client, thank you for the information, its great you guys are on here. – RickWeb Jun 15 '21 at 09:27
  • @RickWeb, not exactly sure what you mean by sql native client but be sure to use MSOLEDBSQL rather than SQLNCLIxx. SQL Server Native Client is deprecated with end of support next year. – Dan Guzman Jun 23 '21 at 11:00
  • 1
    Sorry I didn't actually mean the native client, I will just be using System.Data.SqlClient – RickWeb Jun 24 '21 at 16:25
0

As far as I could read in the Internet the native client is faster than the MSOLEDBSQL driver when working with ADO as OleDB is a different layer in between.

Limux
  • 1
  • This does not provide an answer to the question. Once you have sufficient [reputation](https://stackoverflow.com/help/whats-reputation) you will be able to [comment on any post](https://stackoverflow.com/help/privileges/comment); instead, [provide answers that don't require clarification from the asker](https://meta.stackexchange.com/questions/214173/why-do-i-need-50-reputation-to-comment-what-can-i-do-instead). - [From Review](/review/late-answers/31967820) – Shmiel Jun 12 '22 at 19:02