pyodbc connection to MS SQL Server running on a docker container

Question

pyodbc doesn't seem to be able to connect when trying to connect through specifying Driver. I am able to connect to setting up a DSN but I also want to make connection when user has got the Driver, Server, UID, PWD and Database details.

I am on Mac and and using FreeTDS driver.

freetds.conf

[MYMSSQL]
host = 0.0.0.0
port = 1433
tds version = 7.3

odbcinst.ini

[FreeTDS]
Description=FreeTDS Driver for Linux & MSSQL
Driver=/usr/local/lib/libtdsodbc.so
Setup=/usr/local/lib/libtdsodbc.so
UsageCount=10

Here is how I am trying to connect:

conn_str = "DRIVER=FreeTDS;SERVER={0};UID={1};PWD={2};DATABASE={3}".format('MYMSSQL', 'sa', 'password','tempdb')
conn = pyodbc.connect(conn_str)

The error I get is this:

pyodbc.OperationalError: ('08001', '[08001] [FreeTDS][SQL Server]Unable to connect to data source (0) (SQLDriverConnect)')

Exact same database details work when I try to connect through DSN.

Are you choosing the FreeTDS driver while creating the DSN or any other driver. — ansu5555
There is 'Driver' entry in the DSN configuration so I have [MS_SQL_SERVER_DSN] Description = Test to SQLServer Driver = FreeTDS Servername = MYMSSQL Database = tempdb — Toseef Zafar
Could you try giving driver like DRIVER={FreeTDS} and see what happens — ansu5555
If you want to pull the server name from freetds.conf I think you'll need to use SERVERNAME= in your connection string (rather than SERVER=). Details here. — Gord Thompson

Gord Thompson Gord Thompson · Accepted Answer · 2018-10-24T15:18:55

If you have a freetds.conf file containing the host name/IP and port, e.g.,

gord@xubuntu64-nbk1:~$ cat /etc/freetds/freetds.conf 
[myFreeTdsHost]
    host = 192.168.0.179
    port = 49242

then you can use both of those values in your DSN-less connection string by simply specifying the SERVERNAME=

# get host name/IP and port from freetds.conf
cnxn_str = (
    'DRIVER=FreeTDS_ODBC;'
    'SERVERNAME=myFreeTdsHost;'
    'DATABASE=myDb;'
    'UID=sa;PWD=_whatever_;'
)

You can also supply the host name/IP and port directly via SERVER= and PORT= like so

# supply host name/IP and port directly (bypassing freetds.conf)
cnxn_str = (
    'DRIVER=FreeTDS_ODBC;'
    'SERVER=192.168.0.179;'
    'PORT=49242;'
    'DATABASE=myDb;'
    'UID=sa;PWD=_whatever_;'
)

For details, see the FreeTDS documentation here.

pyodbc connection to MS SQL Server running on a docker container

1 Answers