Odbc sql server driver dbnetlib ошибка безопасности ssl - ErrorsMaster.ru - большая энциклопедия ошибок и их решений

This article, suggests a way of resolving the below error message, when you are trying to access SQL Server using “Microsoft OLE DB Provider for SQL Server” and TLS 1.0: [DBNETLIB] [ConnectionOpen (SECDoClientHandshake()).] SSL Security Error

Prior to start writing this article, I was thinking of using a title like “How to still use TLS 1.0”, or something similar, but besides the fact that would have given a wrong message, it would not help so much because many people, usually search for such articles using the error message (SSL Security error)…

So, I anticipate that this article, with this title, would help as many people as possible 🙂

Drop me a line if you find the article useful and why not, subscribe to my newsletter to stay up to date with future articles!

A Few Words About TLS 1.0

TLS 1.0 is considered a deprecated protocol and it is not recommended anymore to be used to secure connections. That’s why many organizations (if not all) transitioned or are in the process of transitioning to newer versions of TLS such as TLS1.1 or above.

However, you may still encounter outdated applications that still need to use this protocol, even for a while for just performing a single operation. One such example, is to try and connect to a SQL Server instance via Microsoft OLE DB Driver for SQL Server using TLS 1.0.

If you are in such situation, I have good news, from a technical aspect, it is still possible to do this.

Read on to learn more.

SQL Server Support for TLS 1.0 and Above

SQL Server still supports all TLS protocols, currently from 1.0 to 1.2. However, depending on the version of SQL Server you have, especially in cases of older SQL Server versions, you might need a patch.

Read this article on SQLNetHub to learn more about SQL Server support for TLS versions.

Now let’s jump to the juicy part of this article and see how finally we can resolve the above error and manage to connect to SQL Server using Microsoft OLE DB Driver for SQL Server and TLS 1.0.

Note that if you are just trying to connect with TLS 1.0 for a while in order to perform a specific task, then make sure to revert the below changes in order to restore the security level of your systems back to their previous level.

Latest Microsoft OLE DB Driver for SQL Server

The first step towards resolving the SSL Security error, is to make sure that the version of the target SQL Server instance you want to connect to, is supported by the driver.

For example, Microsoft OLE DB Driver 18.1 for SQL Server supports connecting to SQL Server 2012 or later.

For older versions of SQL Server, you will need to find an earlier version of Microsoft OLE DB Provider for SQL Server as well.

You can find the latest version of the OLE DB driver here.

Useful details:

The Microsoft OLE DB Provider for SQL Server, allows ADO to access Microsoft SQL Server. However, This is an older driver and it is not recommended to be used driver for new development, since it is deprecated.

The new OLE DB provider is called the Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL) which will be updated with the most recent server features going forward (learn more)

Registry Changes

The next step is, to edit the Windows Registry (* always be careful when messing up with Windows Registry – only certified engineers should do that).

To enable TLS 1.0 in Windows

In Windows Registry, add the below dword keys:

[HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSecurityProvidersSCHANNELProtocolsTLS 1.0Server]

“Enabled”=dword:00000001
“DisabledByDefault”=dword:00000000

[HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSecurityProvidersSCHANNELProtocolsTLS 1.0Client]

“Enabled”=dword:00000001
“DisabledByDefault”=dword:00000000

To disable TLS 1.0 in Windows

[HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSecurityProvidersSCHANNELProtocolsTLS 1.1Server]

“Enabled”=dword:00000000
“DisabledByDefault”=dword:00000001

[HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSecurityProvidersSCHANNELProtocolsTLS 1.1Client]

“Enabled”=dword:00000000
“DisabledByDefault”=dword:00000001

Learn more about the above registry changes in this MS Docs article.

Local Security Policy

The next step is to check the Local Security Policy on the database server.

So, in Local Security Policy on the Database Server, make sure that the setting “System Cryptography: Use FIPS compliant algorithms for encryption, hashing, and signing” is disabled.

If you want to learn more about this security option, you can check this MS Docs article.

Strengthen your SQL Server Administration Skills – Enroll to our Online Course!

Check our online course on Udemy titled “Essential SQL Server Administration Tips”
(special limited-time discount included in link).

Via the course, you will learn essential hands-on SQL Server Administration tips on SQL Server maintenance, security, performance, integration, error handling and more. Many live demonstrations and downloadable resources included!

(Lifetime Access/ Live Demos / Downloadable Resources and more!)
Enroll from $12.99

Server Protocols, Ciphers, Hashes and Client Protocols

The last step in this troubleshooting guide, is to use IISCrypto, which is an excellent free tool, that allows you to control which protocols, ciphers, and more are enabled (or not) on a Windows server.

That being set, you will need to run IISCrypto and make sure that the “TLS 1.0” Server and Client Protocols, as well as the”SHA” hash are enabled.

Here’s a screenshot of IISCrypto, running on my PC, having TLS 1.0 and “SHA” enabled for illustration purposes:

Note that, if finally you need to perform any changes using IISCrypto, you will need to restart the server.

Actually, for any changes you might need to perform, it is recommended to restart the server.

A Piece of Advice

As mentioned in this article’s beginning, TLS 1.0 is considered a deprecated protocol and it is not recommended anymore to be used to secure connections.

Instead, you should be using newer versions of TLS.

In case you just need to switch to TLS 1.0 for performing an ad hoc task, you need to make sure that after you completed the task, you revoked any changes you might have applied, and disable again TLS 1.0 and the “SHA” hash.

See More

Check out DBA Security Advisor, a SQL Server security tool to assess your SQL Server instances against a rich set of security checks and get security best practice recommendations.

Featured Online Courses:

SQL Server 2022: What’s New – New and Enhanced Features
Data Management for Beginners – Main Principles
Introduction to Azure Database for MySQL
Working with Python on Windows and SQL Server Databases
Boost SQL Server Database Performance with In-Memory OLTP
Introduction to Azure SQL Database for Beginners
Essential SQL Server Administration Tips
SQL Server Fundamentals – SQL Database for Beginners
Essential SQL Server Development Tips for SQL Developers
Introduction to Computer Programming for Beginners
.NET Programming for Beginners – Windows Forms with C#
SQL Server 2019: What’s New – New and Enhanced Features
Entity Framework: Getting Started – Complete Beginners Guide
A Guide on How to Start and Monetize a Successful Blog
Data Management for Beginners – Main Principles

Other SQL Server Security-Related Articles

How to Enable SSL Certificate-Based Encryption on a SQL Server Failover Cluster
Why You Need to Secure Your SQL Server Instances
Policy-Based Management in SQL Server
Advanced SQL Server Features and Techniques for Experienced DBAs
Should Windows “Built-InAdministrators” Group be SQL Server SysAdmins?
Frequent Password Expiration: Time to Revise it?
The “Public” Database Role in SQL Server
Encrypting SQL Server Databases
10 Facts About SQL Server Transparent Data Encryption
Encrypting a SQL Server Database Backup
…check all

Subscribe to our newsletter and stay up to date!

Subscribe to our YouTube channel (SQLNetHub TV)

Easily generate snippets with Snippets Generator!

Secure your databases using DBA Security Advisor!

Generate dynamic T-SQL scripts with Dynamic SQL Generator!

Check our latest software releases!

Check our eBooks!

Rate this article: 1 Star 2 Stars 3 Stars 4 Stars 5 Stars (8 votes, average: 5.00 out of 5)

Loading…

Reference: SQLNetHub.com (https://www.sqlnethub.com)

Artemakis Artemiou is a Senior SQL Server Architect, Author, a 9 Times Microsoft Data Platform MVP (2009-2018). He has over 20 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and {essentialDevTips.com}. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Moreover, Artemakis teaches on Udemy, you can check his courses here.

Источник

My server is windows 2008 server r2.
I found the following error on my server after disable tls 1.0 and SSLv3.

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

Currently, only TLS 1.2 is enabled on my server, and at the client side the TLS 1.2 is set on

Is it posible if [DBNETLIB] is running on TLS 1.2?

Источник

My server is windows 2008 server r2.
I found the following error on my server after disable tls 1.0 and SSLv3.

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

Currently, only TLS 1.2 is enabled on my server, and at the client side the TLS 1.2 is set on

Is it posible if [DBNETLIB] is running on TLS 1.2?

UditS

1,93616 silver badges37 bronze badges

asked Apr 6, 2016 at 13:29

There might be chances that ODBC 11.0 earlier version is been installed. In this case the connection string should be
Driver={ODBC Driver 11 for SQL Server};Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;

And also check TLS 1.2 is enabled

answered Dec 12, 2018 at 16:04

After disabling TLS 1.0 and 1.1 on Windows Server 2016 we were able to get our ASP Classic scripts database connections working again in the following way:

Download and install Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL)
Replace Provider=SQLOLEDB; with Provider=MSOLEDBSQL; in all connection strings for the ASP Classic scripts
Done! (no restart needed)

Note: The previous (same name but different abbreviation) Microsoft OLE DB Provider for SQL Server (SQLOLEDB) and SQL Server Native Client OLE DB provider (SQLNCLI) remains deprecated and it is not recommended to use either for new development work. (source)

answered Jan 12, 2022 at 13:25

I had to do several things. The server was Windows 2016 Standard testing with a .txt file I changed to .UDL. This server was trying to connect to our Sql Server 2008 R2 SP3

On the 2016 box I installed SQL Server Native client 10.0

Get that here : https://www.microsoft.com/en-us/download/details.aspx?id=57606

Finally on the sql server box I had to install patch KB4057113 to enable TLS1.2

Note when testing the connection by opening the .UDL file don’t forget to change the provider to SQL Server Native Client 10.0

answered Jun 21, 2019 at 18:58

drzoundsdrzounds

3693 silver badges16 bronze badges

Similiar to Jonas Appelgran’s answer, but some slight changes to solve our issue.

Download and install install Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL)
Replace Provider=SQLOLEDB; with Provider=MSOLEDBSQL19; in all connection strings for the ASP Classic scripts (note the version of the driver was needed)
At this point, was still getting the following error:

SSL Provider: The certificate chain was issued by an authority that is
not trusted.

After some research, I found that I also needed to either trust the server certificate or set the encryption to false in the connection string:

Trust Server Certificate=True;
and/or
Use Encryption for Data=False;

Need to update these depending on your situation but it was an internal intranet application only for us so either option was fine. (Otherwise you would need to create and install either a self-signed certificate or one from your organization’s CA.)

answered Jul 11, 2022 at 22:27

TahariTahari

1316 bronze badges

Open Regedit (Wind+R > regedit)
Locate HKLMSOFTWAREMicrosoftMSSQLServerMSSQLServerSuperSocketNetLib
Create a new word value and name it «Certificate» set the value to this: «Certificate=0»
Also modify the default REG_SZ and set the value to «Certificate=0»
Restart the MSSQL service

Hope this helps since worked for me.

answered Dec 15, 2017 at 15:03

Источник

Remove From My Forums

Вопрос
When I am trying to test SQL connection from WebFOCUS Windows server ( under segment network) with SQL server 2008. I got the following error :

Connection Failed:

SQL State: ‘01000’

SQL server Error: 772

[Microsoft][ODBC SQL Server Driver][DBNETLIB][ConnectionOpen (SECDoClientHandshake())

Connection Failed:

SQL state: ‘08001’

SQL Server Error :18

[Microsoft][ODBC SQL Server Driver][DBNETLIB]SSL security error

Ответы

Hi Neha,

Could you ping the SQL Server successfully? Clients that have the Force Protocol Encryption option set ON on the client may fail to connect to SQL Server if clients specify an IP address for the server name.

Please try:

Use the server name to connect to SQL Server. You can use the SQL Server Client Network Utility to set up an alias for the server that is running SQL Server or implement name resolution by using WINS, DNS, or LMHOST file so that you can connect by server
name.

Or Set the Force Protocol Encryption option to ON on the server by using the SQL Server Server Network Utility. If you turn on encryption on the server, all the clients must connect by using encryption and you must install a certificate
on the server. For more information, refer to the «Net-Library Encryption» topic in SQL Server 2000 Books Online.

For more information please see:
http://support.microsoft.com/kb/316779

Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
- Помечено в качестве ответа
  
  9 августа 2012 г. 2:11

Источник

Добрый день.

У нас размещена база IT-Invent на MsSQL сервере согласно инструкции. На Windows 10 все отлично работает.

После обновления до Windows 11 подключиться не удается из-за ошибки:

Ошибка соединения!

Connection string: Provider=SQLOLEDB;(дальше данные для авторизации)

[DBNETLIB][ConnectionOpen(SECCreateCredentials().)Ошибка безопасности SSL.

Что необходимо сделать, чтобы исправить эту ошибку?

Источник

Im trying to setup a mssql on a linux machine (CentOS). But the application cannot connect through it because of:

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

This is the error being thrown by the application. I’ve already disabled my firewall and configured applications OS’ host file to point to the linux server. Thank you

This is configuration in my mssql.conf

This is the application’s caught error

In windows the settings that Im using is I enable named pipes, tcp/ip in the configuration of the sql. Is there a translation to this in linux?

asked Jun 30, 2020 at 6:51

I have the same problem. What sytem are you using to run the application from? Older systems (up to Windows 7) have old ODBC and OLE DB drivers. I recommend updating them:

OLE DB Drivers
ODBC Drivers

If that does not help try running the same scenario on newer system and see it the problem persists.

Also chceck if the software which is causing these problems have configurable connection string. If so change OLEDBProvider to OLEDBDriver.

answered Sep 16, 2021 at 8:59

Источник

A Few Words About TLS 1.0

SQL Server Support for TLS 1.0 and Above

Latest Microsoft OLE DB Driver for SQL Server

Registry Changes

To enable TLS 1.0 in Windows

To disable TLS 1.0 in Windows

Local Security Policy

Server Protocols, Ciphers, Hashes and Client Protocols

A Piece of Advice

See More

Featured Online Courses:

Read Also

Other SQL Server Security-Related Articles

Вопрос

Ответы