0

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 would like to ask if the approaches #3/ #4 below is correct or am I missing anything? Thank you!

Approaches I've taken

  1. Network connection [succeeded]: Tested using ping

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

  3. Windows Authenication Login (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)'

  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
alangan17
  • 1

0 Answers0