82

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.

Mike G
  • 4,232
  • 9
  • 40
  • 66
Suba Nandhini K
  • 821
  • 1
  • 6
  • 3
  • From SSMS, this manifests as an error 10054, which is of course 0x2746 in decimal. Thanks for posting this question... you've helped a ton of people. – Lynn Crumbling Dec 11 '19 at 22:51
  • This answer simplifies what you need to do: https://stackoverflow.com/a/61111267/9535070 – ofundefined Apr 08 '20 at 22:55

21 Answers21

122

[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
MSSQL_Ubuntu
  • 1,321
  • 1
  • 5
  • 4
  • 6
    systemctl edit mssql-server was empty. I don't know if that was to be expected. Adding the Environment path and creating symlinks resolved my issue. mssql-server: Installed: 14.0.3223.3-15 – stinkyjak Aug 15 '19 at 20:20
  • Thanks! This solution works for mssql-server version 15.0.1900.25-1. – SergeyYu Aug 27 '19 at 10:37
  • Savior! Confirmed working on Ubuntu 18.04 LTS with mssql-server version 14.0.3223.3-15 – Panki Aug 28 '19 at 13:10
  • 3
    For me (Ubuntu 18.04) in step 2 run "sudo nano /lib/systemd/system/mssql-server.service" to edit the service config. Otherwise the config is emtpy like @stinkyjak wrote. Additionally you have to run "sudo systemctl daemon-reload" before step 5. – Hunv Sep 02 '19 at 18:25
  • 1
    In debian 9 I had to do the same, but pointing symbolic links to libssl.so.1.0.2 and libcrypto.so.1.0.2. Thank you! – BigBother Sep 11 '19 at 08:29
  • This works for us too. But is this a good long term solution? Wondering about the impact of these changes for future upgrades of SQL server and the ssl libraries. – Max Sep 21 '19 at 21:31
  • @John: I don't know anything about the future, so, I cannot guarantee that Microsoft or Openssl will not destroy this workarround. By desing, the override created with "systemctl edit ...." will survive the next mssql update. Therefor it will be up to you to check if the override will cause problems (I do not beleve so) and if it is still nescessary after the next update. I hope that Microsoft will address the problem in the next update and the override won't be nescessary anymore, so you may try to delete the added line (using sudo systemctl edit mssql-server again) and check if it will work. – MSSQL_Ubuntu Sep 22 '19 at 09:59
  • @stinkyjak: "systemctl edit mssql-server" creates an override, it's correct, that this override file is normally empty if used the first time. I would not recommend the method indicated by Hunv. This will edit the original service config, and eventually will be overwritten by updates in the future. – MSSQL_Ubuntu Sep 23 '19 at 07:34
  • You're Genius!!! Working on Ubuntu 16.04 with mssql-server version 15.0.1900.25-1. – shyammakwana.me Oct 20 '19 at 07:49
  • 4
    I just installed SQL Server 2019 (15.0.2000.5) on Ubuntu 18.04.3 and what is listed above is still the answer – Lance Perry Nov 11 '19 at 13:50
  • ubuntu 19.04. sql server 2019. just needed to do #4 then restart! thank you. – sam yi Nov 14 '19 at 02:12
  • ubuntu 19.10 with obdc driver 17 just needed to do the symbolic links (#4) and restart – Josh Sharkey Dec 17 '19 at 16:07
  • Thanks! This solution works for me on Ubuntu 18.04 LTS – Ayoub Anbara Dec 31 '19 at 13:41
  • Yesss this worked for me on Ubuntu 18.04. I first followed the tutorial to install sqlsrv on ubuntu 18.04 here https://computingforgeeks.com/how-to-install-ms-sql-on-ubuntu/ and then came to this post once my password output the OP's error. Once I followed the instructions in this answer... my same admin password I set up worked!! – CodeConnoisseur Jan 08 '20 at 15:28
  • this is really solved my problem on Ubuntu18.04 with SQL Server 2017 – Mustafa Salih ASLIM Jan 12 '20 at 00:28
  • Hi, this doesn't work for Ubuntu 20.04.1 LTS, do you what is the alternative @MSSQL_Ubuntu – heisenbug29 Jan 06 '21 at 17:06
  • Switching to UnixODBC + FreeTDS is another option. – daviewales May 11 '21 at 06:34
45

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

miktea
  • 451
  • 4
  • 2
  • 2
    Just chiming in to say this was my fix, too. Seems much safer than downgrading openssl – Quinten Jan 26 '20 at 16:31
  • 1
    I would upvote this a dozen times if I could. After banging my head against Error code 0x2746 for 2 days while trying to update my dev container from python 3.7 to 3.8, it forced an upgrade from Debian Stretch to Buster, which introduced this change and broke the whole works... This solved it like a champ! – Vaelek May 15 '20 at 16:13
  • 2
    If you don't want to downgrade security for the whole system, you can have a local, modified copy of `/etc/ssl/openssl.cnf` and, for the process that needs to communicate with SQL Server, set the environment variable `OPENSSL_CONF` to point to it. – liori Sep 12 '20 at 22:40
  • `MinProtocol = TLSv1.1` might also be needed if `CipherString = DEFAULT@SECLEVEL=1` doesn't help – bartolo-otrit Jun 29 '23 at 13:33
29
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

Kurt Preston
  • 391
  • 2
  • 6
23

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.

Community
  • 1
  • 1
ofundefined
  • 2,692
  • 2
  • 18
  • 35
  • 5
    Option 1 solved a problem for me in connecting to SQL Server instances from a docker container which took almost a full day to figure out. SECLEVEL 1 worked fine. For seclevel references, see https://www.openssl.org/docs/man1.1.1/man3/SSL_CTX_set_security_level.html#DEFAULT-CALLBACK-BEHAVIOUR – mgefvert Aug 24 '20 at 20:02
  • 1
    If you don't want to downgrade security for the whole system, you can have a local, modified copy of `/etc/ssl/openssl.cnf` and, for the process that needs to communicate with SQL Server, set the environment variable `OPENSSL_CONF` to point to it. – liori Sep 12 '20 at 22:41
  • 1
    I havent been able to use Option 1 as the config file doesnt have this line. Even if i add it, the issue will still persist. option 2 gave me errors on the make test – Andres Mora Jul 09 '21 at 16:39
20

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

Radai
  • 230
  • 3
  • 8
Ola774
  • 201
  • 1
  • 2
  • Thanks! This is exactly what we needed. Seems like the symlinks weren't created on install? Unrelated, but we also had to symlink to mssql-tools' sqlcmd in /bin as it wasn't accessible otherwise. All 3 post-install issues we had were related to symlinks not being created. – Radai Dec 17 '19 at 14:49
14

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.

Habardeen
  • 321
  • 2
  • 11
7

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.

knb
  • 9,138
  • 4
  • 58
  • 85
Ilya Rogojin
  • 321
  • 2
  • 11
  • I recommend the solution from @MSSQL_Ubuntu with the env var and the symlinks to new ssl and crypto libs. That is, until MS fixes their update. Purging worked before but then I had the issue later and it did not. – stinkyjak Aug 15 '19 at 20:10
  • 10x save my day – Valentin Petkov Oct 18 '19 at 15:56
  • For me worked download libssl1.0.0_1.0.2l-1_bpo8 + 1_amd64.deb and openssl_1.0.2l-1_bpo8 + 1_amd64.deb and extract libssl.so.1.0.0 and libcrypto.so.1.0.0 to /opt/mssql/lib and I named them as libssl.so and libcrypto.so respectively. – Joseph Moreno Oct 20 '19 at 03:31
  • 1
    This helped me using debian 10 (buster) – Phoenix Oct 30 '19 at 10:38
4

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

Alexey Busygin
  • 351
  • 3
  • 4
4

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/

X99
  • 905
  • 10
  • 24
  • 1
    `msodbcsql17=17.3.1.1-1` Package not found. – Necro Sep 19 '19 at 02:08
  • wget https://packages.microsoft.com/ubuntu/20.04/prod/pool/main/m/msodbcsql17/msodbcsql17_17.6.1.1-1_amd64.deb wget https://packages.microsoft.com/ubuntu/20.04/prod/pool/main/m/mssql-tools/mssql-tools_17.6.1.1-1_amd64.deb dpkg -i msodbcsql17_17.6.1.1-1_amd64.deb dpkg -i mssql-tools_17.6.1.1-1_amd64.deb wget http://archive.ubuntu.com/ubuntu/pool/main/o/openssl/libssl1.1_1.1.1f-1ubuntu2_amd64.deb wget http://archive.ubuntu.com/ubuntu/pool/main/o/openssl/openssl_1.1.1f-1ubuntu2_amd64.deb sudo dpkg -i libssl1.1_1.1.1f-1ubuntu2_amd64.deb sudo dpkg -i openssl_1.1.1f-1ubuntu2_amd64.deb – Hairy Ass Feb 26 '23 at 20:32
4

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
7ochem
  • 2,183
  • 1
  • 34
  • 42
Patrick
  • 41
  • 2
3

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

gp van eron
  • 66
  • 3
1

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'

Michał Majewski
  • 11
  • 1
1

Solved for RHEL 9

Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : TCP Provider: Error code 0x2749.
Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to localhost. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

Solution

update-crypto-policies --set LEGACY
  • CentOS Stream release 9
  • Kernel 5.14.0-75.el9.x86_64
  • openssl version: OpenSSL 3.0.7.2 Nov 2022

Documentation: Chapter 4. Using system-wide cryptographic policies

jw-smc
  • 23
  • 5
0

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.

Rafael Machado
  • 51
  • 1
  • 6
0

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.

Hiroyuki Terauchi
  • 1
0

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

ADM-IT
  • 3,719
  • 1
  • 25
  • 26
0

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>

Izzy
  • 35
  • 1
  • 5
0

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.

Piffre
  • 596
  • 6
  • 9
0

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

Vinit Kadkol
  • 1,221
  • 13
  • 12
0

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

GTS
  • 550
  • 5
  • 7
0

Update 2023, SQL Server 2012, Ubuntu 22.10 with ODBC Driver 18 (installation instructions),:

This will NOT work for 22.04., only for 22.10+ Why? because the 22.04 release contains an incomplete unixodbc-dev package. Only 22.10 contains a working unixodbc-dev version 2.3.11-2 (more info). I was not able to install PHP-modules sqlsrv and php_sqlsrv on 22.04.

I am now using:

/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.2.so.1.1 from

cat /etc/apt/sources.list.d/mssql-release.list
deb [arch=amd64,armhf,arm64] https://packages.microsoft.com/ubuntu/22.10/prod kinetic main

openssl version
OpenSSL 3.0.5 5 Jul 2022 (Library: OpenSSL 3.0.5 5 Jul 2022)

odbcinst -q -d
[ODBC Driver 18 for SQL Server]

env | grep OPEN
OPENSSL_CONF=/home/knb/.ssh/openssl-conf-TLS1.1-mssql.cnf
# for command line tools sqmlcmd and isql see file below 

#  "-C ": set "TrustServerCertificate=yes;" in the connection string.
export OPENSSL_CONF=${HOME}/.ssh/openssl-conf-TLS1.1-mssql.cnf && \
  sqlcmd -S myserver -Uknb -Pxxxxx -C -q "select @@version;"

Result: Microsoft SQL Server 2012 (SP4-GDR) (KB4583465) - 11.0.7507.2 (X64)

File /home/knb/.ssh/openssl-conf-TLS1.1-mssql.cnf (For most of these, I don't know what it means)


HOME = .

oid_section     = new_oids
# System default
openssl_conf = default_conf

[default_conf]
ssl_conf = ssl_sect

[ssl_sect]
system_default = system_default_sect

[system_default_sect]
CipherString = DEFAULT@SECLEVEL=0

[ new_oids ]
tsa_policy1 = 1.2.3.4.1
tsa_policy2 = 1.2.3.4.5.6
tsa_policy3 = 1.2.3.4.5.7

[ ca ]
default_ca  = CA_default        # The default ca section

[ CA_default ]

dir     = ./demoCA      # Where everything is kept
certs       = $dir/certs        # Where the issued certs are kept
crl_dir     = $dir/crl      # Where the issued crl are kept
database    = $dir/index.txt    # database index file.
                    # several certs with same subject.
new_certs_dir   = $dir/newcerts     # default place for new certs.

certificate = $dir/cacert.pem   # The CA certificate
serial      = $dir/serial       # The current serial number
crlnumber   = $dir/crlnumber    # the current crl number
                    # must be commented out to leave a V1 CRL
crl     = $dir/crl.pem      # The current CRL
private_key = $dir/private/cakey.pem# The private key

x509_extensions = usr_cert      # The extensions to add to the cert

name_opt    = ca_default        # Subject Name options
cert_opt    = ca_default        # Certificate field options

default_days    = 365           # how long to certify for
default_crl_days= 30            # how long before next CRL
default_md  = default       # use public key default MD
preserve    = no            # keep passed DN ordering

policy      = policy_match

[ policy_match ]
countryName     = match
stateOrProvinceName = match
organizationName    = match
organizationalUnitName  = optional
commonName      = supplied
emailAddress        = optional

[ policy_anything ]
countryName     = optional
stateOrProvinceName = optional
localityName        = optional
organizationName    = optional
organizationalUnitName  = optional
commonName      = supplied
emailAddress        = optional

[ req ]
default_bits        = 2048
default_keyfile     = privkey.pem
distinguished_name  = req_distinguished_name
attributes      = req_attributes
x509_extensions = v3_ca # The extensions to add to the self signed cert

string_mask = utf8only

[ req_distinguished_name ]
countryName         = Country Name (2 letter code)
countryName_default     = AU
countryName_min         = 2
countryName_max         = 2

stateOrProvinceName     = State or Province Name (full name)
stateOrProvinceName_default = Some-State

localityName            = Locality Name (eg, city)

0.organizationName      = Organization Name (eg, company)
0.organizationName_default  = Internet Widgits Pty Ltd

organizationalUnitName      = Organizational Unit Name (eg, section)

commonName          = Common Name (e.g. server FQDN or YOUR name)
commonName_max          = 64

emailAddress            = Email Address
emailAddress_max        = 64

[ req_attributes ]
challengePassword       = A challenge password
challengePassword_min       = 4
challengePassword_max       = 20

unstructuredName        = An optional company name

[ usr_cert ]

basicConstraints=CA:FALSE

nsComment           = "OpenSSL Generated Certificate"

subjectKeyIdentifier=hash
authorityKeyIdentifier=keyid,issuer

[ v3_req ]

basicConstraints = CA:FALSE
keyUsage = nonRepudiation, digitalSignature, keyEncipherment

[ v3_ca ]

subjectKeyIdentifier=hash

authorityKeyIdentifier=keyid:always,issuer

basicConstraints = critical,CA:true

[ crl_ext ]

authorityKeyIdentifier=keyid:always

[ proxy_cert_ext ]

basicConstraints=CA:FALSE

nsComment           = "OpenSSL Generated Certificate"

subjectKeyIdentifier=hash
authorityKeyIdentifier=keyid,issuer

proxyCertInfo=critical,language:id-ppl-anyLanguage,pathlen:3,policy:foo

[ tsa ]

default_tsa = tsa_config1   # the default TSA section

[ tsa_config1 ]

dir     = ./demoCA      # TSA root directory
serial      = $dir/tsaserial    # The current serial number (mandatory)
crypto_device   = builtin       # OpenSSL engine to use for signing
signer_cert = $dir/tsacert.pem  # The TSA signing certificate
                    # (optional)
certs       = $dir/cacert.pem   # Certificate chain to include in reply
                    # (optional)
signer_key  = $dir/private/tsakey.pem # The TSA private key (optional)
signer_digest  = sha256         # Signing digest to use. (Optional)
default_policy  = tsa_policy1       # Policy if request did not specify it
                    # (optional)
other_policies  = tsa_policy2, tsa_policy3  # acceptable policies (optional)
digests     = sha1, sha256, sha384, sha512  # Acceptable message digests (mandatory)
accuracy    = secs:1, millisecs:500, microsecs:100  # (optional)
clock_precision_digits  = 0 # number of digits after dot. (optional)
ordering        = yes   # Is ordering defined for timestamps?
                # (optional, default: no)
tsa_name        = yes   # Must the TSA name be included in the reply?
                # (optional, default: no)
ess_cert_id_chain   = no    # Must the ESS cert id chain be included?
                # (optional, default: no)
ess_cert_id_alg     = sha1  # algorithm to compute certificate
                # identifier (optional, default: sha1)
knb
  • 9,138
  • 4
  • 58
  • 85