Connecting to a Local Microsoft SQL Server on MacOS with Docker and Dotnet Core

Execute SQL against a Microsoft SQL Server Docker Container running on MacOS.

-

I'm a developer that uses Microsoft technology, and I'm on a mission to never use the Windows OS again (haha, ok. maybe not never again). As counterintuitive as that sounds, a lot of advancements in the last few years have helped make this possible. Microsoft released Visual Studio Code & Visual Studio 2017 For MacOS, SQL Server 2017 for Linux, and there is much better support for Dotnet Core in Azure DevOps (read: Visual Studio Online) and the Azure Cloud to build, release, & host code then there was a couple years ago.

Even though Docker Inc. has been in flux lately, the company has established Docker well enough that it should live on (TLDR: skip to the last paragraph). With that in mind, Docker shouldn't be going anywhere anytime soon. So, since you can't install the Linux version of SQL Server 2017 on MacOS, we'll use Docker to quickly spin up a box that can get us working.

Pull the Docker Image from Microsoft

Microsoft has some pretty decent steps that I pulled from. This article contains more of the nitpicky stuff you need to actually run a sql command against it. First things first, download docker community-edition and install it on your MacOS device. Installing Docker is pretty easy, so I won't belabor the point.

Open up terminal and pull the Docker image:

docker pull microsoft/mssql-server-linux:2017-latest

Create a Docker Container from an Image.

There are some pretty specific parameters that need to be passed when creating a container. Each Docker image has its own individual parameters based on what you need the image for. This is straight from the Microsoft steps too. Here's what it's doing: -d Tells Docker that we'd like to run our container in the background. In this case, we'd want to use the mssql server image we just pulled down which is specified right after the optional -d. -e specifies environment variables that the image itself can use for configuration. We'll tell docker that we want to accept the End User License Agreement because we don't want to have to type YES a hundred times. We'll also specify a default password for the SQL server. Next, -p tells Docker to expose the container's ports to the host machine (so that we can connect to SQL from our host machine). Lastly, --name allows us to specify an easy name for the container so we can quickly start and stop it without having to look up the container name.

docker run \
-e 'ACCEPT_EULA=Y' \
-e 'MSSQL_SA_PASSWORD=YourSTRONG!Passw0rd' \
-p 1401:1433 \
--name sql1 \
-d microsoft/mssql-server-linux:2017-latest

Connecting to Your SQL2017 Instance

You can use Docker to run SQL commands by opening bash inside your running container, but for our purposes, let's connect to the container from outside of our Docker container. We'll just set up a Dotnet Core console app and add the SqlClient package.

mkdir ~/Source
mkdir ~/Source/sqlTest
cd ~/Source/sqlTest
dotnet new console
dotnet add package System.Data.SqlClient

Great. We've created a new conosole app and have all the dependencies we'll need to connect it to SQL. Let's Open Program.cs in vi to get started.

vi Program.cs

Tap v and press the down arrow to highlight the program, then tap d to delete it. Next, right click -> paste the following code:

using System;
using System.Data.SqlClient;

class Program
{
   private static string _connStr = @"
      Server=127.0.0.1,1401;
      Database=Master;
      User Id=SA;
      Password=YourSTRONG!Passw0rd
   ";

   static void Main() 
   {
      using (var conn = 
         new SqlConnection(_connStr)) 
      {
         conn.Open();
         
         var cmd = new SqlCommand(@"
            SELECT [Name] 
            FROM master..sysdatabases;
         ", conn);
            
         var reader = 
            cmd.ExecuteReader();
         
         while (reader.Read()) {
            Console.WriteLine(
               reader["Name"]
            );
         }
      }
   }
}

Hit the esc key, then shift + :, then type wq, and hit the enter key to write the file and quit vi. Now your program is ready to run.

dotnet run

Well look at that, a simple Dotnet Core console app that lists all of the DBs in your brand spankin' new MSSQL Server 2017 Docker container, running on your very own local MacOS machine!

Once you're finished, be sure to turn off your Docker container.

docker container stop sql1

Microsoft suggests persisting the data before you close the Docker container, and suggests a couple ways of doing it: here, and here.

If you liked this article, let me know by upvoting my answer on stack exchange. Thanks for reading!

Rich Minchuk

Technology Enthusiast and Wannabe Growth Hacker