Connecting to SQL Server with pyodbc, FreeTDS, and Kerberos authentication on macOS

Question

I never ask questions to forums, as I can generally find the answer somewhere on the interweb.
However, in this instance I cannot.

Summary: I can connect to and query the database with kerberos authentication via Azure Data Studio and tsql with FreeTDS. I cannot connect to the pyodbc. I've tried tens of different configurations with no success.

My ultimate goal is to connect to the MSSQL server DB with python.

Thank you for any input.

Background

local machine macOS 10.15.4 Connected to VPN required for kerberos authentication Have successfully queried DB from Azure Data Studio database is Microsoft SQL Server 2016

FreeTDS

tsql -S -U 'directory\username' -> Works, can query DB

isql

isql dsn_name 'directory\username' 'password'

error DIAG [42000] [FreeTDS][SQL Server]Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.

isql dsn_name 'directory\username'

error: DIAG [42000] [FreeTDS][SQL Server]Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.

pyodbc

cnxn = pyodbc.connect('DSN=dsn_name;Trusted_Connection=yes')

error: pyodbc.ProgrammingError: ('42000', '[42000] [FreeTDS][SQL Server]Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. (18452) (SQLDriverConnect)')

cnxn = pyodbc.connect('DSN=dsn_name;UID=directory\username;PWD="password"')

error: DIAG [01000] [FreeTDS][SQL Server]Adaptive Server connection failed pyodbc.OperationalError: ('08001', '[08001] [FreeTDS][SQL Server]Unable to connect to data source (0) (SQLDriverConnect)')

Configuration

krb5.conf

libdefaults

default_realm = domain

[realms]

domain_same_as_default = {

kdc = kdc_address

}

odbc.ini

[dsn_name]

Description = MSSQL Server

Driver = FreeTDS

Servername = server_name

odbcinst.ini

[FreeTDS]

Description=FreeTDS Driver for Linux & MSSQL

Driver=/usr/local/lib/libtdsodbc.so

Setup=/usr/local/lib/libtdsodbc.so

UsageCount=1

[ODBC] Trace=Yes

TraceFile=/dev/stdout

freetds.conf

[server_name] host = ip_address

port = port_num

database = db_name

REALM = DOMAIN

FlipperPA FlipperPA · Accepted Answer · 2020-04-08T10:06:04

I've avoided using DSNs with pyodbc, as I prefer to have all my configuration in one spot. Here's an example connection string I use with a domain.

con = pyodbc.connect(
    r"DRIVER={FreeTDS};"
    r'SERVER=mssql.mydomain.com;'
    r"PORT=1433;"
    r"DATABASE=my_db;"
    f"UID=MYDOMAIN\\my_username;"
    f"PWD=my_password;"
    r"TDS_Version=7.3;"
    r"Encrypt=yes;"
    r"Trusted_Connection=yes;"
)

Give that a whirl? The two backslashes (\\) are needed for escaping if using Windows domain auth, that is not a typo. The key thing you may be missing is TDS_Version. You can read more about TDS Versions here: https://www.freetds.org/userguide/ChoosingTdsProtocol.html

Good luck!

Connecting to SQL Server with pyodbc, FreeTDS, and Kerberos authentication on macOS

1 Answers