I've been using this guide for connecting to database through pyodbc: https://github.com/mkleehammer/pyodbc/wiki/Connecting-to-SQL-Server-from-Mac-OSX
My config files look like this, in parallel with the tutorial: In freetds.conf:
[MYMSSQL]
host = localhost
port = 1433
tds version = 7.3
In odbc.ini:
[MYMSSQL]
Description = Testing SQLServer
Driver = FreeTDS
Servername = MYMSSQL
In odbcinst.ini:
[FreeTDS]
Description=FreeTDS Driver for Linux & MSSQL
Driver=/usr/local/lib/libtdsodbc.so
Setup=/usr/local/lib/libtdsodbc.so
UsageCount=1
When I test the connection with "tsql -S MYMSSQL -U myuser -P mypassword", I get the error:
Error 20002 (severity 9):
Adaptive Server connection failed
There was a problem connecting to the server
Likewise, "isql MYMSSQL myuser mypassword" returns an error as well:
[ISQL]ERROR: Could not SQLConnect
EDIT: In the query console:
"SELECT @@SERVERNAME" returns "4a70ffff1294"
"SELECT @@SERVICENAME" returns "MSSQLSERVER"
"SELECT @@VERSION" returns "Microsoft SQL Server 2019 (RTM-CU8) (KB4577194) - 15.0.4073.23 (X64)"
tsql -S MYMSSQL
returns
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
Error 20002 (severity 9):
Adaptive Server connection failed
There was a problem connecting to the server
The server is running in a docker image. I am able to connect to it via pycharm's database tool with the 1433 port and the relevant password. Sadly, I'm not very experienced with managing servers. All help is much appreciated.
If you want to continue down that path, we need some more info. What's in your freetds.conf
? Can you connect to your SQL Server from the machine you're trying to install FreeTDS on with telnet mssql.myhost.com 1433
?
However, I find it easier to avoid using freetds.conf
and odbc.ini
, and just keep everything in Python. As long as you have properly configured odbcinst.ini
, you should be able to do something like this:
import pyodbc
con = pyodbc.connect(
"DRIVER={FreeTDS};"
"SERVER=mssql.yourserver.com;"
"PORT=1433;"
"DATABASE=your_db;"
"UID=your_user;"
"PWD=your_pass;"
"TDS_Version=7.3;"
)
cursor = conn.cursor()
cursor.execute("SELECT 1")
for row in cursor.fetchall():
print(row)
Good luck!