How to use Windows (Kerberos) Authentication in DevContainer access to SQL Server?

[alangan17]

Background

I would like to build a DevContainer for the ETL application (using Python and dbt). While the pain-point I'm facing is that the SQL Server is using Windows Authenication.

I tried to research for the method of how to perform Windows Authenication in Linux (i.e. the DevContainer). It seems like I need to use Kerberos authenication to perform the connection.

Question:

I want to ask if approaches no. 3/ 4 below are correct, or if I am missing anything? Thank you!

Approaches I've taken

1. Network connection [succeeded]: Tested using ping

2. SQL Login to Target DB [succeeded]: Tested using sqlcmd and dbt debug

3. Windows Authentication Login to Target DB (kinit) [failed]:

   kinit USERNAME@DOMAIN.LOCAL
   >>> Nothing returned

   klist
   >>> Ticket cache: FILE:/tmp/krb5cc_0
   >>> Default principal: USERNAME@DOMAIN.LOCAL
   >>> Valid starting     Expires            Service principal
   >>> 03/13/23 18:32:08  03/14/23 04:32:08  krbtgt/DOMAIN.LOCAL@DOMAIN.LOCAL
   >>>     renew until 03/20/23 18:32:02

   dbt debug
   >>> 'HY000', '[HY000] [Microsoft][ODBC Driver 17 for SQL Server]SSPI Provider: Decrypt integrity check failed (851968) (SQLDriverConnect)'

   sqlcmd
   >>> 'HY000', '[HY000] [Microsoft][ODBC Driver 17 for SQL Server]SSPI Provider: Decrypt integrity check failed (851968) (SQLDriverConnect)'

4. Windows Authenication Login (ktutil to create keytab) [failed]:

   kdestroy

   ktutil
   add_entry -password -p USERNAME@DOMAIN.LOCAL -k 1 -e aes256-cts-hmac-sha1-96
   write_kt USERNAME.keytab
   quit

   kinit USERNAME@DOMAIN.LOCAL -k -t USERNAME.keytab
   >>> kinit: Preauthentication failed while getting initial credentials

DevContainer related files

devcontainer.json

{
	"name": "Python 3",
	"build": { 
		"context": "..",
		"dockerfile": "Dockerfile",
		"args": {
			"NODE_VERSION": "lts/*"
		}
	},

	"remoteUser": "root",

	"runArgs": [
		"--network=host",
		"--add-host=host.docker.internal:host-gateway"
	]
}

Dockerfile

FROM mcr.microsoft.com/azure-functions/python:3.0-python3.8

## Handle SQL Service Driver
## install FreeTDS and dependencies
RUN apt-get update \
 && apt-get install unixodbc -y \
 && apt-get install unixodbc-dev -y \
 && apt-get install freetds-dev -y \
 && apt-get install freetds-bin -y \
 && apt-get install tdsodbc -y \
 && apt-get install --reinstall build-essential -y \
 && apt-get install git -y \
 && apt-get install sudo -y \
 && apt-get install iputils-ping -y

## Install Kerberos V5 (Windows Auth in Linux)
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 .devcontainer/krb5.conf /etc/krb5.conf

## populate "ocbcinst.ini"
RUN echo "[FreeTDS]\n\
Description = FreeTDS unixODBC Driver\n\
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so\n\
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so" >> /etc/odbcinst.ini

## Install Python Packages
COPY requirements.txt /
RUN pip install -r /requirements.txt

## Edit /etc/ssl/openssl.cnf
## Change from "CipherString = DEFAULT@SECLEVEL=2"
## Change to "CipherString = DEFAULT@SECLEVEL=0"
## https://stackoverflow.com/questions/74708033/error-code-0x2746-10054-when-trying-to-connect-to-sql-server-2014-via-odbc-fro
COPY .devcontainer/openssl.cnf /etc/ssl/openssl.cnf

krb5.conf

[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 = DOMAIN.LOCAL
	dns_lookup_realm = true  
	dns_lookup_kdc = true  
	ticket_lifetime = 25h
	renew_lifetime = 7d  
	forwardable = yes
	noaddresses = true
	rdns=false
	fcc-mit-ticketflags = true

[domain_realm]
	.DOMAIN.LOCAL = DOMAIN.LOCAL

[realms]
	DOMAIN.LOCAL = {  
		kdc = KDCSERVER.DOMAIN.LOCAL
		default_domain = DOMAIN.LOCAL
		admin_server = krbtgt/DOMAIN.LOCAL@DOMAIN.LOCAL
	}  

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

The above krb5.conf is referenced from my current Windows PC
I typed klist to capture the below information

Server: krbtgt/DOMAIN.LOCAL @ DOMAIN.LOCAL
KerbTicket Encryption Type: AES-256-CTS-HMAC-SHA1-96
Ticket Flags 0x40e10000 -> forwardable renewable initial pre_authent name_canonicalize 
Start Time: 3/9/2023 15:45:21 (local)
End Time:   3/10/2023 1:45:21 (local)
Renew Time: 3/16/2023 15:45:21 (local)
Session Key Type: AES-256-CTS-HMAC-SHA1-96
Cache Flags: 0x1 -> PRIMARY 
Kdc Called: KDCSERVER.DOMAIN.LOCAL

profiles.yml (for dbt)

dbt_sample:
  target: dev
  outputs:
    dev:
      type: sqlserver
      driver: 'ODBC Driver 17 for SQL Server'
      server: DBSERVER.DOMAIN.LOCAL
      database: DBNAME
      schema: dbo
      windows_login: True
      authentication: ActiveDirectoryIntegrated
      port: 1433
      trust_cert: False
      encrypt: False

[bamurtaugh]

Thanks for sharing this scenario. I've cross-posted this in our dev container community Slack channel to get some more perspectives. And if you'd like to join our community Slack, we'd love to have you join! https://github.com/orgs/devcontainers/discussions/3

[sliekens]

It's curious that you could login with a password but not with a keytab. One of the errors in your log is "Pre-authentication failed" which might be a clue.

This problem can occur when a domain controller doesn't have a certificate installed for smart card authentication (for example, with a "Domain Controller" or "Domain Controller Authentication" template), the user's password has expired, or the wrong password was provided.

Hard to say what is really going on here.