Error: TCP Provider: Error code 0x2746. During the Sql setup in linux through terminal

Question

I am trying to setup the ms-sql server in my linux by following the documentation https://learn.microsoft.com/pl-pl/sql/linux/quickstart-install-connect-ubuntu?view=sql-server-2017

The SQL server status is Active (Running).

I am getting the following error while executing the command

sqlcmd -S localhost -U SA -P '<YourPassword>'

Error:

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : TCP Provider: Error code 0x2746. Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Client unable to establish connection.

I also tried by giving the command

sqlcmd -S 127.0.0.1 -U SA -P '<YourPassword>' 

But the same error is displayed. When I tried the wrong password it also displays the same error.

Answer 1

[UPDATE 17.03.2020: Microsoft has released SQL Server 2019 CU3 with an Ubuntu 18.04 repository. See: https://techcommunity.microsoft.com/t5/sql-server/sql-server-2019-now-available-on-ubuntu-18-04-supported-on-sles/ba-p/1232210 . I hope this is now fully compatible without any ssl problems. Haven't tested it jet.]

Reverting to 14.0.3192.2-2 helps.

But it's possible to solve the problem also using the method indicated by Ola774, not only in case of upgrade from Ubuntu 16.04 to 18.04, but on every installation of SQL Server 2017 on Ubuntu 18.04.

It seems that Microsoft now in cu16 messed up with their own patch for the ssl-version problems applied in cu10 (https://techcommunity.microsoft.com/t5/SQL-Server/Installing-SQL-Server-2017-for-Linux-on-Ubuntu-18-04-LTS/ba-p/385983). But linking the ssl 1.0.0 libraries works.

So just do the following:

1. Stop SQL Server

   sudo systemctl stop mssql-server 
   

2. Open the editor for the service configuration by

   sudo systemctl edit mssql-server 
   

This will create an override for the original service config. It's correct that the override-file, or, more exactly "drop-in-file", is empty when used the first time.

3. In the editor, add the following lines to the file and save it:

   [Service]
   Environment="LD_LIBRARY_PATH=/opt/mssql/lib" 
   

4. Create symbolic links to OpenSSL 1.0 for SQL Server to use:

   sudo ln -s /usr/lib/x86_64-linux-gnu/libssl.so.1.0.0 /opt/mssql/lib/libssl.so 
   sudo ln -s /usr/lib/x86_64-linux-gnu/libcrypto.so.1.0.0 /opt/mssql/lib/libcrypto.so 
   

5. Start SQL Server

   sudo systemctl start mssql-server 
   

Answer 2

If you are having issues with the client on Debian 10 with OpenSSL1.1.1 the fix is to revert to the previously default weaker key length. To do so:

Modify /etc/ssl/openssl.cnf config file as follows (fyi see known issues with OpenSSL 1.1.1 in Debian 10 below):

Change the last line from CipherString = DEFAULT@SECLEVEL=2 to CipherString = DEFAULT@SECLEVEL=1

https://github.com/microsoft/msphpsql/issues/1021

https://wiki.debian.org/ContinuousIntegration/TriagingTips/openssl-1.1.1

Answer 3

sudo apt-get install mssql-server=14.0.3192.2-2

Reverting to this version worked for me.

My scenario was a fresh install (everything latest version) on Ubuntu Server 18.04.2 receiving the client connection error from sqlcmd:

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : TCP Provider: Error code 0x2746

Answer 4

Simply:

TCP Provider: Error code 0x2746

This is likely a problem with openssl vs. sql-server protocol/version.

Check your openssl version. Run the following command on your terminal openssl version:

$ openssl version
OpenSSL 1.0.2k-fips  26 Jan 2017

If your openssl version is not 1.0, then you may want to solve the connection problem by one of the following options:

Option 1: Workaround your openssl

sed -i -E 's/(CipherString\s*=\s*DEFAULT@SECLEVEL=)2/\11/' /etc/ssl/openssl.cnf

Yes, it is .cnf.

This command changes your SECLEVEL to 1, if you have it in your /etc/ssl/openssl.cnf file. Done.

Option 2: Downgrade openssl.

If your openssl version is 1.1, you would probably like it to be 1.0. This method is basic: download the source code, configure and make the binary. It may take few minutes to build everything:

cd /usr/local/src/
wget https://www.openssl.org/source/old/1.0.1/openssl-1.0.1k.tar.gz
tar -xvf /usr/local/src/openssl-1.0.1k.tar.gz
cd /usr/local/src/openssl-1.0.1k
./config --prefix=/usr/local/ --openssldir=/usr/local/openssl
make
make test
make install
mv /usr/bin/openssl /usr/bin/openssl-bak

then

cp -p /usr/local/openssl/bin/openssl /usr/bin/openssl

or

cp -p /usr/local/ssl/bin/openssl /usr/bin/openssl
ll -ld /usr/bin/openssl
openssl version

Leave comments if you need insights for something special: docker image, or different system, etc.

Answer 5

Upgrade from Ubuntu 16.04 to 18.04 still results in some issues

A few systems may require version 1.0 of the OpenSSL libraries to connect to SQL Server. Using OpenSSL 1.0 can be done as follows:

Stop SQL Server

sudo systemctl stop mssql-server

Open the editor for the service configuration

sudo systemctl edit mssql-server

In the editor, add the following lines to the file and save it:

[Service]
Environment="LD_LIBRARY_PATH=/opt/mssql/lib"

Create symbolic links to OpenSSL 1.0 for SQL Server to use

sudo ln -s /usr/lib/x86_64-linux-gnu/libssl.so.1.0.0 /opt/mssql/lib/libssl.so
sudo ln -s /usr/lib/x86_64-linux-gnu/libcrypto.so.1.0.0 /opt/mssql/lib/libcrypto.so

Start SQL Server

sudo systemctl start mssql-server

I hope this helps

Answer 6

You can either roll back to the previous version with the command sudo apt-get install mssql-server=14.0.3192.2-2 or keep the new version by following MSSQL_Ubuntu's answer.

Also disable the updates on the mssql-server package:

sudo apt-mark hold mssql-server

This will not prevent you to update it manually when you wish so.