I am running a webserver (in this case airflow) on an Ubuntu 18.04 machine which needs to access a SQL Server database which is on the domain/AD.
Q: How can I use Kerberos authentication in a systemd service to access a MSSQL database on the domain?
Subqestion: How might I automate renewal of tickets? I toiled with k5start for some time with no success, is there a standardized way of doing this?
Running from shell:
airflow@airflow:~$ kinit airflow@EXAMPLE.COM
Password for airflow@EXAMPLE.COM:
airflow@airflow:~$ klist -A
Ticket cache: KEYRING:persistent:478604841:478604841
Default principal: airflow@EXAMPLE.COM
Valid starting Expires Service principal
11/08/2019 22:34:41 11/09/2019 08:34:41 krbtgt/EXAMPLE.COM@EXAMPLE.COM
renew until 11/09/2019 08:34:41
airflow@airflow:~$ airflow webserver
Result: webserver successfully connects to SQL Server through Kerberos
Side note: after running web server as above, klist
shows the SPN of the SQL Server:
airflow@airflow:~$ klist
Ticket cache: KEYRING:persistent:478604841:478604841
Default principal: airflow@EXAMPLE.COM
Valid starting Expires Service principal
11/08/2019 23:00:22 11/09/2019 09:00:18 MSSQLSvc/dbserver.example.com:1433@EXAMPLE.COM
renew until 11/15/2019 23:00:14
11/08/2019 23:00:18 11/09/2019 09:00:18 krbtgt/EXAMPLE.COM@EXAMPLE.COM
renew until 11/15/2019 23:00:14
Kinit then start service
airflow@airflow:~$ kinit airflow@EXAMPLE.COM
airflow@airflow:~$ sudo service airflow-webserver start
airflow@airflow:~$ journalctl -u airflow-webserver.service -xe
Output
Nov 08 18:12:11 airflow airflow[54723]: File "/home/EXAMPLE.COM/airflow/.pyenv/versions/3.7.4/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 481, in connect
Nov 08 18:12:11 airflow airflow[54723]: return self.dbapi.connect(*cargs, **cparams)
Nov 08 18:12:11 airflow airflow[54723]: sqlalchemy.exc.DBAPIError: (pyodbc.Error) ('HY000', '[HY000] [Microsoft][ODBC Driver 17 for SQL Server]SSPI Provider: No Kerberos credentials available (default cache: FILE:/tmp/krb5cc_478604841) (851968) (SQLDriverConnect)')
Nov 08 18:12:11 airflow airflow[54723]: (Background on this error at: http://sqlalche.me/e/dbapi)
/etc/krb5.conf
[libdefaults]
default_realm = EXAMPLE.COM
dns_lookup_realm = true
dns_lookup_kdc = true
ticket_lifetime = 24h
renew_lifetime = 7d
forwardable = true
ccache_type = 5
default_ccache_name = KEYRING:persistent:%{uid}
default_client_keytab_name = /home/%d/%u.keytab
[realms]
EXAMPLE.COM = {
kdc = exampledc1.example.com
kdc = exampledc2.example.com
}
[domain_realm]
.example.com = EXAMPLE.COM
example.com = EXAMPLE.COM
/etc/systemd/system/airflow-webserver.service
[Unit]
Description=Airflow web server daemon
After=network.target
[Service]
EnvironmentFile=/etc/sysconfig/airflow
User=airflow
Group=airflow
Type=simple
ExecStart = /home/EXAMPLE.COM/airflow/.pyenv/versions/3.7.4/bin/airflow webserver
Restart=on-failure
RestartSec=5s
[Install]
WantedBy=multi-user.target