Search code examples
.netsql-serveramazon-web-servicesdockeramazon-ecs

Access SQL Server with Integrated security from .NET Core app running on Linux container using AWS ECS

I have a .NET Core application running on Linux container using AWS ECS Fargate.

How to connect from that application to SQL Server using integrated security?

Solution

  • Step 1: ensure that SQL Server supports Kerberos authentication # Using SQL Server Management Studio (SSMS), connect to your database and execute following statement:

    select auth_scheme  
from sys.dm_exec_connections 
where session_id = @@spid

    If result of the query is KERBEROS, you are all set and proceed to Step 2. Otherwise, if result is NTLM, this means that Kerberos authentication failed, and SSMS silently fell back to using NTLM authentication. Since integrated security between SQL Server and clients running in Linux environments solely rely on Kerberos authentication, this issue must be addressed first.

    Note: when connecting to SQL Server, it is important to use server hostname or FQDN instead of IP address, otherwise Kerberos authentication will not work.

    Check SPN configuration

    Ensure that SPN is properly configured for SQL Server.

    Microsoft has also released several diagnostic tools that can help with SPN verification and configuration:

    Last but not least, you can use following setspn command to query for a specific SPN(s):

    setspn -T CONTOSO.COM -F -Q MSSQLSvc/your_sql_server.contoso.com

    Query above does support * for a wildcard (replace CONTOSO.COM with your domain)

    Configure encryption types allowed for Kerberos

    Within Active Directory, find an account under which SQL Server is running. Under Account tab and Account options section, confirm that applicable Kerberos cyphers are selected.

    Example:

    enter image description here

    Step 2: Configure ECS Task To use Kerberos authentication, Application Task within ECS Service will be composed of two containers:

    1. A container that will periodically (re)obtain and cache Kerberos ticket-granting tickets (TGT) using kinit command.
    2. A container that will run application, and use TGT acquired by first task to authenticate against MS SQL Server.

    Both containers will mount the same volume. 1st container will cache/write TGT ticket to it, 2nd container will read cached TGT ticket from it.

    TGT acquisition container (sidecar container)

    There are only 3 files needed to setup TGT acquisition container:

    1. krb5.conf - a Kerberos configuration file.
    2. renew.sh - script file with commands to renew TGT.
    3. Dockerfile - packages all into a docker image.
    # krb5.conf
[libdefaults]
dns_lookup_realm = true
dns_lookup_kdc = true
forwardable = true
default_ccache_name = FILE:/var/kerberos/krbcache # TGT cache location
default_realm = CONTOSO.COM
permitted_enctypes = aes256-cts aes128-cts

[realms]
CONTOSO.COM = {
  kdc = CONTOSO.COM
  admin_server = CONTOSO.COM
}

[domain_realm]
.contoso.com = CONTOSO.COM
contoso.com = CONTOSO.COM

[logging]
default = STDERR

    # renew.sh
#!/bin/bash

# Refresh the token periodically.
# Set the length of time that the script will wait to refresh the token.
[[ "$DELAY_SECONDS" == "" ]] && DELAY_SECONDS=3600

# If the AWS region hasn't been set, get it from instance metadata. This will work in an instance as well as in an ECS container.
[[ "$AWS_REGION" == "" ]] && AWS_REGION=$(curl --silent http://169.254.169.254/latest/dynamic/instance-identity/document | jq -r .region)

# Use the ECS container as the source for AWS credentials. This allows the AWS CLI to use the permissions of the task role.
aws configure set credential_source EcsContainer


while true
do
    echo "Starting ticket renewal at: " + $(date)

    # Get the credentials from Secrets Manager.
    CREDENTIALS_SECRET_VALUE=$(aws secretsmanager get-secret-value --secret-id $CREDENTIALS_SECRET_ARN --region $AWS_REGION --query SecretString --output text)

    # Use `jq` to parse the credentials into username & password.
    CREDENTIALS_USERNAME=$(echo $CREDENTIALS_SECRET_VALUE | jq -r '.username')
    CREDENTIALS_PASSWORD=$(echo $CREDENTIALS_SECRET_VALUE | jq -r '.password')

    # Use the username & password to authenticate to Kerberos. The resulting token is written to the token cache, 
    # which is set up in `krb5.conf` to use the task scratch volume, shared by all containers.
    echo $CREDENTIALS_PASSWORD | kinit $CREDENTIALS_USERNAME -f -V $OPTIONS

    echo "Ticket renewal complete, waiting for $DELAY_SECONDS seconds"


    sleep $DELAY_SECONDS &
    wait
done

    # Dockerfile

FROM amazonlinux:2

COPY renew.sh /
COPY krb5.conf /etc/krb5.conf

# Install the Kerberos tools -- to authenticate;
# `jq` -- to parse the credentials from the AWS Secrets Manager, which returns JSON
# `unzip` -- to install the latest version of the AWS CLI
RUN yum install -y krb5-workstation jq unzip 

# Download and install the latest version of the AWS CLI
RUN curl "https://awscli.amazonaws.com/awscli-exe-linux-x86_64.zip" -o "awscliv2.zip"
RUN unzip awscliv2.zip
RUN ./aws/install

VOLUME ["/var/kerberos"]

ENTRYPOINT ["/renew.sh"]

    Based on value specified in CREDENTIALS_SECRET_ARN , renew.sh will periodically renew TGT and cache/save it in location specified at krb5.conf file (e.g. /var/kerberos/krbcache).

    To test that container successfully acquires TGT for a given principle, establish interactive session with the container and execute klist command. When successful, you should see the details of TGT ticket, containing principle name, expiration date, etc.

    Application Container

    Application container runs your .NET Core application. To enable Kerberos on that container add following lines in Dockerfile:

    ...
RUN apt update
RUN apt install -y krb5-config krb5-user  
COPY krb5.conf /etc/krb5.conf
VOLUME ["/var/kerberos"]
...

    The content of krb5.conf file should be identical to one in TGT acquisition container, and it will instruct application to locate Kerberos TGT at FILE:/var/kerberos/krbcache .

    Your application SQL connection string should look similar to this:

    Server=yourSqlServer.contoso.com;Initial Catalog=YourDB;Integrated Security=true;

    To test that container has access to cached TGT, establish interactive session with the container and execute klist . When successful, you should see same TGT ticket, as in another container.

    If everything went well, you should be able to successfully connect to SQL Server using Integrated Security from your .NET Core application running on Linux.

    Additional resources