Here are some useful commands when working with Microsoft SQL and Linux, specifically pertaining to the `mssql-cli` sqlcmd command line utility.
Table of Contents
Testing Connectivity using sqlcmd
The format for testing connectivity is below. The caveats in 2024 is SSL certificates and encryption. If you’re lucky, this will work without errors:
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, use One
1> select name from sys.databases
2> go
3> use database
4> go
5> SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ‘BASE TABLE’;
6> go
Create a new database
> CREATE DATABASE database_name;
> go
Select Records
1> select top 10 * from table_name order by id desc
2> go
Using in Laravel
On a Laravel Forge server, you might encounter the following error when trying to connect to a database:
could not find driver
The instructions below pertain to PHP 8.3 (20230831) but should work for other versions too if you modify the printf command. Please note the PECL output suggestions adding to .ini, rather follow the instructions about printf.
sudo -i pecl install sqlsrv Installing '/usr/lib/php/20230831/sqlsrv.so' ... pecl install pdo_sqlsrv ... Installing '/usr/lib/php/20230831/pdo_sqlsrv.so' ...You should add "extension=sqlsrv.so" to php.iniYou should add "extension=pdo_sqlsrv.so" to php.ini
Finally:
printf "; priority=20\nextension=sqlsrv.so\n" > /etc/php/8.3/mods-available/sqlsrv.ini printf "; priority=30\nextension=pdo_sqlsrv.so\n" > /etc/php/8.3/mods-available/pdo_sqlsrv.ini # Try this: phpenmod -v 8.3 sqlsrv pdo_sqlsrv #Otherwise this: #/etc/php/8.3/cli/conf.d# ln -s /etc/php/8.3/mods-available/pdo_sqlsrv.ini 30-pdo_sqlsrv.ini #/etc/php/8.3/cli/conf.d# ln -s /etc/php/8.3/mods-available/sqlsrv.ini 20-sqlsrv.ini #/etc/php/8.3/fpm/conf.d# ln -s /etc/php/8.3/mods-available/pdo_sqlsrv.ini 30-pdo_sqlsrv.ini #/etc/php/8.3/fpm/conf.d# ln -s /etc/php/8.3/mods-available/sqlsrv.ini 20-sqlsrv.ini
Change this line:
// 'trust_server_certificate' => env('DB_TRUST_SERVER_CERTIFICATE', 'false'),
Certificate and Encryption Nightmares
Even after doing everything correctly you may 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. So let’s get right to it.
mkdir /usr/local/ssl vi /usr/local/ssl/openssl.cnf
Add this:
openssl_conf = openssl_init [openssl_init] providers = provider_sect ssl_conf = ssl_sect [provider_sect] default = default_sect legacy = legacy_sect [default_sect] activate = 1 [legacy_sect] activate = 1 [ssl_sect] system_default = system_default_sect [system_default_sect] CipherString = ALL:@SECLEVEL=0
Finally:
export OPENSSL_CONF=/usr/local/ssl/openssl.cnf
On Forge, preserve the old file:
Do the above steps, but to make it stick you’ll have to replace the Ubuntu 24.04 file. I’m not entirely sure this is a good thing for security but since you’re a developer you probably want to get on with your day.
mv /etc/ssl/openssl.cnf /etc/ssl/openssl.cnf.org mv /usr/local/ssl/openssl.cnf /etc/ssl/openssl.cnf
References
Installation
Usage
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