Ошибка безопасности ssl microsoft sql server

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!

Essential SQL Server Administration Tips - Online Course with Live Demonstrations and Hands-on Guides

(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:

[DBNETLIB] [ConnectionOpen (SECDoClientHandshake()).] SSL Security Error - How to Resolve - Article on SQLNetHub

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

Read Also

  • DBA Security Advisor v2.3 is Now Out!
  • The OLE DB provider “SQLNCLI11” for linked server “…” supplied inconsistent metadata for a column… – How to Resolve
  • SQL Server 2022: What’s New – New and Enhanced Features (Course Preview)
  • How to Connect to SQL Server Databases from a Python Program
  • What is Data Security and which are its Main Characteristics?
  • Introduction to Azure Database for MySQL (Course Preview)
  • Data Management for Beginners – Main Principles (Course Preview)
  • Advanced SQL Server Features and Techniques for Experienced DBAs
  • SQL Server Database Backup and Recovery Guide

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 Star2 Stars3 Stars4 Stars5 Stars (8 votes, average: 5.00 out of 5)

Loading… 

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

© SQLNetHub

Artemakis Artemiou

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.

Views: 24,707

Источник
  • 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

Источник
Рекомендовать в новости

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.

Views: 24,707

Источник
  • 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

Источник

Гость

0

— 23.10.2018 — 18:55
Работаю на терминальном сервере .
БАЗА на сервере 1С предприятия крутится на другом сервере
Обращаюсь к третьему серверу SQL (не 1С) для прямого SQL запроса
Все работало много лет

Счас при попытке соединения пишет :
Произошла исключительная ситуация (Microsoft OLE DB Provider for SQL Server): [DBNETLIB][ConnectionOpen (SECCreateCredentials()).]Ошибка безопасности SSL.

Где ошибка ?
На сервере SQL где сервер 1С Предприятия или на сервере к которому делается запрос ?

что делать ?
Перезагружате сервак SQL ?

Коннекчусь к SQL вот так :

eXsocet = Новый COMОбъект («ADODB.Connection»);
eXrowset = Новый COMОбъект(«ADODB.Recordset»);

eXsocet.Open(ConnectionString(Server,NameDatabase, UserID,Password));
eXrowset.ActiveConnection = eXsocet;

eXrowset.Open(СокрЛП(SqlQuery));

Функция ConnectionString(Server,NameDatabase,UserID = 0,Password = 0) Экспорт
Перем ConnectionString;

ConnectionString =»Provider=SQLOLEDB.1;Persist Security Info=False;»;
ConnectionString = ConnectionString + «Initial Catalog=»+СокрЛП(NameDatabase)+»;Data Source=»+СокрЛП(Server)+»;»;
Если ПустаяСтрока(UserID) <> 1 Тогда
ConnectionString = ConnectionString + «User ID=»+СокрЛП(UserID)+»;»;
Иначе
ConnectionString = ConnectionString + «Integrated Security=SSPI;»;
КонецЕсли;
Если ПустаяСтрока(Password) <> 1 Тогда
ConnectionString = ConnectionString + «Password=»+СокрЛП(Password)+»;»;
КонецЕсли;

Возврат ConnectionString;
КонецФункции//ConnectionString

Гость

1

— 23.10.2018 — 19:11

Если возникает ошибка вида [DBNETLIB][ConnectionOpen (SECCreateCredentials()).]SSL Security error при обращении к MSSQL серверу, то решение следующее:
Необходимо установить значение Enable параметру групповой политики безопасности
Local Security Policy —> Local Policies —> Security Options —> ‘System cryptography: Use FIPS compliant algorithms for encryption, hashing, and signing

Затем от имени администратора выполнить в командной строке:
gpupdate /force

Гость

2

— 24.10.2018 — 09:37

Пытаешься организовать SSL соединение, а доверия сертификатам нет. ИМХО.
«Почему вдруг SSL», скорее всего, в 1)

Гость

3

— 24.10.2018 — 13:06

(1) Это нужно проделать на сервере SQL к которому идет обращение ?

Гость

4

— 24.10.2018 — 17:01

(3) Да
Источник


First published on MSDN on Jul 09, 2015

This blog is regarding one of most commonly faced issues that you may receive when connecting to the SQL Server. Mostly you may run into this issue after some improper Windows security update (say

KB2655992

in my case) or improper application of Poodle security fix.


ISSUE DESCRIPTION FROM SQL CONNECTIVITY STANDPOINT:

When we try to connect to the SQL Server instance using the SQL Server Management Studio, it may fail with the following error message,


Error:

TITLE: Connect to Server

——————————

Cannot connect to <mySQLServer>.

——————————

ADDITIONAL INFORMATION:

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: Shared Memory Provider, error: 0 — No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)

For help, click:

http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=233&LinkI…

——————————

BUTTONS:

OK

——————————

Using a UDL file, we saw the following error message,

—————————

Microsoft Data Link Error

—————————

Test connection failed because of an error in initializing provider. [DBNETLIB][ConnectionOpen (SECDoClientHandshake()).]SSL Security error.

—————————

OK

—————————


Note:

Forcing the connection to use any other protocol like TCP, Named Pipes & Shared Memory also throws the same error message. The Dedicated administrator connection (DAC) was also throwing a “Login timeout expired.” error.


OUR FINDINGS & INFERENCE:

There are numerous reasons on why you may witness these error messages. But usually, if you see these 2 specific error messages from SQL Server Management Studio & UDL file. Then it is worth checking the below settings.

As per the error message received, “…


an error occurred during the pre-login handshake



.”

and “


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

.”

means that the client application was able to complete the TCP 3-way handshake properly (hence you notice “


A connection was successfully established with the server


”), but during the


pre-login handshake,


the client application checks with the SQL Server on the TDS protocol version to be used henceforth for the communication, the login passed by the client application (Windows authenticated login or SQL Authenticated Login), whether there is any client-side or SQL Server connection encryption using SSL certificates or TLS etc. If the SQL Server doesn’t respond to this request from the client in a timely fashion or fails to respond due to any internal machine-level issues, we end-up at this particular error message. (wiz. “…


an error occurred during the pre-login handshake”


)

These error messages are thrown from the actual SQL Server drivers / providers that are used to establish the connection to the SQL server. E.g (OLE DB provider for SQL Server / SQL Server Native Client etc.). So different SQL Drivers/ Providers throws different error messages for the same issue. When we tried the connection from the UDL file, we see a different error message for this reason, but this error message was more straightforward.


Error:

Test connection failed because of an error in initializing provider. [DBNETLIB][ConnectionOpen (SECDoClientHandshake()).]SSL Security error.

Hence, we directly jumped to the SCHANNEL registry hive to check the values. Wiz.

Target hive:


HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSecurityProvidersSCHANNEL


CAUSE:

By default, you may not find the below registry keys, which is completely fine. But  in my case, when we checked the values for these registry keys on the target SQL Server, the following were the values.

i)

HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSecurityProvidersSCHANNELProtocolsSSL 2.0Server


DisabledByDefault

was set to 1


Enabled

was set to 0

ii)

HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSecurityProvidersSCHANNELProtocolsSSL 3.0Server


DisabledByDefault

was set to 1


Enabled

was set to 0

iii)

HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSecurityProvidersSCHANNELProtocolsTLS 1.0 Server


DisabledByDefault

was set to 0


Enabled

was set to 0

Based on these values, we figured-out that none of the security provider protocols were enabled. We confirmed that all SSL 2.0, SSL 3.0 & TLS 1.0 were disabled and this is not an ideal scenario.


RESOLUTION:

We enabled the TLS 1.0 protocol by setting the following value.

Under,


HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSecurityProvidersSCHANNELProtocolsTLS 1.0 Server

Set

Enabled

to

1

Rebooted the SQL Server machine for the changes to reflect.

After the successful reboot, we were able to connect to the SQL Server locally without any issues. Also tested the connection from a remote server machine and confirmed that the connections to the SQL Server worked as expected without any issues. SQL server was just a victim here like any other application that uses windows security providers and since all the security provider’s protocols were disabled, the SQL Server wasn’t able to accept any new connection request.

Please drop in your comments or connect with Microsoft BI-ONE CSS team if you are still encountering the same issue even after performing the above steps.

Happy troubleshooting!!!!


Author: Krishnakumar Rukmangathan, Technical Advisor, SQL Server BI-ONE Developer team, Microsoft


Reviewed by: Sunil Kumar B.S, Escalation Engineer, SQL Server BI-ONE Developer team, Microsoft.

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!

Essential SQL Server Administration Tips - Online Course with Live Demonstrations and Hands-on Guides

(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:

[DBNETLIB] [ConnectionOpen (SECDoClientHandshake()).] SSL Security Error - How to Resolve - Article on SQLNetHub

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 [New]
  • 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

Read Also

  • DBA Security Advisor v2.3 is Now Out!
  • The OLE DB provider “SQLNCLI11” for linked server “…” supplied inconsistent metadata for a column… – How to Resolve
  • SQL Server 2022: What’s New – New and Enhanced Features (Course Preview)
  • How to Connect to SQL Server Databases from a Python Program
  • What is Data Security and which are its Main Characteristics?
  • Introduction to Azure Database for MySQL (Course Preview)
  • Data Management for Beginners – Main Principles (Course Preview)

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
  • 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 Star2 Stars3 Stars4 Stars5 Stars (8 votes, average: 5.00 out of 5)

Loading… 

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

© SQLNetHub

Artemakis Artemiou

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.

Views: 20,612

We are experiencing the following error when attempting to run a SQL Server 2016 SSIS package.

Error: 2020-01-07 11:01:19.31
Code: 0xC0202009
Source: InsightMonthlyFile Connection manager "CognitionDB"Description: SSIS Error Code DTS_E_OLEDBERROR. 
An OLE DB error has occurred. Error code: 0x80004005.An OLE DB record is available.  Source: "Microsoft OLE DB Provider for SQL Server"  Hresult: 0x80004005  Description: "[DBNETLIB][ConnectionOpen (SECDoClientHandshake()).]SSL Security error.".
End Error

We temporarily re-enabled TLS 1.0 on the server and the package worked, but we need to ensure this protocol remains disabled. Both TLS 1.1 and TLS 1.2 are enabled on the server, and .NET Framework 4.7 is installed (we are running SQL Server 2016 Standard Edition on Windows Server 2016 Standard).

We added a script task to the SSIS package as per here (in order to force the connection to use TLS 1.2, which is enabled), however the error is still occurring.

Public Sub Main()
    ' Force SSIS package to use TLS 1.2.
    System.Net.ServicePointManager.SecurityProtocol = CType(3072, System.Net.SecurityProtocolType)
    Dts.TaskResult = ScriptResults.Success
End Sub

A very frustrating issue, especially since TLS 1.2 is supposed to be enabled by default. Can anyone please advise?

Many thanks in advance.

  • This topic was modified 3 years ago by  zoggling.

MVDBA (Mike Vessey)

SSC-Insane

Points: 21797

I’ve been through this before, but I’ve been to sleep since then

I did a quick google based on my issue at the time and there are a few registry settings to modify

[HKEY_LOCAL_MACHINESOFTWAREMicrosoft.NETFrameworkv4.0.30319]

«SchUseStrongCrypto»=dword:00000001

[HKEY_LOCAL_MACHINESOFTWAREWow6432NodeMicrosoft.NETFrameworkv4.0.30319]

«SchUseStrongCrypto»=dword:00000001

this is obviously framework 4.0, but you get the drift? these force 4.0 to use TLS1.2

original material from https://success.solarwindsmsp.com/kb/solarwinds_n-central/How-To-Force—NET-to-use-TLS-1-2

MVDBA

zoggling

SSCrazy

Points: 2209

Thank you for your reply. Unfortunately I have been unable to locate the keys you reference (or any equivalents), throughout the whole registry?

MVDBA (Mike Vessey)

SSC-Insane

Points: 21797

just keep googling — it was definatly a registry key for me

MVDBA

zoggling

SSCrazy

Points: 2209

We created both of those keys (as they were not present), but no cigar sadly. Been Googling for most of the day!!

MVDBA (Mike Vessey)

SSC-Insane

Points: 21797

there aer definatly 2 or 3 articles on here about it

I Turned TLS1.0 off and just kept plugging away — took me most of a day, but i’m sorry , I can’t rememeber exactly the details other than registry

MVDBA

Источник
  • Remove From My Forums

  • Question

  • i found the following error on my server after disable tls 1.0 and SSLv3.

    My server is windows 2008 server r2, and below is detil error that i got :

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

    currently, only TLS 1.2 is enable on my server, and at the client side the TLS 1.2 is set on

    Whether it posible if [DBNETLIB] running on TLS 1.2?

    • Moved by

      Wednesday, April 6, 2016 6:39 PM
      Moved to a relevant forum for best results

Answers

    • Proposed as answer by
      Sam ZhaMicrosoft contingent staff
      Wednesday, April 13, 2016 5:05 AM
    • Marked as answer by
      Sam ZhaMicrosoft contingent staff
      Friday, April 15, 2016 11:26 AM
Источник

Понравилась статья? Поделить с друзьями:
  • Ошибка безопасности ssl microsoft ole db
  • Ошибка безопасности parentcontainserrorrecordexception fullyqualifiederrorid unauthorizedaccess
  • Ошибка безопасности kerberos при добавлении серверов
  • Ошибка безопасности kerberos на контроллере ad
  • Ошибка безопасности kerberos server 2012 r2