Connect to remote MSSQL 2016 using isql and python on Mac?

Question

Goal: Connect to remote MSSQL 2016 server via Python.

Main approach: Closely followed tutorial in https://github.com/mkleehammer/pyodbc/wiki/Connecting-to-SQL-Server-from-Mac-OSX .

Problem: Able to connect via tsql, but isql is not working. Errors

[S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source
[37000][unixODBC][FreeTDS][SQL Server]Login failed for user 'DOMAIN\user-p'
[37000][unixODBC][FreeTDS][SQL Server]Cannot open database "TIT_BI_OPERATIONS" requested by the login. The login failed.

Things tried:

Different ODBC drivers 13.1, 17, FreeTDS
Inclusion/exclusion of escape character in the user name.
Host name vs host ip.

Settings: odbc.ini

[ODS_DSN]
Description = Connection to ODS MS_SQL 2016
Driver = FreeTDS
Servername = ODS_DSN
Port = 40000
Database = TIT_BI_OPERATIONS

odbcinst.ini

[FreeTDS]
Driver=/usr/local/lib/libtdsodbc.so
Setup=/usr/local/lib/libtdsodbc.so
UsageCount=1

freetds.conf

[ODS_DSN]
host = 164.10.17.77
port = 40000
tds version = 7.4
client charset = UTF-8

Notes:

Even though, its not very promising to run python without connecting through tsql and isql first, i still tried without success. Using pyodbc, pypodbc, sqlalchemy.
Most errors in the form: Login failed for user 'DOMAIN\user-p'
For ODBC driver 13: Can't open lib '/usr/local/lib/libmsodbcsql.13.dylib'
I am able to connect via SQL PRO STUDIO, using exact same credentials.

If you have any thoughts which direction to go to climb out of this connection problem, it would be greatly appreciated. Thank you!

FlipperPA · Accepted Answer · 2019-12-08T00:57:30.917

If you're using Windows domain auth, you'll have to use FreeTDS. Oddly enough, Windows domain auth isn't supported by the Microsoft ODBC Driver, only FreeTDS.

Since you can connect with the tsql command, that means FreeTDS is working. I'd recommend connecting directly from Python explicitly. Try a connection string like this:

import pyodbc

con = pyodbc.connect(
    r"DRIVER={FreeTDS};"
    r"SERVER=164.10.17.77;"
    r"PORT=40000;"
    r"DATABASE=TIT_BI_OPERATIONS;"
    f"UID=DOMAIN\\user-p;"
    f"PWD=yourpassword;"
    r"TDS_Version=7.3;"
)

cursor = con.cursor();

cursor.execute("SELECT 'this' AS that")

for row in cursor.fetchall():
    print(row)

Note that you do need two backslashes in the UID field to connect with Windows domain auth; that is not a typo!

Connect to remote MSSQL 2016 using isql and python on Mac?

1 Answers1