26

Our client recently upgraded from TLS 1.0 to TLS 1.2 and after this our software cannot connect with SQL server. It uses OLE DB provider for connecting to SQL server. Below is the error which is returned from SQL server-

[DBNETLIB][ConnectionOpen SECDoClientHandshake()]SSL Security error SQL State: 08001 SQL Error Number: 18

Could not find any useful information related to whether Microsoft OLE DB Provider for SQL Server support TLS 1.2 or not.

One of the links I found seems to suggest that it is not supported. https://forums.iis.net/t/1233674.aspx?connecing+SQL+server+DB+issue+after+installingTLS1+2+in+SQL+srver+with+classic+asp+application+

Hence, wanted to check on stackoverflow in case anyone has any information on this.

Improve this question

6 Answers 6

Reset to default
54

The SQLOLEDB provider and the SQL Server ODBC driver that ship with Windows are legacy components provided only for backwards compatibility. These have been deprecated since SQL 2005.

According to this blog post by the MSSQL Tiger Team:

SQLOLEDB will not receive support for TLS 1.2. You will need to switch your driver to one of the supported drivers listed in https://support.microsoft.com/en-us/kb/3135244

You should be able to install SQL Server Native Client 2012 and use that OLE DB provider with only a connection string change (change Provider=SQLOLEDB to Provider=SQLNCLI11). Of course, once should test to avoid surprises. For example, I recall someone experiencing behavior differences with the SQL Server Native Client provider and ADO classic when server API cursors were used, although the commonly used firehose cursors were fine. 

EDIT

The new OLE DB driver, MSOLEDBSQL, has been released. This new driver includes the support for the latest TLS 1.2 standards and is backwards compatible with SQL Server Native Client 11 (SQLNCLI11). See the Microsoft SQLNCLi team blog announcement. Change the connection string to Provider=MSOLEDBSQL after installation.

EDIT #2

Time continues to march on. The SQL Server Native Client OLE DB driver is now deprecated in favor of MSOLEDBSQL. End of support for the final 2012 SNAC version is July 12, 2012.

Furthermore, as David called out in his answer, Microsoft recently added support for TLS 1.2 MDAC components in Windows 10 Build 17763.1554:

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)

No enhancements (e.g. support for post-SQL 2000 data types) were added, though.

In summary, newer SQL Server client drivers provides more control over the specific driver and version without a direct OS patch level dependency. Modern drivers like MSOLEDBSQL and ODBC Driver n for SQL Server are continuously improved to support latest SQL Server features and security standards.

EDIT#3

SQLOLEDB and the SQL Server ODBC driver (part of MDAC, shipped and serviced with Windows) has support for TLS 1.2 with October 20, 2020 build, version 17763.1554. This applies to Windows 10 and Windows Server 2019 or later.

Improve this answer
3
  • 1
    Cracking answer.
    – Ciarán
    Commented Sep 3, 2017 at 19:44
  • 5
    Remember to change Provider=SQLOLEDB to Provider=MSOLEDBSQL. This wasn't immediately apparent to me. I thought that I just needed to install the new driver and it would just work.
    – user1751825
    Commented May 25, 2020 at 5:31
  • Recommend mentioning here the subtle difference in naming in the Microsoft documentation, or continue reading Dan Guzman's notes below. Also (2021) Microsoft has now deprecated the Native Client. Which, admittedly, just sounds surprising.
    – Forbin
    Commented Jun 22, 2021 at 19:25
12

This may not be a solution for you, since it's a future fix your client may not be able to wait for, but apparently Microsoft is undeprecating the OLEDB Driver, with a new release supporting TLS 1.2 out Q1 2018: https://blogs.msdn.microsoft.com/sqlnativeclient/2017/10/06/announcing-the-new-release-of-ole-db-driver-for-sql-server/

The new Microsoft OLE DB Driver for SQL Server, or msoledbsql, will also introduce multi-subnet failover capabilities in this first upcoming release, and keeps up with latest TLS 1.2 standards.

Also, this first upcoming release will be a stand-alone install package that is out-of-band with SQL Server lifecycle. This also means the driver will not be packaged in the SNAC library, nor coupled with any other driver.

Improve this answer
7
  • 5
    To be clear, the legacy "Microsoft OLE DB Provider for SQL Server" (SQLOLEDB) remains deprecated. The undeprecation announcement says a new OLE DB provider will be released. The name will be "Microsoft OLE DB Driver for SQL Server" (MSOLEDBSQL).
    – Dan Guzman
    Commented Jan 24, 2018 at 11:52
  • @DanGuzman So, I am trying to find this new provider but I can't so far. See this explanation. dba.stackexchange.com/q/202636/148012 Do you have Ideas?
    – T.S.
    Commented Mar 29, 2018 at 16:26
  • @T.S., it has not yet been released.
    – Dan Guzman
    Commented Mar 29, 2018 at 17:22
  • @T.S., I expect there will be an official announcement, probably on the same blog and hopefully soon since they said March. I think it will likely be a separate download/install.
    – Dan Guzman
    Commented Mar 29, 2018 at 18:05
  • @T.S., the new driver is now available and I added the links to my answer..
    – Dan Guzman
    Commented Mar 30, 2018 at 23:02
4

TLS 1.2 Support has been added to sqloledb in Windows. See KB4580390.

This includes support both ODBC and OleDB providers in MDAC:

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)

You can verify that MDAC has been updated by checking the Windows build number, anything 17763.1554 or later has this fix. MDAC has not been distributed outside of OS patches for many years.

The build is visible in winver or in Powershell with [environment]::OSVersion.Version.Build

Improve this answer
5
  • That's great news, thanks. Is there any online resource with more details on this change? In particular, I'd like to know whether it also affects the "classic" {SQL Server} ODBC driver, and how we, the software developers, can verify that MDAC has been upgraded to a TLS 1.2 compatible version on a customer's system.
    – Heinzi
    Commented Oct 27, 2021 at 20:11
  • See updated answer.
    – David Browne - Microsoft
    Commented Oct 27, 2021 at 20:41
  • Brilliant, thanks!
    – Heinzi
    Commented Oct 27, 2021 at 21:02
  • Thanks! One clarification: KB4580390 seems to be a Windows 10 update; do you happen to know about Windows 11 support?
    – Nickolay
    Commented May 16, 2022 at 17:00
  • 1
    All Windows 10 updates before the launch of Windows 11 are part of Windows 11. So yes, this is supported in Windows 11.
    – David Browne - Microsoft
    Commented May 16, 2022 at 17:54
1

Following changes on my end fixed the issue after TLS1.2 upgrade on Azure cloud -

  • change Provider=SQLOLEDB to Provider=SQLNCLI11
  • update ADODB version to Microsoft ActiveX Data Objects 6.0 Library
Improve this answer
2
  • Just be aware. SQLNCLI11 may not be fully compatible with SQLOLEDB. A classic ASP application I was attempting to ugprade, didn't work with this provider, because of incompatibilities with XML query result sets. Use with caution.
    – user1751825
    Commented May 25, 2020 at 5:27
  • If you do switch to a "native" client (e.g. SQLNCLI, SQLNCLI10, SQLNCLI11, MSOLEDBSQL, MSOLEDBSQL19), and you are using ActiveX Data Objects (ADO), then you need to include DataTypeCompatibility=80 in your connection string, to cause SQL Server to present the new data types as an equivalent old data type (e.g. xml columns will be present to the client as nvarchar(max).
    – Ian Boyd
    Commented Dec 13, 2022 at 21:08
1

The use of "Microsoft OLEDB Driver for SQL Server" is what worked for us but I can also confirm Native Driver 11 also tests OK.

Here was our scenario: after we disabled TLS 1.0 and 1.1 and enabled TLS 1.2, Crystal Reports using the "Microsoft OLEDB Provider for SQL Server" would no longer connect. Instead you get a user/pw prompt that fails with even with valid credentials. In our case we were running Crystal Reports from within an ASP.NET v4.5.2 application that has the Crystal 13 Viewer embedded in. Users pick from a list of reports and run them and they run without a prompt with TLS 1.0 enabled.

To fix this, we had to open the report in the designer and convert it report from using the "Microsoft OLEDB Provider for SQL Server" to using the "Microsoft OLEDB Driver for SQL Server".

If you don't see the driver in your list here's the OLEDB Driver for SQL Server: https://learn.microsoft.com/en-us/sql/connect/oledb/download-oledb-driver-for-sql-server?view=sql-server-ver15

Credit to Dan Guzman who mentioned the existence of the "driver" in a somewhat buried comment and an update above.

Improve this answer
0

This might not directly answer the question, but it is still related to sql server connection with TLS 1.2 error.

I'm maintaining an old ASP Classic website which broke with following error.

Microsoft OLE DB Provider for SQL Server error '80004005'
[DBNETLIB][ConnectionOpen (SECDoClientHandshake()).]SSL Security error.

Changing Provider from SQLOLEDB to SQL Server Native Client 11.0 or any higher version which is available fixed the error.

Thus, changing connection string from 

constr = "Provider=SQLOLEDB;Data Source=..."

to

constr = "Provider=SQL Server Native Client 11.0;Data Source=...."

might work too

Improve this answer
1
  • Just be sure to test everything carefully. I found the code I was working with was only partially compatible with the native client.
    – user1751825
    Commented May 25, 2020 at 5:35

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