I gotta tell you. A lot of software development infidels like me were stoked when we heard the news in 2017 that Microsoft SQL Server (MSSQL) will start supporting Linux.
People like me believe that any form of tech, be it software or hardware, should not be limited by the platform or operating system they’re using. There is no superior platform or programming language or operating system, and we all just want to achieve one thing: a project that works. So Microsoft’s move to embrace Linux and the open-source world is really something that got us excited.
Years have passed and Microsoft did not back out of its promises. Linux emulators came to Windows 10, Visual Studio Code can be installed and used in both Linux and Mac, and they partnered with Docker last year to deliver Azure Container Instances (ACI). But the best thing that happened in my opinion is the release of the official MSSQL Docker images that can run on either Windows 10 or Linux.
Just this week, I thought of making a major change in one of my personal projects. One of the servers I’m maintaining is running Windows and it provides FTP and MSSQL access to other computers in the network. It’s an old server and running Windows there for longer than it did might not be a good idea since Windows gets bloated as time passes. So I thought, what if I set it up again and provide the same service but this time, it’ll run Debian? Since I’m a lazy guy, I can easily setup OpenMediaVault 5 in there and manage the server through that. Then, install Docker and Portainer to setup MSSQL. It sounds easy, right? Well… not quite.
I encountered certain speed bumps such as permission issues. They’re not hard to deal with. But it sure eats time to get it rolling when you don’t know what you’re doing (like me). So I thought maybe I can write about how I got it to work so you can save time and get MSSQL working in the shortest possible time.
So let’s start with logging in to Portainer. Open the docker instance and get to the Container view.
Some articles online would suggest that you just use Portainer’s MSSQL template, provide the necessary values, and call it a day. While that’s a fine solution for most people, at the time of writing, that template is still pointed at the MSSQL 2017 Docker image. If you want to use MSSQL 2019 like me, you would need to do it manually. So within the Container view, click on Add container.
You need to provide the following:
1. The container name.
2. In the Image, you need to provide the image name and the appropriate tag. In my case, it’s mcr.microsoft.com/mssql/server:2019-latest.
3. If you want to use a different port number for your MSSQL Docker container, you may declare it in the Network ports configuration. Please take note that the container value pertains to the port number used within the container, in this case, 1433, the default MSSQL port number.
4. In the Command and logging tab, you need to specify the user as root.
Perhaps you’re wondering why the user should be set to root. If you’re not using root, this is what’s going to happen.
And when you try to log in, this happens:
Why is this happening? It happens because the files necessary to make MSSQL work (e.g. the master database) can’t be copied to the /var/opt/mssql folder. This means that even though your container was deployed successfully and is technically working, you won’t be able to login to MSSQL since it does not have its important components. The container needs to set things up on startup and it needs root privileges to complete the setup. So root it is.
5. In the Volumes tab, you should map at least the data folder. In my case, I mapped 3 directories: the data folder, the log folder, and the secrets folder.
6. I left the network as is. It’s set to bridge mode by default so unless you know what you’re doing, let it be.
7. In the Env tab, you need to set at least 3 environment variables: ACCEPT_EULA, MSSQL_SA_PASSWORD, and MSSQL_PID.
ACCEPT_EULA should be set to Y.
MSSQL_SA_PASSWORD is your SA account’s password.
MSSQL_PID is the product ID that you want to use. It can be Evaluation, Developer, Express, Web, Standard, Enterprise, or a product key.
For more information on the environment variables that you can use, refer to the MSDN entry Configure SQL Server settings with environment variables on Linux.
8. In the Restart policy tab, set the restart policy to Always.
9. Do not touch the Runtime and Resources and Capabilities tabs unless you know what you’re doing.
After all these steps, you can now deploy your MSSQL container. To test it, you open your Management Studio, provide your server’s address and the necessary credentials.
But what are you going to do when you encounter this?
This happens because the certificate being used is not from a trusted certificate authority. this is normal and should not matter that much in development. To make this work, you can simply set the Connection Properties to trust the certificate.
Take note that this is not a good practice in a production setting and you better make sure you have a certificate from a trusted CA when dealing with production systems.
So there you have it. You should be able to get your MSSQL Docker instance to work if you followed everything in this guide. Happy coding!
Bien is a software engineer for more than 10 years, focusing on Microsoft .NET technology. He developed solutions ranging from embedded systems to accounting systems. He spends his free time trying to understand the world and its people.