Authenticate from Linux to Windows SQL Server with pyodbc

Question

I am trying to connect from a linux machine to a windows SQL Server with pyodbc.

I do have a couple of constraints:

• Need to log on with a windows domain account
• Need to use python3
• Need to do it from Linux to Windows
• Need to connect to a specific instance

I set up the environment as described by microsoft and have it working (I can import pyodbc and use the configured mussel driver).

I am not familiar with Windows domain authentication and what not, so there is where my problem is.

My connection string:

DRIVER={ODBC Driver 17 for SQL Server};SERVER=myserver.mydomain.com;PORT=1433;DATABASE=MyDatabase;Domain=MyCompanyDomain;Instance=MyInstance;UID=myDomainUser;PWD=XXXXXXXX;Trusted_Connection=yes;Integrated_Security=SSPI

Supposedly one should use "Trusted_Connection" to use the Windows domain authentication instead of directly authenticating with the SQL server.

The error I get when running pyodbc.connect(connString):

pyodbc.Error: ('HY000', '[HY000] [unixODBC][Microsoft][ODBC Driver 17 for SQL Server]SSPI Provider: No Kerberos credentials available (851968) (SQLDriverConnect)')

From other sources I read this should work on Windows as this code would use the credentials of the currently logged in user.

My question is how can I connect to a Windows SQL Server instance from Linux using Windows Domain credentials.

Answer 1

DRIVER={ODBC Driver 17 for SQL Server};SERVER=myserver.mydomain.com;PORT=1433;DATABASE=MyDatabase;Domain=MyCompanyDomain;Instance=MyInstance;UID=myDomainUser;PWD=XXXXXXXX;Trusted_Connection=yes;Integrated_Security=SSPI

----->

DRIVER={ODBC Driver 17 for SQL Server};SERVER=myserver.mydomain.com;PORT=1433;DATABASE=MyDatabase;Domain=MyCompanyDomain;Instance=MyInstance;UID=myDomainUser;PWD=XXXXXXXX

By removing the Trusted_Connection=yes part, instead of AD authentication, it will try to use SQL basic authentication instead. However, if your system is configured not to accept it, it will not work.

Answer 2

FreeTDS is the most promising solution upto now,

If you are looking for Sqlalchemy(Python ORM) on Linux

import urllib

from sqlalchemy import create_engine

engine = create_engine('mssql+pyodbc:///?odbc_connect=' +
    urllib.quote_plus('DRIVER=FreeTDS;SERVER=<IP_OR_HOSTNAME>;PORT=1433;DATABASE=<DATABASE_NAME>;UID=<USERNAME>;PWD=<PASSWORD>;TDS_Version=8.0;')
)

for more details about freeTDS use with pyodbc snd sqlalchemy

Answer 3

I had the same issue and got the docker container for airflow using windows authentication by adding a few things to my airflow build. The apt install needs to be run as root.

USER root

RUN apt install -y krb5-config 
RUN apt-get install -y krb5-user

COPY krb5.conf /etc/krb5.conf

In the krb5.conf file

[appdefaults]
    default_lifetime      = 52hrs
    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         = DEFAULT_DOMAIN
    ticket_lifetime       = 52h
    renew_lifetime        = 90d
    forwardable           = true
    noaddresses           = true
    allow_weak_crypto     = true
    rdns                  = false

[realms]
     MY.COMPANY.LOCAL = {
        kdc            = SERVER.DEFAULT_DOMAIN
        default_domain = DEFAULT_DOMAIN
    }

[domain_realm]
    my.company.local    = DEFAULT_DOMAIN

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

DEFAULT_DOMAIN for me is DOMAIN.COMPANY.COM. Others have .LOCAL at the end. Make sure it is all caps in the file. I had an error the first time I tried to authenticate.

Rebuild and then launch the shell for the airflow worker. Run kinit USER It will prompt for a password. Running klist afterwards to confirm you have a ticket. Once you get this working you should be able to authenticate to the server from python.

Answer 4

I was trying to do the same thing and after reading the OPs answer I tested out pymssql and noticed that it worked with just the below:

pymssql.connect(server='myserver', user='domain\username', password='password', database='mydb')

After realizing that that was all pymssql needed I went back to pyodbc and was able to get it working with:

pyodbc.connect("DRIVER={FreeTDS};SERVER=myserver;PORT=1433;DATABASE=mydb;UID=domain\username;PWD=password;TDS_Version=8.0")

---

I just wanted to thank you for posting this as it helped me so greatly!!!! :)

Answer 5

I find two ways for same task. I have MSSQL server with AD auth.

You can use JVM. Load and install JAVA https://www.oracle.com/technetwork/java/javase/downloads/jre8-downloads-2133155.html. Also install JPype1 version 0.6.3 pip install JPype==0.6.3. Version above 0.6.3 won't work correct

import jaydebeapi
import pandas as pd
driver_name = "net.sourceforge.jtds.jdbc.Driver"
connection_url="jdbc:jtds:sqlserver://<server>:<port>/<database name>"
connection_properties = {
"domain": "<domain name>",
"user": "<username>",
"password": "<pwd>"}
jar_path =  <path to jsds>"/jtds-1.3.1.jar"
CONN = jaydebeapi.connect(driver_name, connection_url, connection_properties, jar_path)
sql = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS"
df = pd.read_sql(sql, CONN)

This version was too slow for me.

Also You can use pyodbc via FreeTDS. To create a FreeTDS connection Install FreeTDS on your Linux apt-get install tdsodbc freetds-bin, configure FreeTDS /etc/odbcinst.ini like this:

[FreeTDS]
Description=FreeTDS
Driver=/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup=/usr/lib/x86_64-linux-gnu/odbc/libtdsS.so

and turn it on odbcinst -i -d -f /etc/odbcinst.ini

After that, you can use pyodbc

import pandas as pd
import pyodbc    
CONN =pyodbc.connect('DRIVER={FreeTDS};'
                                  'Server=<server>;'
                                  'Database=<database>;'
                                  'UID=<domain name>\\<username>;'
                                  'PWD=<password>;'
                                  'TDS_Version=8.0;'
                                  'Port=1433;')
sql = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS"
df = pd.read_sql(sql, CONN)

It's works much faster

Answer 6

You must obtain a Kerberos ticket for this to work. Your example doesn't specify whether your Linux system is set up to authenticate via Kerberos or whether you have previously obtained a Kerberos ticket before your code hits your connection string.

If your Linux system is set up to authenticate via Kerberos, then as a proof of concept you can obtain a Kerberos ticket using kinit from the command line. Here's what works for me in python3 running in Ubuntu on Windows via the WSL. The python code:

#!/usr/bin/env python

# minimal example using Kerberos auth
import sys
import re
import pyodbc

driver='{ODBC Driver 17 for SQL Server}'
server = sys.argv[1]
database = sys.argv[2]

# trusted_connection uses kerberos ticket and ignores UID and PASSWORD in connection string
# https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/using-integrated-authentication?view=sql-server-ver15

try:
    cnxn = pyodbc.connect(driver=driver, server=server, database=database, trusted_connection='yes')
    cursor = cnxn.cursor()
except pyodbc.Error as ex:
    msg = ex.args[1]
    if re.search('No Kerberos', msg):
        print('You must login using kinit before using this script.')
        exit(1)
    else:
        raise

# Sample select query
cursor.execute("SELECT @@version;")
row = cursor.fetchone()
while row:
    print(row[0])
    row = cursor.fetchone()
print('success')

This tells you if you don't have a ticket. Since it uses a ticket you don't have to specify a user or password in the script. It will ignore both.

Now we run it:

user@localhost:~# kdestroy # make sure there are no active tickets
kdestroy: No credentials cache found while destroying cache

user@localhost:~# python pyodbc_sql_server_test.py tcp:dbserver.example.com mydatabase
You must login using kinit before using this script.

user@localhost:~# kinit
Password for user@DOMAIN.LOCAL:

user@localhost:~# python pyodbc_sql_server_test.py tcp:dbserver.example.com mydatabase
Microsoft SQL Server 2016 (SP2-GDR) (KB4505220) - 13.0.5101.9 (X64)
        Jun 15 2019 23:15:58
        Copyright (c) Microsoft Corporation
        Enterprise Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: )

success

user@localhost:~#

You may also have success obtaining a Kerberos ticket from python code that runs before you make this connection but that is beyond the scope of this answer. A search for python Kerberos modules might point you toward a solution.

It also appears possible to set up the Linux system so that as soon as a user logs in it automatically obtains a Kerberos ticket that can be passed to other processes. That is also outside of the scope of this answer but a search for automatic Kerberos ticket upon Linux login may yield some clues.

Answer 7

Generating windows authentication via Linux is complex. EasySoftDB (commercial) used to be able to handle this, and FreeTDS has some convoluted support.

https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/using-integrated-authentication

My suggestion is to move away from Windows Authentication and use SQL authentication. Really there is no security difference, except that you are providing a username and password in the connection string. But this would make your life a lot easier.

Answer 8

I ended up using the pymssql library which basically is pyodbc on top of the FreeTDS driver. It worked out of the box.

Weird how I had such a hard time discovering this library..