Msphpsql: Randomly getting error code 0x2746 on connect

[matthenning]

## PHP Driver version or file name

5.8.0

## SQL Server version

2016 Enterprise SP2
2012 Enterprise SP4 (Availability Group)

## Client operating system

RHEL 7.5

## PHP version

PHP 7.2.27

Microsoft ODBC Driver version

msodbcsql17-17.5.1.1
Also occured with 17.4

Problem description

The connection randomly fails with error code 0x2746.
An estimated 5% of connections fail, the rest succeeds.
This problem started with the last wave of Windows updates, though we weren't able to pinpoint the exact update.

Debug:

sqlsrv.LogSubsystems = -1
sqlsrv_connect: entering
sqlsrv_connect: SQLSTATE = 08001
sqlsrv_connect: error code = 10054
sqlsrv_connect: message = [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x2746
sqlsrv_connect: SQLSTATE = 08001
sqlsrv_connect: error code = 10054
sqlsrv_connect: message = [Microsoft][ODBC Driver 17 for SQL Server]Client unable to establish connection
sqlsrv_errors: entering

PHP_RSHUTDOWN for php_sqlsrv: entering

[yitam]

Hi @matthenning, there are different reasons why the driver fails to connect to SQL Server, as you can see in this support article. It also depends on the server or cpu usage and whether your sql server is on premise or in the cloud, etc.

In short, we can only guess from our end as we won't be able to replicate the problem. Please provide more details.

[matthenning]

Over the weekend we found the issue also exists with other encrypted connections to Windows systems so I suspect an underlying issue with different TLS versions. At least that's what the Windows event log suggests.

I'll update the issue when I get new information as soon as I can verify them.

[yitam]

Any update, @matthenning ?

[matthenning]

Still waiting for results from another team.
Might be related to the network driver of the AV solution, but nothing confirmed yet.

[yitam]

Any update, @matthenning ?

[matthenning]

We're currently looking into it with our Windows infrastructure department.
Will definitely update as soon as we got something.

[matthenning]

As of now it looks like the problem does not occur on Windows Server 2016 but only on 2012 R2. Maybe on older versions, too, but I cannot verify that.

[matthenning]

It looks like changing the cipher suite on Windows Server 2012 R2 solves the issue, though we're still in the process of confirming these findings.

From the openssl trace:

Broken:  cipher_suite {0x00, 0x9F} TLS_DHE_RSA_WITH_AES_256_GCM_SHA384
Working: cipher_suite {0xC0, 0x28} TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384

[v-chojas]

Intermittent connections with DHE ciphersuites was a known issue several years ago: https://docs.microsoft.com/en-us/archive/blogs/dataaccesstechnologies/intermittent-jdbc-connectivity-issue-the-driver-could-not-establish-a-secure-connection-to-sql-server-by-using-secure-sockets-layer-ssl-encryption-error-sql-server-returned-an-incomplete-respons

If you could see which update changed the cipher suites that would probably be the culprit.

[matthenning]

It sure sounds like it @v-chojas
The strange thing is that the problem also occurs when using the latest ODBC driver and also when making e.g. HTTPS connections.
Maybe the workaround is only implemented in JDBC.

[v-chojas]

The workaround is to disable the DHE ciphersuites, so that it will use ECDHE instead.

[ZackInMA]

I'm seeing this as well. Server is set with the DH key exchange disabled, TLS 1.0 and 1.1 disabled, and TLS_DHE ciphers disabled. Only TLS_ECDHE ciphers are enabled.

Windows Server 2012 R2.

[David-Engel]

Related:
https://support.microsoft.com/en-us/help/4557473/errors-when-applications-try-to-connect-to-sql-server-in-windows

Apparently this has been fixed in the latest Windows 10 and Windows Server 2016 updates.

[matthenning]

I guess we can close this.
Solution is, as previously mentioned, to update the cipher suite on OS versions lower than 2016.
Thanks everyone

[ZackInMA]

I guess we can close this.
Solution is, as previously mentioned, to update the cipher suite on OS versions lower than 2016.
Thanks everyone

I don't agree that it should be closed considering people are having the issue EVEN WITH the applicable cipher suites enabled. But hey - I guess that's par for the MS course... Don't try to replicate it in a lab or anything... Don't engage or reach out to others who have experiences contrary to the proposed solution. Just close the issue...
/unimpressed.

[matthenning]

You realize I'm not with Microsoft but the issue creator, right?
Besides, since this issue is related to cipher suites this would be the wrong place to address it anyway.

[v-chojas]

You may get more relevant answers here -> https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home