Run sqlcmd in Dockerfile

Question

Is it possible to run sqlcmd inside a dockerfile?

First I am creating a layer

FROM microsoft/mssql-server-windows-developer:2017-latest

After that I accept the eula and set the sa password like this:

ENV ACCEPT_EULA=Y

ENV SA_PASSWORD=VerySecret!

After doing so I am trying to execute a simple sample query:

RUN sqlcmd -S "localhost" -U "SA" -P "VerySecret!" -Q "select name from master.dbo.sysdatabases"

Unfortunately the docker build command breaks with the error message "Sqlcmd: 'name from master.dbo.sysdatabases': Unexpected argument. Enter '-?' for help."

Am I doing something wrong?

Answer 1

The MS SQL containers can have SQL statements run against them inside a dockerfile that uses them.

I think your problem is just that double quotes are being stripped from your RUN command.

I couldn't quite decide if its a bug based on this github issue but escaping them as \" will work around it.

You can also avoid setting the SA password in your docker file by defaulting to a trusted connection:

run sqlcmd -Q \"select name from master.dbo.sysdatabases\"

This way the variables can be set at container run time like this:

docker run -e ACCEPT_EULA=Y -e SA_PASSWORD=bobleponge -p 1433:1433 <imageid>

Answer 2

To interactively run Microsoft SQL server from the shell.

docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd -S localhost -U sa

It will prompt for a password.

"sql1" is the name of the instance. Change this with container id if it has not been named.

1>