.NET Core And SQL Server In Linux Docker Containers

Throughout the years, we have been using ASP.NET and SQL Server mainly on Windows. Now the times have changed! You can now develop the same ASP.NET (with more optimized runtime and libraries) apps with the same SQL Server Database Engine on Linux and this is what I want to show you here.

To make things more interesting, I will take Docker, a leading container technology platform, into account for this demo. We have an Azure Linux VM (Ubuntu 16.04) where Docker is installed. We will spin up an ASP.NET Core container and a SQL Server container in a separate Docker user-defined network, accessing the application container from the VM's public IP address and SQL Server container from SSMS on our local Windows Machine.

Getting Started

I have a Linux Ubuntu 16.04 VM in Azure with Docker installed. I will SSH into the machine and generate a default ASP.NET Core 1.0 LTS project with SQL Server Entity Framework Core provider installed. You can do so manually either using dotnet CLI or Yeoman Generators.

The default ASP.NET Core project uses Identity for authentication\authorization. Identity, in turn, depends upon an EFCore provider which is in our case SQL Server.

Now, we do the following 3 important things here.

1. Pull down the official Microsoft SQL Server image from Docker Hub
2. Change the Connection String of the application such that it points to the SQL Server Docker container
3. Dockerize the application

Pull down the official SQL Server Docker image from the terminal as,

docker pull microsoft/MySQL-server-Linux: latest

Next, change the connection string of the ASP.NET Core application in the appsettings.json file using Vim or Nano editor at the root as,

"ConnectionStrings": {
    "DefaultConnection": "Server=sqlinux;User Id=SA;Password=Br0ckLesnar!;Database=ApplicationDb;Trusted_Connection=False;"
},

Notice the Server name in the connection string. It should not be the localhost if you want to run the application inside the Docker container as in our case. The Server name must match the SQL Server custom container name when we run it. This is how Services are discovered by the Docker Engine.

Also, make sure that the "Trusted_Connection" is set to false as it forces the integrated security inside the Linux which is not supported.

Now finally, create the Dockerfile to build the Docker image of our application at the root as,

touch Dockerfile

With the contents,

FROM microsoft/dotnet:1.0.0-preview2-sdk

ENV ASPNETCORE_URLS http://*:5000

COPY . /app

WORKDIR /app

RUN ["dotnet", "restore"]

RUN ["dotnet", "build"]

EXPOSE 5000

ENTRYPOINT ["dotnet", "run"]

And build the image with any name (aspnetcoreapp in our case) by typing in the terminal.

docker build -t aspnetcoreapp

It will start restoring the NuGet packages, set up the environment, and build the Docker image for the application.

We now have the SQL Server and ASP.NET Core Docker images. The next thing we need to do is to configure the Azure VM's Network Security Group (NSG) to open port 80 to allow HTTP traffic from the Internet and port 1433 to allow our local SQL Server Management Studio to connect to the SQL Server container running inside the Linux VM.

My Linux VM is provisioned using the ARM model this is why we need to configure the NSG which was created with the VM. If you used the ASM model, configure the VM's endpoint instead.

To do this, we add Inbound Security Rules for ports 80 and 1433. So go to NSG blade => Inbound security rules => Add inbound security rule, type any formal names, and open the ports 80 and 1433 as.

This is all we have to do. Now we have 2 Docker images and configured the NSG of the NIC attached to the VM.

Spinning up the containers
 

Spin up the SQL Server Container

To spin up the SQL Server container, type in the terminal as

docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=Br0ckLesnar!' -p 1433:1433 -d --name sqlinux --network=isolated_network microsoft/mssql-server-linux

Notice the name of the container. As said earlier, this must match the server name given in the connection string of the web app settings. Also, notice that we must place these running containers inside a separate Docker network. If we don't specify a network, it will run inside the default network and automatic service discovery does not work in the default network to create a separate Docker network type.

And, use this network for your containers.

Spin up the ASP.NET Core Container

To run the ASP.NET Core container, simply type.

docker run -p 80:5000 -d --name webapp --network=isolated_network aspnetcoreapp

We now have the application and database containers running and they are interacting with each other. To test this, browse to the public IP address or DNS name of your Linux VM on your local machine's browser and you will see that the application is up and running.

Go to the Register page and try to register a user.

And you can see that the user is successfully created in SQL Server running inside the Docker Container.

Connecting Windows SSMS with Docker SQL Server

Now we will use our local SQL Server tool, called SQL Server Management Studio (SSMS), to connect the SQL Server instance running inside the Docker container inside the Azure VM. Remember, we opened port 1433 in the NSG attached to the NIC of the VM. So open the SSMS and type the IP Address of the VM with port (with format [ip_address, port]) inside the Server name section, use the SQL Server Authentication option, type the user SA, and type the password we used when we spun the SQL Server container.

We see that the Server is connected -- now run a SQL query against one of the tables created by Identity in the database and you will see that the record has been successfully added and displayed in the SSMS.

Conclusion

We saw how we connect an application container and a database container with the service discovery feature of Docker Engine. I did not mount any volume to the SQL Server container nor did I use any Docker Volume plugin to make it even more productive so this is stateless by default. Using the same technique for production use cases is not recommended. The idea was to provide a step-by-step guide to building a simple 3-tier application using Docker containers.