Unable to connect to standalone Microsoft SQL Server 2014 from Azure App Service on Linux

When accessing standalone Microsoft SQL Server 2014 and other legacy services from Azure App Service on Linux, you might encounter connectivity issues but there are no detailed error logs for connectivity by default for same.

To test the connectivity and cause of error, we can first install sqlcmd by doing Web SSH to the Linux App service and running the below commands:

a) From Azure portal , firstly SSH into the container as shown below:

b) Please run the following command in the SSH to install sqlcmd:

//install curl

apt update

apt -y install curl

//install gnupg

apt-get update

apt-get install gnupg

// follow the steps in the docs changing 16.04 to 18.04

curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -

curl https://packages.microsoft.com/config/ubuntu/18.04/prod.list | tee /etc/apt/sources.list.d/msprod.list

apt-get update

apt --fix-broken install

ACCEPT_EULA=Y apt-get install mssql-tools

//Create symlinks to 'SQLCMD' and 'BCP' under /usr/bin/to usesqlcmd command

ln -sfn /opt/mssql-tools/bin/sqlcmd /usr/bin/sqlcmd

ln -sfn /opt/mssql-tools/bin/bcp /usr/bin/bcp

After running these commands test the connection with the following command after replacing the desired variables.

sqlcmd -S tcp:<sqlserver-name>.database.windows.net,1433 -d <database-name> -U <username> -P <password>

In case of standalone SQL Server 2014, which connecting to same from Azure App Service Linux, you will notice the following error when connecting with legacy Microsoft SQL Server 2014:

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.

The most common issue in this regression testing will be a TLS negotiation failure due to a client (Azure App Service in current case) connection attempt to an operating system or browser that does not support TLS 1.2.

There are following two workarounds for the issue:

a) Microsoft SQL Server 2014 should be either upgraded to higher version with support for latest TLS version or

b) Bypassing the security restriction at the Client (Azure App Service) to allow it to connect with older versions & access legacy services

The first option is always recommended but if that can not be done immediately, we can alternatively use the workaround to bypass the OpenSSL security level. Here are the various OpenSSL security levels.

[...]

Level 0

Everything is permitted. This retains compatibility with previous versions of OpenSSL.

Level 1

The security level corresponds to a minimum of 80 bits of security. Any parameters offering below 80 bits of security are excluded. As a result RSA, DSA and DH keys shorter than 1024

bits and ECC keys shorter than 160 bits are prohibited. All export cipher suites are prohibited since they all offer less than 80 bits of security. SSL version 2 is prohibited. Any

cipher suite using MD5 for the MAC is also prohibited.

Level 2

Security level set to 112 bits of security. As a result RSA, DSA and DH keys shorter than 2048 bits and ECC keys shorter than 224 bits are prohibited. In addition to the level 1

exclusions any cipher suite using RC4 is also prohibited. On Ubuntu, TLS versions below 1.2 are not permitted. Compression is disabled.

Level 3

Security level set to 128 bits of security. As a result RSA, DSA and DH keys shorter than 3072 bits and ECC keys shorter than 256 bits are prohibited. In addition to the level 2

exclusions cipher suites not offering forward secrecy are prohibited. Session tickets are disabled.

Level 4

Security level set to 192 bits of security. As a result RSA, DSA and DH keys shorter than 7680 bits and ECC keys shorter than 384 bits are prohibited. Cipher suites using SHA1 for the

MAC are prohibited.

Level 5

Security level set to 256 bits of security. As a result RSA, DSA and DH keys shorter than 15360 bits and ECC keys shorter than 512 bits are prohibited.

[...]

For the blessed images used at App Service Linux, by default the SECLEVEL is set to 2 as shown below:

ContinuousIntegration/TriagingTips/openssl-1.1.1 - Debian Wiki

cat /etc/ssl/openssl.cnf

So, in order to access legacy services , we can Override OpenSSL security level to SECLEVEL to 0 instead of 2 in /etc/ssl/openssl.conf and this should resolve the connectivity issues.

In case of Azure App Service, since only the content in /home is persisted on Azure, so please create a startup.sh file in /home/site/wwwroot with following content:

#!/usr/bin/env bash

cp /etc/ssl/openssl.cnf /home

sed -i -e 's/DEFAULT@SECLEVEL=2/DEFAULT@SECLEVEL=0/g' /home/openssl.cnf

cp /home/openssl.cnf /etc/ssl

dotnet Appname.dll (this will depend on your app dll file or startup command for the app depending on stack)

Once the startup.sh file is created & its content is updated ,please update the Startup command from Configuration blade & provide the path of startup.sh file as :

/home/site/wwwroot/startup.sh

Please refer to screenshot below to update the Startup command from Configuration blade and the App Service will be able to connect with legacy services:

If you have any questions or feedback, please add a comment below.

Continue reading...