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

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.

81476 次浏览
小开

Same problem. It's awful because im in dev now and that "great" update just killing my working time.

Update: MS SQL version rollback helped me, but unfortunately I have to remove all my data. Thanks that it was my dev machine. All notes below tested on

ijin -> lsb_release -a
No LSB modules are available.
Distributor ID: LinuxMint
Description:    Linux Mint 19 Tara
Release:        19
Codename:       tara

1) I've remover MS SQL and its data

sudo rm -rf /var/opt/mssql
sudo apt-get purge mssql-server mssql-tools
sudo apt-get autoremove
sudo apt-get autoclean

2) Check available versions of MS SQL in repository

ijin -> apt-cache policy mssql-server

3) Installed custom MS SQL

sudo apt-get install mssql-server=15.0.1600.8-1 mssql-tools

4) Setup

sudo /opt/mssql/bin/mssql-conf setup

5) Mem limit, server agent

sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true
sudo /opt/mssql/bin/mssql-conf set memory.memorylimitmb 3072

4) Restart, status

sudo service mssql-server restart
sudo service mssql-server status

Probably there is some issues with interaction of openssl package and updated MS SQL, I can't find if it is true or not, but googled a few notes about it. So you can use

apt-cache policy openssl
sudo apt-get install openssl=<version>
openssl version

To change openssl version and try to connect.

小开

After 2 days working on this problem I've finally solved it! In my case, I am using Fedora 28, so for those using RHEL, I followed this tutorial:

Installing Microsoft SQL Server on Red Hat Enterprise Linux 8 Beta

So, are you using Python 3? Apparently, you need to switch to Python 2 before installing it, using the following code (I guess in Ubuntu would work as well):

sudo alternatives --config python

Create a repository from https://packages.microsoft.com/config/rhel/7/mssql-server-2017.repo

Download it, then install it without resolving dependencies.

After that, you can run the setup for mssql-conf:

sudo /opt/mssql/bin/mssql-conf setup

And continue the Microsoft documentation tutorial from that step.

Microsoft Tutorial for installing SQL Server 2017 on RHEL
Microsoft Tutorial for installing SQL Server 2017 on Ubuntu

Note: I read in some forums that SQL Server 2019 may be causing that problem, so I recommend installing the 2017 version.

小开

Updated SQL Server to the version 14.0.3223.3-15 (Ubuntu 18.04.2 LTS) today and got exactly the same issue for both local and remote connections. Rolling back to the previous version (14.0.3192.2-2 in my case) worked for me:

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

List versions installed on your machine:

apt-cache policy mssql-server

小开

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

小开 
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

小开

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.

小开

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

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

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

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

  3. Start SQL Server 

    sudo systemctl start mssql-server
小开

I got the same issue.

My OS is Ubuntu 18.10

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

Then, in my case, I could not enter my SQL server because I got the below message

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login failed for user 'sa'. Reason: Server is in script upgrade mode. Only administrator can connect at this time..

Then, I followed the ServerFault Answer

Each command took a process for a while in my case.

小开

I had the very same issue from within a docker container, I had to downgrade msodbc, mssql-tools and lib ssl:

RUN ACCEPT_EULA=Y apt-get install msodbcsql17=17.3.1.1-1 mssql-tools=17.3.0.1-1 -y
RUN wget http://security.debian.org/debian-security/pool/updates/main/o/openssl1.0/libssl1.0.2_1.0.2s-1~deb9u1_amd64.deb \
&& dpkg -i libssl1.0.2_1.0.2s-1~deb9u1_amd64.deb

Now it works like a charm.

Just in case, older versions of ms odbc driver and tools can be found here: https://packages.microsoft.com/debian/9/prod/pool/main/m/

For openssl: http://security-cdn.debian.org/debian-security/pool/updates/main/o/openssl1.0/

小开

After trying a few solutions, I found this:
https://www.youtube.com/watch?v=mfLbCarRzpg

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


sudo systemctl stop mssql-server
sudo systemctl edit mssql-server

Add these lines:

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

Then restart the server:

sudo systemctl start mssql-server
小开

I encountered the same issue with SQL Server 2019 (RTM - 15.0.2000.5) on Debian 10, both trying to connect locally with sqlcmd or remote through SSMS.

The same resolution as mentioned above was able to resolve this for me. My OpenSSL version was 1.0.2, this should be adjusted for whichever version is available.

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


sudo systemctl restart mssql-server

Now both local and remote connections work OK

小开

At this moment , only this version is working, looks like problem in openssl .

Working on Debian 9 .

Before anything be sure that you have valid backup of DB.

You need to purge mssql-server

apt-get remove --purge mssql-server

and after that check and delete /var/opt/mssql and /opt/mssql .

Next use this version 15.0.1700.37-2

apt-get install mssql-server=15.0.1700.37-2

after config you should be able to connect to mssql server 2019 at localhost or 127.0.0.1

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

小开

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

小开

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.

小开

Update 2021, Fedora 34, Sql Server 2019.

If you still have that error you need to execute the following command:

dnf install openldap-compat

Or check what is actually happening in console by starting the mssql in a single mode:

sudo -u mssql /opt/mssql/bin/sqlservr -m

More info you can find here: https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-troubleshooting-guide?view=sql-server-ver15#connection

小开

I don't recommend down grading openssl any more since you should be able to fix any openssl issue you have by upgrading to a new version. In my case openssl 1.1.1k worked. The easiest way to install an openssl version not in your distro is to install from source. For full details see https://wiki.openssl.org/index.php/Compilation_and_Installation

For me the following steps were sufficient but I assume you will also need build-essentials and a few other packages.

git clone git://git.openssl.org/openssl.git
cd openssl/
git checkout OpenSSL_1_1_1k
chmod +x ./config
./config
make
sudo make install

However this will not entirely solve the problem anymore. sqlcmd seems to have a DNS resolution bug that is not in older versions. That means you need the full domain name or ipaddress plus connection protocol or port number may also be required. So while before things like sqlcmd -S 127.0.0.1 or sqlcmd -S <server_name> worked just fine. Now you may need something like sqlcmd -S tcp:127.0.0.1,<port_number> or sqlcmd -S tcp:<server_name>.<AD domain>.<domain name>,<port_number>

小开

On my side, the problem was caused by a mounting issue. I found the solution here: https://github.com/microsoft/mssql-docker/issues/603#issuecomment-652958304

For some reason, if you map /var/opt/mssql/, and not only /var/opt/mssql/data, it fails on a Windows filesystem. There is no problem doing that on a Linux filesystem.

小开

If your using multiple php versions, please remove all unwanted versions

sudo apt-get purge php5.*
sudo apt-get purge php5.6  #specific version

restart apache2 or nginix server restart php sudo service php7.4-fpm restart

小开

After 2 days of struggle in which, as suggested by various sources, I tried to:

  • downgrade openssl from 3 to 1
  • downgrade Microsoft ODBC Driver 18 to 17
  • update sql 2008 r2 SP3 with TLS support
  • change MinProtocol = TLSv1 and CipherString = DEFAULT@SECLEVEL=1
  • set protocol registry in windows to accept TLS1.0-TLS1.3

without any success:

  • error:0A000102:SSL routines::unsupported protocol]
  • error:0A0C0103:SSL routines::internal error]
  • TCP Provider: Error code 0x2746 (the last one after all the changes)

I finally found out a solution installing a lower version of ODBC Driver 17:

libmsodbcsql-17.10.so.1.1 (DIDN'T WORK) -> libmsodbcsql-17.6.so.1.1 (WORKS)

It seems that libmsodbcsql-17.10 doesn't work with sql 2008 r2 instead libmsodbcsql-17.6 does


提交答案