Unable to connect sql server is unavailable or does not exist ssl security error - Исправление ошибок и поиск оптимальных решений проблем

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

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 $14.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 [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

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 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.

Источник

Я пытаюсь подключиться к базе данных SQL Server 2005 с моего Mac с помощью unixODBC и FreeTDS, как я изложил здесь. Однако, когда я пытаюсь подключиться к другой БД, используя ту же настройку, я получаю:

Connection Failed:[FreeTDS][SQL Server]Unable to connect: Adaptive Server is unavailable or does not exist.

Вот моя настройка freetds.conf:

[my_db]
host = 12.34.56.789
port = 1433
tds version = 8.0

И вот мой odbc.ini:

[my_dsn]
Driver = /opt/local/lib/libtdsodbc.so
Description = My Database
Trace = no
Servername = my_db
Database = MyDB

[ODBC Data Sources]
my_dsn = FreeTDS

Я все еще могу подключиться к другой БД, установленной на этом компьютере (описанный в моем сообщении в блоге, приведенном выше), поэтому я уверен, что ошибка не на конце Mac. Я проверил на сервере, что я использую правильный IP-адрес и порт. Любые мысли о том, что может быть проблемой, и если это на сервере заканчивается?

Ответ 1

1. См. Информацию о сервере SQL

tsql -LH SERVER_IP_ADDRESS

locale is "C"
locale charset is "646"
ServerName TITAN
InstanceName MSSQLSERVER
IsClustered No
Version 8.00.194
tcp 1433
np \TITANpipesqlquery

2. Установите freetds.conf

tsql -C    
freetds.conf directory: /usr/local/etc

[TITAN]
host = SERVER_IP_ADDRESS
port = 1433
tds version = 7.2

3 Попробуйте

tsql -S TITAN -U user -P password

ИЛИ

 'dsn' => 'dblib:host=TITAN:1433;dbname=YOURDBNAME',

См. также http://www.freetds.org/userguide/confirminstall.htm (пример 3-5.)

Если вы получаете сообщение 20009, помните, что вы не подключились к аппарату. Это проблема конфигурации или сети, а не отказ протокола. Проверьте, что сервер встал, использует имя и IP-адрес FreeTDS и слушает сконфигурированный порт.

Ответ 2

Похоже, что у вас есть проблема с источником данных dsn или odbc.

Попробуйте обходить сначала dsn и подключиться, используя:

TDSVER=8.0 tsql -S *serverIPAddress* -U *username* -P *password*

Если это работает, вы знаете его проблему с вашим dsn или с freetds, используя ваш dsn. Кроме того, возможно, что ваша версия tds несовместима с вашим сервером. Возможно, вы захотите попробовать другие настройки TDSVER (5.0, 7.0, 7.1).

Ответ 3

После бесчисленных часов разочарования мне удалось все работать:

odbcinst.ini:

[FreeTDS]
Description = FreeTDS Driver v0.91
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
fileusage=1
dontdlclose=1
UsageCount=1

odbc.ini:

[test]
Driver = FreeTDS
Description = My Test Server
Trace = No
#TraceFile = /tmp/sql.log
ServerName = mssql
#Port = 1433
instance = SQLEXPRESS
Database = usedbname
TDS_Version = 4.2

freetds.conf:

[mssql]
host = hostnameOrIP
instance = SQLEXPRESS
#Port = 1433
tds version = 4.2

Первое тестовое соединение (mssql — это имя раздела из freetds.conf):

tsql -S mssql -U username -P password

Вы должны увидеть некоторые настройки, но никаких ошибок и только 1 > . Для выхода используйте выйти.

Затем пусть тест DSN/FreeTDS (test — это имя раздела из odbc.ini; -v означает подробный):

isql -v test username password -v

Вы должны увидеть сообщение Подключено!

Ответ 4

Ответ, потому что этот ответ появился сначала для поиска, когда у меня была такая же проблема:
[08S01] [unixODBC] [FreeTDS] [SQL Server] Не удается подключиться: Adaptive Server недоступен или не существует

Именованные экземпляры MSSQL должны быть правильно настроены без установки порта. (http://freetds.schemamania.org/userguide/freetdsconf.htm говорит, что экземпляр экземпляра или порт NOT BOTH)

freetds.conf

[Name]
host = Server.com
instance = instance_name
#port = port is found automatically, don't define explicitly
tds version = 8.0
client charset = UTF-8

И в odbc.ini только потому, что вы можете установить порт, НЕ ПРИНИМАЙТЕ, когда вы используете именованный экземпляр.

Ответ 5

У меня была такая же проблема, моя проблема заключалась в том, что брандмауэр на сервере не был открыт с текущего IP-адреса.

Ответ 6

Бад, отключите selinux или добавьте следующее на сервер RedHat/CentOS:

setsebool -P httpd_can_network_connect_db 1
setsebool -P httpd_can_network_connect 1

Лучше всего!

Источник

I’m running SQL Server 2016 sp1 on a Windows Server 2012 r2 box. Supposedly, unlike earlier versions of SQL Server, SQL Server 2016 is compatible with TLS 1.2 right out of the box…. Right? Anyway, everything in my application works fine until I disable TLS 1.0 and 1.1, then I get this error:

Could not open data connection. Error:
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SSL Security error

When I look at the SQL Server ODBC 11 drivers they only apply to SQL server 2014 or earlier releases. What gives? I’m unclear as to how to update ODBC to be TLS 1.1 & 1.2 compatible on SQL Server 2016 which isn’t even supposed to have this problem (after all, TLS 1.0 vulnerability is retro 2016 at this point) ?

SQL Server ODBC 11 Driver download: «Microsoft ODBC Driver 11 for SQL Server is a single dynamic-link library (DLL) containing run-time support for applications using native-code APIs to connect to Microsoft SQL Server 2005, 2008, 2008 R2, SQL Server 2012, SQL Server 2014 and Windows Azure SQL Database.»

Doesn’t make sense…what am I doing wrong? I’m using the standard SQL Server ODBC driver. Is that the problem? Perhaps I should be using the ODBC driver version 13 for SQL Server?

asked Jul 25, 2017 at 1:01

Michael BarberMichael Barber

1652 gold badges3 silver badges10 bronze badges

Ran into the same issue with SQL 2016 (though it was on a Server 2016 box). Couldn’t find a fix via ODBC drivers but found a Symantec article where a Backup Exec agent was unable to connect to the DB with TLS 1.0 disabled.

The suggested fix is enabling FIPS on the SQL box, this fixed it in our case but I couldn’t work out why. Here’s some guidance on how to enable FIPS — with the caveat that it isn’t recommended unless you need to comply with a regulatory or legislative directive. (I would provide links to articles that go into detail but apparently as I’m a newbie I only get to post two links. Googling «why you shouldn’t enable FIPS» should get you the articles in question)

answered Sep 4, 2017 at 9:24

Источник

Гость 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) Да

Источник

Вопрос

Ответы

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

Ответ 1

Ответ 2

Ответ 3

Ответ 4

Ответ 5

Ответ 6