For some reason I'm not able to establish a connection to MSSQL Server with FreeTDS in a python:3.7-slim-buster (debian) docker image.
Code
Also available on github ..
Dockerfile
FROM python:3.7-slim-buster
# install free-tds used for MSSQL connections
RUN sed -i "s#deb http://security.debian.org/debian-securitystretch/updates main#deb http://deb.debian.org/debian-security stretch/updates main#g" /etc/apt/sources.list
RUN apt-get update
RUN apt-get install -y g++
RUN apt-get install -y unixodbc-dev
RUN apt-get install -y freetds-dev
RUN apt-get install -y freetds-bin
RUN apt-get install -y tdsodbc
# create a freetds odbc driver path
# required so python scripts can reference {FreeTDS} in their pypyodbc connections
RUN echo "[FreeTDS]" >> /etc/odbcinst.ini
RUN echo "Description=FreeTDS Driver" >> /etc/odbcinst.ini
RUN echo "Driver=/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so" >> /etc/odbcinst.ini
RUN echo "Setup=/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so" >> /etc/odbcinst.ini
RUN apt-get clean
COPY requirements.txt .
RUN pip install --default-timeout=2000 --no-cache-dir -r requirements.txt
COPY example.py .
# set entrypoint
ENTRYPOINT python example.py
example.py
#!/usr/bin/python3
import platform
import pandas as pd
import pypyodbc
def connect():
driver = '{FreeTDS}' if platform.system() != 'Windows' else '{ODBC Driver 17 for SQL Server}'
creds = { # dummy credentials
'server': 'mssqlserver.mydomain.com',
'port': '1443',
'database': 'my_database_name',
'username': 'my_username',
'password': 'my_password'
}
return pypyodbc.connect(
(
'DRIVER={driver};'
'SERVER={server};'
'PORT={port};'
'DATABASE={database};'
'UID={username};'
'PWD={password};'
'TDS_Version={tds_version};'
).format(
driver=driver,
server=creds['server'],
port=creds['port'],
database=creds['database'],
username=creds['username'],
password=creds['password'],
tds_version='7.2'
)
)
def dummy_query(conn):
sql_query = '''SELECT * FROM (VALUES ('Hello world'),('Hello world')) t1 (col1) WHERE 1=1'''
return pd.read_sql(sql_query, conn)
if __name__ == '__main__':
conn = connect()
data = dummy_query(conn)
print(data)
requirements.txt
pandas==0.25.1
pypyodbc>=1.3.4
When running on Windows
works fine
python example.py
col1
0 Hello world
1 Hello world
When using docker
doesn't work, fails with a pypyodbc.DatabaseError ..
Building docker image
docker build . -t freetds_issue
Running docker image
docker run freetds_issue
Traceback (most recent call last):
File "example.py", line 51, in <module>
conn = connect()
File "example.py", line 40, in connect
tds_version='7.2'
File "/usr/local/lib/python3.7/site-packages/pypyodbc.py", line 2454, in __init__
self.connect(connectString, autocommit, ansi, timeout, unicode_results, readonly)
File "/usr/local/lib/python3.7/site-packages/pypyodbc.py", line 2507, in connect
check_success(self, ret)
File "/usr/local/lib/python3.7/site-packages/pypyodbc.py", line 1009, in check_success
ctrl_err(SQL_HANDLE_DBC, ODBC_obj.dbc_h, ret, ODBC_obj.ansi)
File "/usr/local/lib/python3.7/site-packages/pypyodbc.py", line 987, in ctrl_err
raise DatabaseError(state,err_text)
pypyodbc.DatabaseError: ('08S01', '[08S01] [FreeTDS][SQL Server]Unable to connect: Adaptive Server is unavailable or does not exist')
Some output from within docker
docker run -it --entrypoint "/bin/bash" freetds_issue
Output of tsql -C:
Compile-time settings (established with the "configure" script)
Version: freetds v1.00.104
freetds.conf directory: /etc/freetds
MS db-lib source compatibility: no
Sybase binary compatibility: yes
Thread safety: yes
iconv library: yes
TDS version: 4.2
iODBC: no
unixodbc: yes
SSPI "trusted" logins: no
Kerberos: yes
OpenSSL: no
GnuTLS: yes
MARS: no
Output of tsql -LH {server_ip}:
ServerName SERVERNAME
InstanceName MSSQLSERVER
IsClustered No
Version 14.0.1000.169
tcp 1433
strangely, using tsql CLI to connect seems to work ..
tsql -S mssqlserver.mydomain.com -U my_username
Password:
locale is "C.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1> exit
