Why does MSSQL in Docker return “The last operation was terminated because the user pressed CTRL+C” on sql queries?

I’m on Archlinux 64x (4.17.4-1-ARCH) with Docker (version 18.06.0-ce, build 0ffa8257ec). I’m using Microsoft’s MSSQL docker container CU7. Each time I’m trying to enter a query or to run a SQL file I get this warning message:

Sqlcmd: Warning: The last operation was terminated because the user pressed CTRL+C.

Then when I check in the database with Datagrip, the query hasn’t been executed! Here are my commands :

docker pull microsoft/mssql-server-linux:2017-CU7
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=GitGood*0987654321" -e "MSSQL_PID=Developer" -p 1433:1433 --name beep_boop_boop -d microsoft/mssql-server-linux:2017-CU7

# THIS

sudo echo "CREATE DATABASE test;" > /test.sql
docker exec beep_boop_boop /opt/mssql-tools/bin/sqlcmd -U SA -P GitGood*0987654321 < test.sql

# OR

docker exec beep_boop_boop /opt/mssql-tools/bin/sqlcmd -U SA -P GitGood*0987654321 -Q "CREATE DATABASE test;"

My question is How to avoid Warning operation was terminated by user warning on MSSQL queries?

Answer

You should use docker-compose, I’m sure it will make your life easier. My guess is you’re getting an error without actually knowing it. First time I tried, I used an unsafe password which didn’t meet security requirements and I got this error:

ERROR: Unable to set system administrator password: Password validation failed. The password does not meet SQL Server password policy requirements because it is not complex enough. The password must be at least 8 characters long and contain characters from three of the following four sets: Uppercase letters, Lowercase letters, Base 10 digits, and Symbols..

I see your password is strong, but note that you have a * in your password, which may be executed if not correctly escaped.

Or the server is just not started when running with your command line, example:

# example of a failing attempt
docker run -it --rm -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=GitGood*0987654321' -p 1433:1433 microsoft/mssql-server-linux:2017-CU7 bash
# wait until you're inside the container, then check if server is running
apt-get update && apt-get install -y nmap
nmap -Pn localhost -p 1433

If it’s not running, you’ll see something like that:

Starting Nmap 7.01 ( https://nmap.org ) at 2018-08-27 06:12 UTC
Nmap scan report for localhost (127.0.0.1)
Host is up (0.000083s latency).
Other addresses for localhost (not scanned): ::1
PORT     STATE  SERVICE
1433/tcp closed ms-sql-s

Nmap done: 1 IP address (1 host up) scanned in 0.38 seconds

Enough with the intro, here’s a working solution:

docker-compose.yml

version: '2'
services:
  db:
    image: microsoft/mssql-server-linux:2017-CU7
    container_name: beep-boop-boop
    ports:
      - 1443:1443
    environment:
      ACCEPT_EULA: Y
      SA_PASSWORD: GitGood*0987654321

Then run the following commands and wait until the image is ready:

docker-compose up -d
docker-compose logs -f &

• up -d to demonize the container so it keeps running in the background.
• logs -f will read logs and follow (similar to what tail -f does)
• & to run the command in the background so we don’t need to use a new shell

Now get a bash running inside that container like this:

docker-compose exec db bash

Once inside the image, you can run your commands

/opt/mssql-tools/bin/sqlcmd -U SA -P $SA_PASSWORD -Q "CREATE DATABASE test;"
/opt/mssql-tools/bin/sqlcmd -U SA -P $SA_PASSWORD -Q "SELECT name FROM master.sys.databases"

Note how I reused the SA_PASSWORD environment variable here so I didn’t need to retype the password.

Now enjoy the result

name
--------------------------------------------------------------------------------------------------------------------------------
master
tempdb
model
msdb
test

(5 rows affected)

For a proper setup, I recommend replacing the environment key with the following lines in docker-compose.yml:

env_file:
  - .env

This way, you can store your secrets outside of your docker-compose.yml and also make sure you don’t track .env in your version control (you should add .env to your .gitignore and provide a .env.example in your repository with proper documentation.

Here’s an example project which confirms it works in Travis-CI:
https://github.com/GabLeRoux/mssql-docker-compose-example

Other improvements

There are probably some other ways to accomplish this with one liners, but for readability, it’s often better to just use some scripts. In the repo, I took a few shortcuts such as sleep 10 in run.sh. This could be improved by actually waiting until the db is up with a proper way. The initialization script could also be part of an entrypoint.sh, etc. Hope it gets you started 🍻