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.

Hudis

I'm fairly certain that Microsoft's ODBC driver for Linux (msodbcsql) only supports Kerberos for connecting to a SQL Server instance using Windows credentials. If you don't have the appropriate Kerberos setup then you might be able to use FreeTDS ODBC instead, since it is able to use the older NTLMv2 protocol (if the SQL Server will accept it). – Comedian

Thanks! I'll give it a go with the FreeTDS driver – Hudis

Answer 1

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

Hudis

Unfortunately the pymssql project is discontinued and you should consider using pyodbc – Howe

As stated in the project page: "Recent Changes Version 2.1.5 - 2020-09-17 - Mikhail Terekhov General Revert deprecation" Source: pypi.org/project/pymssql It is truly the best answer to this issue. – Enugu

Answer 2

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 [email protected]:

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.

Selfconfidence

Thanks for this. Do you know if I obtain a ticket for a another user (different from the one running the process) with kinit, the ODBC driver will use that ticket when connecting? Or is there a way to specify a custom user? Since UID and PWD options will be ignored – Chou

@LuisLezcanoAiraldi Good question. I imagine pyodbc would try whatever ticket it found regardless of whether the user in the ticket matched the login user, but that is purely a guess. There are more mysteries though: Apparently it's possible to have multiple active tickets for different domains/users. I don't know what pyodbc would do in this case. – Selfconfidence

If you're following this process, recall that you might need to reestablish your k8 keytab credentials using kinit -f -C [email protected], where -C makes the email domain name case insensitive. – Golf

Answer 3

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

Court

Centos 7: yum install freetds freetds-devel; Driver=/usr/lib64/libtdsodbc.so Setup=/usr/lib64/libtdsS.so – Panic

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!!!! :)

Edris

Answer 5

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.

Jean

Thanks for the suggestion (I would definitely prefer using SQL authentication but there is company politics involved :P) – Hudis

Then don't run your server in linux, and use a microsoft box instead, make sure you explain to them the licensing costs and the management costs. Sometimes when you start escalating costs based on policy, they quickly change their tune. – Jean

Answer 6

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.

Hootchykootchy

Answer 7

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

Economic

Answer 8

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

now it will work

Tubate

Why does this fix the problem in the question? A config dump with no contextual information isn't very helpful for future readers, – Congruence

Learn to use markdown to format your posts. – Flytrap