How to test connectivity to a Microsoft SQL database using the Linux command line

Here are some useful commands when working with Microsoft SQL and Linux, specifically pertaining to the `mssql-cli` sqlcmd utility.

Testing Connectivity using sqlcmd

The format for testing connectivity is:

sqlcmd -S ip_address_or_hostname -U user -P password

In many cases when developing you want to use -C to avoid certificate errors. The official documentation also refers to -N o to turn off encryption, but as of 27 July 2024 this does not work.

Testing Connectivity using `sqlcmd`

sqlcmd -S user.database.windows.net -U db_user -P db_password

View all Databases

1> select name from sys.databases
2> go

Select Records

1> select top 10 * from table_name order by id desc
2> go

Certificate and Encryption Nightmares

Even after doing every correctly you’ll end up with this:

$ sqlcmd -S server.example.com -U user -P 'secret'
Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : SSL Provider: [error:0A000102:SSL routines::unsupported protocol][error:0A0000C7:SSL routines::peer did not return a certificate].
Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : Client unable to establish connection. For solutions related to encryption errors, see https://go.microsoft.com/fwlink/?linkid=2226722.

Don’t bother following that link because it is completely useless.

To get past this error, try -C. Once you do that, you’ll end up with this blue eye:

$ sqlcmd -S server.example.com -U user -C -P 'secret'
Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : SSL Provider: [error:0A000102:SSL routines::unsupported protocol].
Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : Client unable to establish connection.

Next you start going down the rabbit hole of OpenSSL. But you can start the prelude like this:

nmap --script ssl-enum-ciphers server.example.com
Starting Nmap 7.94 ( https://nmap.org ) at 2024-07-27 02:02 SAST
Nmap scan report for server.example.com (1.2.3.4)
Host is up (0.0074s latency).
rDNS record for 1.2.3.4: xyz
Not shown: 992 filtered tcp ports (no-response)
PORT STATE SERVICE
...
1433/tcp open ms-sql-s
| ssl-enum-ciphers:
| SSLv3:
| ciphers:
| TLS_RSA_WITH_3DES_EDE_CBC_SHA (rsa 1024) - F
| compressors:
| NULL
| cipher preference: indeterminate
| cipher preference error: Too few ciphers supported
| warnings:
| 64-bit block cipher 3DES vulnerable to SWEET32 attack
| CBC-mode cipher in SSLv3 (CVE-2014-3566)
| Forward Secrecy not supported by any cipher
| Insecure certificate signature (SHA1), score capped at F
| TLSv1.0:
| ciphers:
| TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA (secp384r1) - F
| TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA (ecdh_x25519) - F
| TLS_RSA_WITH_AES_256_CBC_SHA (rsa 1024) - F
| TLS_RSA_WITH_AES_128_CBC_SHA (rsa 1024) - F
| TLS_RSA_WITH_3DES_EDE_CBC_SHA (rsa 1024) - F
| compressors:
| NULL
| cipher preference: server
| warnings:
| 64-bit block cipher 3DES vulnerable to SWEET32 attack
| Insecure certificate signature (SHA1), score capped at F
|_ least strength: F

Read it and weep because next you have to “fix” OpenSSL to work with Microsoft SQL.

References

Installation

• https://docs.microsoft.com/en-us/sql/tools/mssql-cli?view=sql-server-ver15

Usage

• https://github.com/dbcli/mssql-cli/blob/master/doc/usage_guide.md

Other

• https://www.w3schools.com/sql/sql_top.asp
• https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility?view=sql-server-ver15
• https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-tools?view=sql-server-ver15
• https://stackoverflow.com/questions/2087902/sqlserver-08-query-to-list-all-databases-in-an-instancehttps://stackoverflow.com/questions/124205/how-can-i-do-the-equivalent-of-show-tables-in-t-sql