Msphpsql: Connection Lost with encryption

[jansor]

PHP Driver version or file name

Upgrade from 5.6.0 to SQLSRV :5.6.1

SQL Server version

SQL server 2016 v14.00.2014

Client operating system

Windows 10 Pro
Windows server 2016

Webserver

IIS

PHP version

PHP 7.3.3 and PHP 7.3.6

Microsoft ODBC Driver version

Upgrade from 17.2.x to ODBC Driver msodbcsql17.dll | 03.80 | 17.03.0001 | msodbcsql_17.3.1.1_x64

Problem description

Begin SQL DUMP Errors:
SQLSTATE: 08001 code: 21 message: [Microsoft][ODBC Driver 17 for SQL Server]Encryption not supported on the client.
SQLSTATE: 08001 code: 21 message: [Microsoft][ODBC Driver 17 for SQL Server]Client unable to establish connection
SQLSTATE: 08001 code: 0 message: [Microsoft][ODBC Driver 17 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
End SQL DUMP Errors:

Expected behavior and actual behavior

Dont lose connection

Repro code or steps to reproduce

• I cannot reproduce yet, i need to do more research going through the event logs.

Explanation

I have updated the SQLSRV and ODBC driver to the latest versions.
After i upgraded some of the drivers to the latest version i get very strange behaviors.
Sometimes my SQL connection get above mentioned errors.
We get the same issues on the Beta testing setup where IIS servers are separated from the SQL server.
The first time we got this messages was under the beta server, sow initially i think this is connection issue between servers, firewall or user right settings.

Now we got the same issues on the Laptop setup, on our laptop this is the same device with IIS and SQL server installed.
Once we get the issue and reload the website with F5 several times then the connection is working again, or when we do IIS reset it also works again and keeps on working for a longer time.

The explained error mentioned also about encryption, but we dont have this enabled yet at all.

I do not know if this is an issue under ODBC and or SQLSRV, i will try to downgrade back to previous version this weekend.

Anyway, is this a know issue? or does someone else is encountering this same issue?

[jansor]

Small update on above mentioned issue, when i revert the SQLSRV driver to version 5.6.0 It seems to be resolved. At this moment thats the only change i did to test the issue is removed or it still happens.

[yitam]

Hi @jansor

What's your typical connection string? Encryption is not the same as enabling Always Encrypted. In your connection string, if Encrypt is true, the communication with SQL Server is encrypted (false by default).

Note that v5.6.1 is a hotfix, mainly some minor fixes for v5.6.0. Thus, it will be helpful to provide one or more ODBC trace(s). To enable tracing, please turn on the TraceOn flag.

[yitam]

Please ignore my previous question re SQL Server version. I found it later in your original question. Anyway, I'm not sure if this is related to the problem(s) you reported:
https://support.microsoft.com/en-ca/help/3135244/tls-1-2-support-for-microsoft-sql-server

[kolajo]

Hi, this same error happened to me just last Friday, my error is PDOException: SQLSTATE[08001]: [Microsoft][ODBC Driver 17 for SQL Server]Encryption not supported on the client.

I have I'm still trying to solve this as we speak.

[yitam]

@kolajo please see my replies above to @jansor
Other than providing ODBC trace(s), it will help us investigate if you can tell us your repro settings or whether this happens to you always or intermittently, etc.

[jansor]

Small, update on the several laptop of the develop the issue is disepeared after we went back to version 5.6.0 After today i will ask several develop to try update back to version 5.6.1 and see if the issues come back.
Until now the issues did not return yet.

[yitam]

Thanks @jansor. If for any reason the problem happens again please try to get an ODBC trace for us or at least show us an example of your typical connection string. See my first reply above.

[jansor]

Final Update, for now, we had the same issue one more time. After reading the Link about the TSL1.2 we checked several event logs where th issue happend on and found several event related with TSL 1.2

I think the issue started after we did windows updates on some of the server.
Anyway for other poeple who run into the same issue we have applied a register update to make sure that TSL1.0 and TSL1.1 are disabled:

Windows Registry Editor Version 5.00 [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2] [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client] "DisabledByDefault"=dword:00000000 "Enabled"=dword:00000001 [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Server] "DisabledByDefault"=dword:00000000 "Enabled"=dword:00000001 [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0] [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Client] "DisabledByDefault"=dword:00000001 "Enabled"=dword:00000000 [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Server] "DisabledByDefault"=dword:00000001 "Enabled"=dword:00000000 [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1] [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Client] "DisabledByDefault"=dword:00000001 "Enabled"=dword:00000000 [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Server] "DisabledByDefault"=dword:00000001 "Enabled"=dword:00000000

Aftere we applied above Register edit, we did not have the issue returned at all.

For now i consider this Topic closed

[DubiousTrout]

FWIW I was having the same issue and adding the registry entries worked for me also. Thanks for getting me out of a hole!

Windows Registry Editor Version 5.00 [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2] [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client] "DisabledByDefault"=dword:00000000 "Enabled"=dword:00000001 [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Server] "DisabledByDefault"=dword:00000000 "Enabled"=dword:00000001 [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0] [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Client] "DisabledByDefault"=dword:00000001 "Enabled"=dword:00000000 [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Server] "DisabledByDefault"=dword:00000001 "Enabled"=dword:00000000 [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1] [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Client] "DisabledByDefault"=dword:00000001 "Enabled"=dword:00000000 [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Server] "DisabledByDefault"=dword:00000001 "Enabled"=dword:00000000

[jchertudi]

While issue is closed, I wanted to add my similar observations, since the TLS 1.2 fix has not helped me.

We have PHP application on three servers, different Windows generations. They have been communicating with SQL 2008, but we are migrating to SQL 2017.
With SQL 2017, we have enabled "Force Encryption" in SQL Server Configuration Manager, and supplied a certificate. Usually, this is working well.

Three configured servers are all running IIS

• Windows 2008R2, ODBC driver 11 and 17 installed, SQL Native Client 2008 and 2012 installed, php 7.2 using sqlsrv 5.6.1+11919
• Windows 2012, ODBC driver 17 only, php 7.2 using sqlsrv 5.3.0+11108
• Windows 2019, ODBC driver 17 only, php 7.2 using 5.6.1+11919

Once we moved our databases on SQL2017 server, all three server will get the error "Encryption not supported on the client" after running OK for undetermined amount of time.

When error occurs, if I kill the running php-cgi processes on the web server, the next request will launch a new php-cgi, and request works. I don't have to recycle Application Pools.

Yesterday I used the registry settings from @jansor (thanks) to my 3 web servers, and the lone SQL 2017 server, and rebooted them all, but these errors continue today.
We observe some TLS errors in Event Viewer on the Windows 2019 web server
A fatal error occurred while creating a TLS client credential. The internal error state is 10013.

Our connect string uses sqlsrv_connect, and provides a few parameters: UID, Password, Database, and LoginTimeout=1
We did not change connection string in migration from non-encrypted SQL 2008 to Force Encryption SQL 2017, which surprised me.
I have started an ODBC trace, and after all requests succeeded for ~6 hours, I captured some errors (attached).
This file is ~327KB, but I had over 80MB of success before the errors started.

Does this provide any clue, or area to research further?
odbc_w_error.txt

[yitam]

Please answer the following questions to help us start investigating, @jchertudi :

1. What's your typical connection string? Did you specify Encrypt and/or TrustServerCertificate ?
2. Did you mean all three Windows Server have enabled TLS 1.2? Did you upgrade your clients? Did you mean all three could connect initially for a certain time but they will fail sooner or later?
3. What is your ODBC driver version?

[jchertudi]

thank you @yitam for fast response.

All three web servers have had TLS 1.2 enabled via registry.
When you say "upgrade clients", I expect in the case of web server connecting to SQL 2017, the web server is the client, correct?

All three web servers have ODBC driver 2017.173.01.01

Exact text of connection string is obscured in php code (I'm server person, not developer), but we use sqlsrv_connect and specify:
UID, Password, Database, and LoginTimeout=1
plus we add trace code yesterday

We did not enable Encrypt or TrustServerCertificate... but since it normally works, I assumed the driver was handling the Force Encryption requirement.

When web server starts, and php request is made, it works. After some amount of time (we logged over 80MB of trace before an error) the error starts. Then, I must kill the waiting php-cgi in order for a new php-cgi to start, which will succeed again, for some time

[yitam]

hi @jchertudi , while investigating, I found that

A fatal error occurred while creating a TLS client credential. The internal error state is 10013.

The error 10013 may indicate SChannel configuration issues. Have you looked into that?

[jchertudi]

Last observation from me, I had to downgrade to ODBC 13.1 to stop these errors.
Setting our servers to use only TLS 1.0, 1.1 and 1.2 did not help.
Disabling "Force Encryption" on SQL server changed the error message, but did not stop connection failure.
We could not resolve infrequent errors with ODBC 17.3 or 17.4
When my web server received a connection error, php would not even attempt to make a network request to remote SQL server, the attempt failed between php and ODBC.
So, it appears the php sqlsrv driver is working fine!
Thanks for replies

[yitam]

@jchertudi it would help us to understand the real problem(s) if you can provide some traces or details. ODBC driver 17.* should have fixed many connection issues, not the other way around. We would like to investigate, which would need your help, if possible.

[yitam]

FYI, we have communicated with ODBC team, @jchertudi
They suspected secur32.dll can't be loaded from system32 or any schannel calls to initialize client context failed. They saw similar messages when linked servers were used, but that was related to secur32.dll load problems. It should have been fixed in 17.3.1 and shouldn't happen under non linked servers environment.

[Gshaal]

I am facing the same error :

Error:SQLSTATE[08001]: [Microsoft][ODBC Driver 17 for SQL Server]Encryption not supported on the client.

Tried all the above solutions and it is still happening can someone help please

[yitam]

@Gshaal please provide some more details. See my comment above for an example. Also, are you using WIndows or Linux?

[cnyyk]

Have same issue on a refresh installation of Windows Server 2008 R2 with PHP7.3.

Found a link: https://www.centrel-solutions.com/media/xiaconfiguration/adminguideweb/EncryptionNotSupportedClient.html

Encryption not supported on the client
Symptoms

When installing or upgrading the XIA Configuration Server you see the error



[Microsoft][ODBC Driver 17 for SQL Server]Encryption not supported on the client.

[Microsoft][ODBC Driver 17 for SQL Server]SQL Server Network Interfaces: The parameter is incorrect.



Cause

This can occur when performing the installation on Windows Server 2008 R2 where TLS 1.0 is disabled and the latest security patches are not installed on the machine.



Resolution

Ensure that the latest patches from Windows Update are installed on the machine.

Now is under windows updating.

[dileepkumarmudrakola]

Currently iam using SQL Server Native Client 11.0, it is working fine.

But I want to connect to Database by latest driver. So While creating DSN....Iam getting this error

Microsoft ODBC Driver for SQL Server Version 17.03.0001

Running connectivity tests...

Attempting connection
[Microsoft][ODBC Driver 17 for SQL Server]Encryption not supported on the client.
[Microsoft][ODBC Driver 17 for SQL Server]SQL Server Network Interfaces: The parameter is incorrect.

[Microsoft][ODBC Driver 17 for SQL Server]Client unable to establish connection
[Microsoft][ODBC Driver 17 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.

How to resolve this

[yitam]

@dileepkumarmudrakola please create a new issue and provide more details:

1. your typical connection string?
2. the version of your sql server
3. your operating system
   etc.

[creativelogix]

I just restarted IIS server and it fixed the issue.