Error: TCP Provider: Error code 0x2746. During the Sql setup in linux through terminal
Sql ServerLinuxTcpclientSql Server Problem Overview
I am trying to setup the ms-sql server in my linux by following the documentation https://docs.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.
Sql Server Solutions
Solution 1 - Sql Server
[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:
-
Stop SQL Server
sudo systemctl stop mssql-server
-
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.
-
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
Solution 2 - Sql Server
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
Solution 3 - Sql Server
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
Solution 4 - Sql 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
Solution 5 - Sql Server
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.
Solution 6 - Sql Server
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.
Solution 7 - Sql Server
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
-
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
-
Check available versions of MS SQL in repository
ijin -> apt-cache policy mssql-server
-
Installed custom MS SQL
sudo apt-get install mssql-server=15.0.1600.8-1 mssql-tools
-
Setup
sudo /opt/mssql/bin/mssql-conf setup
-
Mem limit, server agent
sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true sudo /opt/mssql/bin/mssql-conf set memory.memorylimitmb 3072
-
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.
Solution 8 - Sql Server
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
Solution 9 - Sql Server
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
Solution 10 - Sql Server
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/
Solution 11 - Sql 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
Solution 12 - Sql Server
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'
Solution 13 - Sql Server
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.
Solution 14 - Sql 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.
Solution 15 - Sql Server
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://docs.microsoft.com/en-us/sql/linux/sql-server-linux-troubleshooting-guide?view=sql-server-ver15#connection
Solution 16 - Sql Server
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>
Solution 17 - Sql Server
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.
Solution 18 - Sql Server
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