.NET Core/5 and remote MSSql Server with docker and kubernetes

There are two method for create docker image

First method

Right click on the web project, choose Add > Docker Support. It will create Dockerfile in the web project.This file contains below code.

  #See https://aka.ms/containerfastmode to understand how Visual Studio uses this Dockerfile to build your images for faster debugging.

  #If you using .Net Core then add mcr.microsoft.com/dotnet/sdk:3.2 or latest
  #If you using .Net 5 then add mcr.microsoft.com/dotnet/aspnet:5.0
  
  FROM mcr.microsoft.com/dotnet/aspnet:5.0 AS base
  WORKDIR /app

  FROM mcr.microsoft.com/dotnet/sdk:5.0 AS build
  WORKDIR /src
  COPY . .
  RUN dotnet restore "<startup_project_name>/<startup_project_name>.csproj"
  WORKDIR "/src/WebApp"
  RUN dotnet build "<startup_project_name>.csproj" -c Release -o /app

  FROM build AS publish
  WORKDIR "/src/WebApp"
  RUN dotnet publish "<startup_project_name>.csproj" -c Release -o /app

  FROM base AS final
  WORKDIR /app
  EXPOSE 80
  EXPOSE 443
  COPY --from=publish /app .
  ENTRYPOINT ["dotnet", "<startup_project_name>.dll"]

• buid with docker

  docker build . -t <your docker username>/<app-name>:<tag>

• OR

  docker build . -t <app-name>:<tag>

• image run
  -p flag redirects a public port to a private port inside the container
  -d runs the container in detached mode, leaving the container running in the background

  docker run -p 3001:3000 -d  <your docker username>/<app-name>:<tag>

• OR

  docker run --name <new image name> -d -p 3001:3000 <your docker username>/<app-name>:<tag>

• OR

  docker run --name <new image name> -d -p 3001:3000 <app-name>

Second method (docker-compose)

Right click on the Web project, choose Add > Container Orchestrator Support. The Docker Support Options dialog appears. Choose Docker Compose. Choose your Target OS, for example, Linux.

Note : Use a .env file to replace variables in your docker-compose.yml file
Sometimes you may need to create a template docker-compose file and replace some values inside. Let’s say that you have multiple environments that are mostly the same except for a few different configurations that are different. In that case you could automatically generate a .env file with all your required variables that would replaced in your docker-compose.yml file. For example the following .env file placed in the same directory as the docker-compose.yml file:

  IMAGE_TAG=4.0.9

Would replace the variable $IMAGE_TAG in the following docker-compose.yml:

  version: '3.4'

  services:
    web:
      image: docker_net5:${IMAGE_TAG}
      build:
        context: .
        dockerfile: <startup_project_name>/Dockerfile

Use the build flag to rebuild your containers
Sometimes you want to force a rebuild of your containers with docker-compose, do it like this:

  docker-compose up -d --build
  # Ignore the cache:
  docker-compose build --no-cache

Connecting to local or remote SQL Server from Docker Container

My docker container host an asp.net core application with EF Core. During the local debugging the app was perfectly connecting to my sql database. But after deploying the app to docker container I got exception like unable to connect to sql server.

So why this happen?. Here the docker container is running as a separate machine inside your host computer. So to connect to the sql database in your host machine, you need to enable remote connections to sql server.

So follow below steps to enable remote connections to sql server.

• You must configure SQL Server with Mixed Mode Authentication. For remote connection you need to supply user name and password.

• Open SQL Server Configuration Manager as Administrator.
  To open the configuration manager, Microsoft management console uses the SQLServerManager<version>.msc file.
  For example, if you have installed SQL Server 2019 on the server, then you can open it by running “SQLServerManager15.msc” command.
  press the Windows and R key and type below one which is your need

  Command	SQL Server version
  SQLServerManager15.msc	SQL Server 2019
  SQLServerManager14.msc	SQL Server 2017
  SQLServerManager13.msc	SQL Server 2016
  SQLServerManager12.msc	SQL Server 2014

• Now select Protocols for SQL and then select TCP/IP now open the Properties by right click.

• In the TCP/IP properties window enable TCP/IP for SQL Server (Enabled=Yes, Listen All=Yes).

  

• Now select IP Addresses tab in properties window. And under IPAll give port number as 1433.

  

• Now restrat SQL Server service.

  

• Make sure SQL Server Browser is running, if not start it.

  

• Now setup your firewall to accept inbound connection to 1433

  

• To connect to sql server from docker, you can’t use the host computer name. You need to find the right ip address of you host.
  Open command prompt and issue “ipconfig” command, Then you can see a nat ip address of Docker NAT ip address copy it.

  

• Now you can use below connection string in your application.

    Data Source=<IPAddress>, 1433;Database=<Database name>;User ID=<sql username>;Password=<Your password>;MultipleActiveResultSets=true;

  OR

    Server=<IPAddress>, 1433;Initial Catalog=<Database name>;User ID=<sql username>;Password=<Your password>;MultipleActiveResultSets=true;

  OR

    Server=<IPAddress>, 1433;Database=<Database name>;User=<sql username>;Password=<Your password>;MultipleActiveResultSets=true;

  OR if SQL Server IP dynamic

    Server=hos.docker.internal, 1433;Database=<Database name>;User=<sql username>;Password=<Your password>;MultipleActiveResultSets=true;