SQL Server windows authentication linux python

Connect to SQL Server from python on linux using windows authentication

One way is to use the FreeTDS driver.

Install FreeTDS driver on your Linux

apt-get install tdsodbc freetds-bin

Turn it on:

odbcinst -i -d -f /etc/odbcinst.ini

Then ready to query the database using the domain user and password

import pandas as pd

import pyodbc

connstr = "Driver={FreeTDS};Server=servername;Database=databasename;uid=domain\\user;pwd=password;TDS_Version=8.0;Port=1433;"

conn =pyodbc.connect(connstr)

sql = "SELECT top 1 * from dbo.table"

df = pd.read_sql(sql, conn)

conn.close()

A better way is using pymssql which is easier to use.

#pip install pymssql

import pandas as pd

import pymssql

conn = pymssql.connect(server='servername', user='domain\\user', password='password', database='databasename')

sql = "SELECT top 1 * from dbo.table"

df = pd.read_sql(sql, conn)

conn.close()

Another way is using Kerberos

This way uses Kerberos token generated beforehand and avoids embedding username password in the connection string.

Firstly, make sure the linux user name is the same as the windows username used for logging to sql server. Don't worry about the domain, just the username.

For example, if the windows user is MARVEL\superman, then just use the 'superman' as the linux user name.

Also need to know the DNS url, e.g. MARVEL.LOCAL, it's case sensitive!!

1.install Kerberos

sudo apt-get install krb5-user

2.edit the conf file at /etc/krb5.conf and replace with below

[libdefaults]

default_realm = MARVEL.lOCAL

dns_lookup_realm = true

dns_lookup_kdc = true

ticket_lifetime = 24h

renew_lifetime = 7d

rdns = true

forwardable = yes

Note the default_realm is the DNS url (domain controller). The original krb5.conf has many serveri info in it. Just replace the whole file with above.

3. Now ready to generate a kerberos token using kinit

kinit

it will concat your linux username and the domain server into superman@MARVEl.LOCAL

and ask for your windows login password

once succeeded, it will keep a token

4. to view the kerberos token

klist

It will show things like

Ticket cache: FILE:/tmp/krb5cc_1000

Default principal: scott@ad.example.net

Valid starting Expires Service principal

05/21/21 13:20:58 05/21/21 23:20:58 krbtgt/ad.example.net@ad.example.net

renew until 05/28/21 13:20:53

Note the token expires very soon so it needs a seprate process to renew it every day.

5. now ready to connect to the database without using username password.

Note put in the "TrustServerCertificate=yes" and "trusted_connection=yes" in connection string.

import pandas as pd

import pyodbc

connstr = "Driver={ODBC Driver 18 for SQL Server};Server=servername;Database=dbname;trusted_connection=yes;TrustServerCertificate=yes;"

conn = pyodbc.connect(connstr)

sql = "SELECT top 1 * from dbo.table"

df = pd.read_sql(sql, conn)

conn.close()

Done

As the Kerberos token expires by design, it needs a CRON job or script that periodically runs to renew the token before their expiration. To avoid requiring the password for each renewal, you can use a keytab file.

Below is a simply solution to renew token at login. Other solutions can be using keytab file or a schedule service to run kinit.

# Always renew Kerberos creds at login

if [ -f "/tmp/krb5cc_$(id -u)" ]; then

kinit -R