Click here to Skip to main content
15,867,488 members
Articles / Containers / Docker

Authenticate .NET Core Client of SQL Server with Integrated Security from Linux Docker Container

Rate me:
Please Sign up or sign in to vote.
5.00/5 (17 votes)
10 Jan 2019CPOL5 min read 79.1K   18   26
Illustrates the implementation of a .NET Core app, running in a Linux container, connecting to a SQL Server database with integrated security.

Facing the Problem

In case of dockerizing .NET applications using SQL server, one has to think about the way to connect to the database.

Fortunately, Microsoft provides us with a couple of opportunities to establish secure and robust connections which can be applied to Windows containers as well. However, in case of connecting out of a Linux container, not all these concepts are available out of the box. This also affects the usage of integrated security for establishing a connection from an already authenticated client (e.g.: Single Sign On systems).

This article applies the concept of integrated security, which is built on top of a Kerberos authentication process, for Linux containers. The solution requires no code changes in .NET Core application. Instead, it illustrates docker image preparations and configuration of kerberos authentication on system level. At the end, you can connect via integrated security to SQL Server out of a previously authenticated Linux container.

Discovering the Solution Step by Step

For this solution, we have to go down a longer road and pick up pieces of a bigger puzzle, before we are able to create a solution.

Prerequisites

Things you need to reconstruct the solution in your environment:

  • Docker host (e.g.: docker on windows, docker tool box...)
  • SQL Server instance with integrated security enabled (on premise or even dockerized)
  • Domain Controller (AD acting as Key Distribution Server)

Our Demo Application

In order to verify the DB connection, I created a very simple solution. The application hangs in an endless loop querying a particular table dat.MyDataTable in the database myDataBase of the SQL Server instance myDataBaseServer. Retrieved result sets (I am assuming that MyDataTable contains at least three columns) are printed as console output and can be viewed by attaching to container.

C#
using System;
using System.Data.SqlClient;

namespace MyApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            while (true)
            {
                try
                {
                    using (var connection = new SqlConnection("Server=tcp:myDataBaseServer,46005;
                                            Initial Catalog=myDataBase;Integrated Security=true;;"))
                    {
                        var command = new SqlCommand
                                      ("SELECT TOP 10 * FROM dat.myDataTable", connection);
                        connection.Open();
                        using (var reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                Console.WriteLine($"{reader[0]}:{reader[1]} ${reader[2]}");
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    Console.Write(ex);
                }

                System.Threading.Thread.Sleep(10000);
            }
        }
    }
}

Please consider the connection string specifying integrated security:

C#
"Server=tcp:myDataBaseServer,46005;Initial Catalog=myDataBase;Integrated Security=true;;"

Prepare Kerberos Authentication in Container

In order to communicate out of Linux containers with a Key Distribution Center (KDC), some preparations of container image and configuration are necessary.

  1. Install packages for KDC consumption.
  2. Creating a proper krb5.conf file for accessing KDC application hosted in AD Domain controller
  3. Generate keytab file (in order to avoid exposing passwords in plain text)

Required Packages for KDC Consumption

The demo application is running on the microsoft/dotnet:aspnetcore-runtime image which basically derives from the debian:stretch-slim image. This requires us to install the following packages (code pasted from underlying dockerfile):

YAML
...
RUN apt install -y krb5-config 
RUN apt-get install -y krb5-user
...

These pacakges enable us to run kinit command within the container for fetching kerberos tickets from the KDC. Additionally, also the ktutil tool is available for creating the above mentioned keytab files.

Creating a Proper krb5.conf File

In order to communicate with the underlying KDC, a proper krb5.conf file is created and stored in the containers /etc folder. For constructing a proper example, assume we are in the domain my.company.local and our KDC is given by mydomaincontroller.my.company.local (in a windows AD driven environment, you can get the value of mydomaincontroller by simply running the command echo %logonserver% when you are logged in via AD). One has to also keep in mind that the KDC is located in the Domain Controller as and using the AD database.

YAML
# /etc/krb5.conf -- Kerberos V5 general configuration.
# $Id: krb5.conf,v 1.43 2011/09/23 00:37:20 eagle Exp $
#
# This is my default Kerberos v5 configuration file.  The
# canonical location of this file is http://www...
#
# This configuration allows any enctypes.  Some systems with really old
# Kerberos software may have to limit to triple-DES and DES.

[appdefaults]
    default_lifetime      = 25hrs
    krb4_convert          = false
    krb4_convert_524      = false

    ksu = {
        forwardable       = false
    }

    pam = {
        minimum_uid       = 100
        forwardable       = true
    }

    pam-afs-session = {
        minimum_uid       = 100
    }

[libdefaults]
    default_realm         = MY.COMPANY.LOCAL
    ticket_lifetime       = 25h
    renew_lifetime        = 7d
    forwardable           = true
    noaddresses           = true
    allow_weak_crypto     = true
    rdns                  = false

[realms]
     MY.COMPANY.LOCAL = {
        kdc            = mydomaincontroller.my.company.local
        default_domain = my.company.local
    }

[domain_realm]
    my.company.local    = MY.COMPANY.LOCAL

[logging]
    kdc          = SYSLOG:NOTICE
    admin_server = SYSLOG:NOTICE
    default      = SYSLOG:NOTICE

The krb5.conf file has to be located in the containers at /etc/kerb5.conf.

Generating a keytab File

In order to avoid passing passwords to kinit command (e.g.: executing kinit username and subsequently entering the password), I decided to generate proper keytab files. These files are used for fetching kerberos tickets without requiring the password in plain text. In order to generate such a keytab, you need to run the following commands in a Linux shell:

Shell
ktutil 
ktutil: add_entry -password -p myUserName@MY.COMPANY.LOCAL -k 1 -e RC4-HMAC
# ktutil will prompt for entering the password ...
ktutil: write_kt myUserName.keytab
ktuilt: extit

The received keytab file can be mapped or copied in the container. For requesting a kerberos ticket under usage of the keytab file, you can run:

Shell
kinit myUserName -k -t myUserName.keytab

By running klist, you can see that a kerberos ticket was received.

Dockerize the Demo Application

Now we are far enough to look at the docker file:

YAML
FROM microsoft/dotnet:sdk AS build-env
WORKDIR /app

# Copy csproj and restore as distinct layers
COPY *.csproj ./
RUN dotnet restore

# Copy everything else and build
COPY . ./
RUN dotnet publish -c Release -o out


# Build runtime image
FROM microsoft/dotnet:aspnetcore-runtime
WORKDIR /app
COPY --from=build-env /app/out .

# Install krb5 packages
RUN apt-get update
RUN apt-get remove krb5-config krb5-user
RUN apt install -y krb5-config 
RUN apt-get install -y krb5-user

# Copy kerberso configuration and keytab
COPY krb5.conf /etc/krb5.conf
COPY myUserName.keytab /app/myUserName.keytab

# copy the launch script
COPY launch.sh /launch.sh

ENTRYPOINT /launch.sh

The job of the dockerfile can be described by:

  1. Building the .NET Core application in a container based on microsoft/dotnet:sdk AS build-env image
  2. Copying the build artefacts to a runtime image (from microsoft/dotnet:aspnetcore-runtime)
  3. Installing kerberos packages for debian:stretch-slim image (is the base of microsoft/dotnet:aspnetcore-runtime)
  4. Copying the krb5.conf file and keytab
  5. Copying a script that executes kinit and launches the .NET application
  6. Specifying the launch script as entry point

The launch script launch.sh has to contain the kerberos authentication (kinit command) and the execution of the application:

Shell
...
kinit myUserName-k -t myUserName.keytab
dotnet MyApplication.dll

Handle Expiration of Kerberos Tickets

Each kerberos ticket, received via kinit command, has an expiration date. In order to avoid losing authentication while the container is running, one has to call kinit again before the ticket has expired. In other words, kinit has to be executed periodically over container's lifetime.

Handle Expiration of Keytab Files

Similar to keberos tickets, keytab files will expire. You have to create and provide new keytab files also in a periodical manner. A possible option would be to create the file during containers startup process and map it into the containers file system. Nevertheless, depending on the underlying environment, there may be other options as well.

Run It

For running the application, you have to create (use dockerfile above) an image and run a container. When you attach to the docker container, you can see the result of the DB query, which is written as console output.

History

  • 10th January, 2019: Article created

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect Admiral Sportwetten GmbH
Austria Austria
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
Questionkinit: Cannot determine realm for host (principal host/container-hostname@) Pin
DichterKohorte29-Sep-21 0:30
DichterKohorte29-Sep-21 0:30 
AnswerRe: kinit: Cannot determine realm for host (principal host/container-hostname@) Pin
Andi Kleinbichler29-Sep-21 10:23
Andi Kleinbichler29-Sep-21 10:23 
GeneralRe: kinit: Cannot determine realm for host (principal host/container-hostname@) Pin
DichterKohorte29-Sep-21 20:28
DichterKohorte29-Sep-21 20:28 
GeneralRe: kinit: Cannot determine realm for host (principal host/container-hostname@) Pin
DichterKohorte30-Sep-21 0:04
DichterKohorte30-Sep-21 0:04 
GeneralMy vote of 5 Pin
rakkeshbisht1-Jun-21 9:10
rakkeshbisht1-Jun-21 9:10 
QuestionSSPI Provider: Server not found in Kerberos database. Pin
Mariano Renkine24-Jul-20 8:41
Mariano Renkine24-Jul-20 8:41 
QuestionPorts required Pin
saalocin16-Jul-20 1:46
saalocin16-Jul-20 1:46 
QuestionE: Unable to locate package krb5-config Pin
Member 148261138-May-20 6:49
Member 148261138-May-20 6:49 
AnswerRe: E: Unable to locate package krb5-config Pin
Andi Kleinbichler20-May-20 23:47
Andi Kleinbichler20-May-20 23:47 
QuestionUsing multiple accounts to access SQL server Pin
Jan Zahradník9-Apr-20 9:13
Jan Zahradník9-Apr-20 9:13 
Questionerror "Unsupported key table format version number" Pin
Member 1477488826-Mar-20 12:03
Member 1477488826-Mar-20 12:03 
AnswerRe: error "Unsupported key table format version number" Pin
Andi Kleinbichler9-Apr-20 21:45
Andi Kleinbichler9-Apr-20 21:45 
QuestionConnecting to Local DB running on DockerHost Pin
mavp-z23-Dec-19 4:11
mavp-z23-Dec-19 4:11 
AnswerRe: Connecting to Local DB running on DockerHost Pin
Andi Kleinbichler5-Feb-20 19:12
Andi Kleinbichler5-Feb-20 19:12 
PraiseYou're a hero for providing this solution!! Pin
kf_united5-Jun-19 23:59
kf_united5-Jun-19 23:59 
GeneralMy vote of 5 Pin
jruth23-May-19 11:25
jruth23-May-19 11:25 
GeneralRe: My vote of 5 Pin
Andi Kleinbichler29-May-19 4:33
Andi Kleinbichler29-May-19 4:33 
QuestionUnable to connect to the Databae Pin
Member 1414831612-Feb-19 10:38
Member 1414831612-Feb-19 10:38 
AnswerRe: Unable to connect to the Databae Pin
Andi Kleinbichler3-May-19 1:13
Andi Kleinbichler3-May-19 1:13 
GeneralRe: Unable to connect to the Databae Pin
rakkeshbisht1-Jun-21 9:37
rakkeshbisht1-Jun-21 9:37 
GeneralRe: Unable to connect to the Databae Pin
rakkeshbisht1-Jun-21 11:53
rakkeshbisht1-Jun-21 11:53 
QuestionLinux host Pin
alexxxius8-Feb-19 5:27
alexxxius8-Feb-19 5:27 
AnswerRe: Linux host Pin
Andi Kleinbichler3-May-19 1:19
Andi Kleinbichler3-May-19 1:19 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.