Error 20002 while trying to connect to a server through tsql (OS X)

Question

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.

Which version of SQL Server are you attempting to connect to? TDS version 7.3 is only relevant to SQL Server 2008 which is no longer supported. You probably want to use at least TDS version 7.4 for SQL Server 2012 and beyond. — AlwaysLearning
TDS 7.1, 7.2, 7.3, and 7.4 will work for SQL Server 2008+, just not with all of the latest features. His version of FreeTDS might not support 7.4. — FlipperPA
"SELECT @@VERSION" command on the query console returns "Microsoft SQL Server 2019 (RTM-CU8) (KB4577194) - 15.0.4073.23 (X64) Sep 23 2020 16:03:08 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Linux (Ubuntu 18.04.5 LTS) <X64>" — Baha

FlipperPA FlipperPA · Accepted Answer · 2021-01-18T21:39:58

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!

Error 20002 while trying to connect to a server through tsql (OS X)

1 Answers