SQL Server windows authentication linux python

Connect to SQL Server from python on linux using windows authentication

1. 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()

2. 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

fi