Adaptive server connection failed (DB-Lib error message 20002, severity 9)

Question

I'm sure this issue has been raised an uncountable number of times before but perhaps, someone could still help me.
I am using pymssql v2.1.3 with Python 2.7.12 and the code that I used several times until yesterday to write data to my Azure SQL DB has somehow decided not to work anymore - for no apparent reason.

The firewall is set, my IP is in the whitelist, I can connect to the database using SQL Server Management Studio and query the data but I still keep getting this error when attempting to connect using pymssql.

The app is a Flask web-app and following is how I connect to the DB:

conn = pymssql.connect(server='myserver.database.windows.net', user='myusername@mydatabase', password='mypassword', database='mydatabase')

Kent

I would recommend using pymssql==2.1.1. There was a change in the later versions that prevents auto SSL handshakes. Alternatively, I would recommend using pyodbc: learn.microsoft.com/en-us/azure/sql-database/… – Eld

Answer 1

This is likely due to the pymssql version. Did you upgrade pymssql? If yes, try reverting back to 2.1.1

sudo pip install pymssql==2.1.1

Eld

Answer 2

Here can work on my workspace pymssql version: 2.2.8 python verison: 3.11

conn = pymssql.connect(host=r'10.0.1.29:60001\sql2019', user='sa', password='sa', charset='UTF-8', atabase=r'database1', tds_version=r'7.0')

the tds_version should be set 7.0 if you used pymssql 2.2.8. you can have a try other tds_version(7.4,7.3,7.2,7.1,7.0...) if you still encountered issue

you can add addtional version informaiton and debug code

print('pymssql version: ', pymssql.__version__)
print('get_dbversion: ', pymssql.get_dbversion())
print('version_info: ', pymssql.version_info())
os.environ['TDSDUMP'] = 'stdout'

Ionize

It worked! Thanks! – Ubangi

Answer 3

Not really a solution to the issue I raised, but using pypyodbc instead of pymssql works.

conn = pypyodbc.connect(driver='{SQL Server}',server='tcp:myserver.database.windows.net,1433',database='mydatabase', uid='myusername', pwd='mypassword')

Kent

Answer 4

freetds-dev might be missing on linux:

apt-get update && apt-get install freetds-dev

Felicefelicia

Answer 5

Working Connection String

conn = pymssql.connect(server='ip',user='sa',password='pwd',database='db',as_dict=True,tds_version='7.0')

Add -

1. tds_version = 7.0 ~7.3

without tds_version you will see the error listed in OP

File "src/pymssql/_pymssql.pyx", line 659, in pymssql._pymssql.connect pymssql.exceptions.OperationalError: (20002, b'DB-Lib error message 20002, severity 9:\nAdaptive Server connection failed

If you add tds_version=x.x to the connect string the error will go away.

Riel

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Liability

Answer 6

unbelievable the bug is still present... ENV: WSL2 on Windows 10

Fix -> switch to pyodbc:.

1. sudo apt-get install unixodbc-dev && pip3 install pyodbc
2. follow instruction for ubuntu https://learn.microsoft.com/de-de/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver15

    import pyodbc 
server = 'tcp:myserver.database.windows.net' 
database = 'mydb' 
username = 'myusername' 
password = 'mypassword' 
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()

Correspondent