Error code 0x2746

[miquelangeld]

Please check the FAQ (frequently-asked questions) first. If you have other questions or something to report, please address the following (skipping questions might delay our responses):

PHP version
8.1
PHP SQLSRV or PDO_SQLSRV version
latest
Microsoft ODBC Driver version
I tried with 17 and 18
SQL Server version
16
Client operating system
Ubuntu 22.04
Table schema

Problem description

I get Error code 0x2746 connecting to sql server. I'm using a docker environment (Laravel Sail)

Expected behavior and actual behavior
To connect to MssqlDatabase

Repro code or steps to reproduce

In my docker i installed following the documented procedure:

RUN curl -sS https://packages.microsoft.com/keys/microsoft.asc --output microsoft.asc
RUN apt-key add microsoft.asc

RUN curl https://packages.microsoft.com/config/ubuntu/22.04/prod.list > /etc/apt/sources.list.d/mssql-release.list
RUN apt-get update

RUN ACCEPT_EULA=Y apt-get install -y msodbcsql17 unixodbc-dev mssql-tools

RUN pecl install sqlsrv
RUN pecl install pdo_sqlsrv

RUN printf "; priority=20\nextension=sqlsrv.so\n" > /etc/php/8.1/mods-available/sqlsrv.ini
RUN printf "; priority=30\nextension=pdo_sqlsrv.so\n" > /etc/php/8.1/mods-available/pdo_sqlsrv.ini`

In the past I had the same issue and I followed the workaround described here
But now it's not working.

[v-makouz]

This error server refusing the connection for some reason, so if you have access to the server logs, they might be more informative. You've already tried fixing the TLS settings, but what is the version of OpenSSL? Sometimes that seems to be an issue.

[guillemmateos]

Experiencing the same exact issue. The workaround used to also work on Ubuntu 20.04, but not any more. I think this may be related to the update to OpenSSL v3.0, same as they saw in PHP here? php/php-src#8369

[guillemmateos]

@miquelangeld found a workaround for this. Turns out OpenSSL 3.0 changed the default behavior of SECLEVEL (you would think they would warn about this somewhere in capital case, as this is a breaking change essentially...) so you need to specify 0 now instead of 1. More info here: openssl/openssl#17476

[clarumedia]

I setup an Ubuntu 20.04 machine a few months ago with the Microsoft ODBC Driver 17 for SQL Server and all the changes needed for openssl and tls1 support. It worked great with sqlcmd.

A week ago, I tried setting up as above on Ubuntu 22.04 and hit the problem as above. So I setup a new Ubuntu 20.04 machine and I also could not get it to work, same as this:

https://stackoverflow.com/questions/74148553/ubuntu-22-04-sqlcmd-can-not-connect-to-ms-sql-server-2016/74319042#74319042

I tried the version 18 driver to no avail.

I finally got it to work by copying in libmsodbcsql-17.8.so.1.1 from the old Ubuntu 20.04 server to /opt/microsoft/msodbcsql17/lib64/ on the new Ubuntu 20.04 server and specifying it in /etc/odbcinst.ini instead of libmsodbcsql-17.10.so.1.1

I don't know if that's a production safe solution, but it certainly indicates where the problem lies. I haven't tried it on Ubuntu 22.04 either.

[guillemmateos]

@clarumedia I would suggest trying what I mentioned on my previous message. The 0x2746 error is almost always tied to TLS, and the way that works in Ubuntu 22.04 has changed.

Copying the library may work as it will potentially then use OpenSSL 1.1 instead of 3, but is not ideal for updates.

[clarumedia]

@guillemmateos I did make all the SSL changes you describe in your message (as I said in my comment). I had to make all those changes on the Ubuntu 20.04 server months ago to solve the 0x2746 back then . This is a new problem that must have started more recently and appears to be solved only with the older libmsodbcsql-17.8.so.1.1

[guillemmateos]

@clarumedia have you tried using:

MinProtocol = TLSv1

Instead of

MinProtocol = TLSv1.0 (taken from the stackoverflow post)

I had issues with using the latest, but the first one did work fine. As for Ubuntu 22.04 the key for me has been to set the SECLEVEL to 0 instead of 1.

My setup is working fine right now with Ubuntu 22.04 using that config and ODBC 17. It was also working with ODBC 18 if specifying -C for sqlcmd so it will accept the cert, as cert validation works different in ODBC 18.

[clarumedia]

@guillemmateos

This change in /etc/ssl/openssl.conf makes no difference for either ODBC 17 using libmsodbcsql-17.10.so.1.1 or ODBC 18

MinProtocol = TLSv1

However it DOES work using libmsodbcsql-17.8.so.1.1.
So does TLSv1.2

Which ODBC 17 driver is being used in your /etc/odbcinst.ini ?

[guillemmateos]

@clarumedia libmsodbcsql-17.10.so.1.1. I did also test with ODBC 18 and it was working fine.

[guillemmateos]

These 2 versions to be precise:

msodbcsql17 17.10.1.1-1
msodbcsql18 18.1.2.1-1

[clarumedia]

@guillemmateos

I just span up a completely new Ubuntu 22.04 server and tested.

#Install MS SQL Server tools
curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
curl https://packages.microsoft.com/config/ubuntu/22.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list
apt-get update
apt-get install -y mssql-tools unixodbc-dev

sqlcmd worked fine with only this change to /etc/ssl/openssl.cnf

[system_default_sect]
MinProtocol = TLSv1.2
CipherString = DEFAULT:@SECLEVEL=0

Also, an Ubuntu 22.04 machine which had been distro upgraded from Ubuntu 20.04 worked with this change as well.

Looks like it's actually an issue with Ubuntu 20.04 after apt-get upgrades have been run.

[guillemmateos]

@clarumedia glad to hear it's working fine now for you also. For me really the key, as mentioned, was the SECLEVEL=0 which did change on OpenSSL 3 (which Ubuntu 22.04 ships with) from what it was on previous versions.

Hopefully the notes here can help other people fix the issue.

[clarumedia]

@guillemmateos The only way I could solve the problem on Ubuntu 20.04 after running apt-get upgrade was to revert to libmsodbcsql-17.8.so.1.1. Thankfully I already had a copy of it but others will lose it in Ubuntu 20.04 after running updates. Would it be possible for MS to post a copy of it on here or in the relevant place ?

Sounds like this StackOverflow question could do with some help ?
https://stackoverflow.com/questions/74148553/ubuntu-22-04-sqlcmd-can-not-connect-to-ms-sql-server-2016/74319042?noredirect=1#comment131267852_74319042

[v-makouz]

@clarumedia If you need to get Driver 17.8.1.1 for Ubuntu, you can get a package here:
https://packages.microsoft.com/ubuntu/18.04/prod/pool/main/m/msodbcsql17/msodbcsql17_17.8.1.1-1_amd64.deb

Note: it is forward compatible, so it should work on all Ubuntu versions 18.04 and up.

[miquelangeld]

@guillemmateos

This change in /etc/ssl/openssl.conf makes no difference for either ODBC 17 using libmsodbcsql-17.10.so.1.1 or ODBC 18

MinProtocol = TLSv1

However it DOES work using libmsodbcsql-17.8.so.1.1. So does TLSv1.2

Which ODBC 17 driver is being used in your /etc/odbcinst.ini ?

Hi, in my setup its 17

[miquelangeld]

e

@miquelangeld found a workaround for this. Turns out OpenSSL 3.0 changed the default behavior of SECLEVEL (you would think they would warn about this somewhere in capital case, as this is a breaking change essentially...) so you need to specify 0 now instead of 1. More info here: openssl/openssl#17476

I downgraded to openssl 1.0 And same problem. I don't know it its something related with the laravel docker image. I'm looking in to it

[miquelangeld]

e

@miquelangeld found a workaround for this. Turns out OpenSSL 3.0 changed the default behavior of SECLEVEL (you would think they would warn about this somewhere in capital case, as this is a breaking change essentially...) so you need to specify 0 now instead of 1. More info here: openssl/openssl#17476

I downgraded to openssl 1.0 And same problem. I don't know it its something related with the laravel docker image. I'm looking in to it

found a workaround for this

Sorry @guillemmateos I did not read your post carefully in the first place. Indeed your workaround works perfectly. You saved my life. Now I can still use docker in my development environment. I close the issue