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?
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>
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>
It’s not possible to connect to databases inside a Dockerfile.
Think of the Dockerfile like a javac or cc or make command. It produces a binary image; once you’ve built that image, you can run it on several different hosts. You can very easily imagine workflows like deleting and recreating the database without rebuilding the image, or pushing and pulling the image onto a different host with a different database, where these setup steps haven’t run.
The usual approach here is to configure your container to run database migrations as part of its startup sequence, possibly with a custom entrypoint script. The stock database images also come with a mechanism to run an SQL script when a container is first created, which you can use to create an empty database for your application.