Pymssql: database connection only works sometimes

Question

I am trying to connect to an Azure SQL server using Python pymssql. The problem is that the following script works, but only sometimes, in other cases, I get this error:

_mssql.MSSQLDatabaseException: (20002, b'DB-Lib error message 20002, severity 9: \ n Adaptive connection to the server failed \ n ')

This is the script I use:

import pymssql conn = pymssql.connect(server='x', user='x', password='x', database='x') cursor = conn.cursor() cursor.execute('SELECT * FROM customers'); row = cursor.fetchone() while row: print (str(row[0]) + " " + str(row[1]) + " " + str(row[2])) row = cursor.fetchone() 

It would help me a lot if someone tells me why this script above only works sometimes and at the same time when I get the error message “Could not connect to Adaptive Server”.

Answer 1

I looked at these old old topics Server crash when trying to connect to sql-azure from tsql and What is the TDS protocol version 8.0 and why should I use it? . The problem seems to be caused by the wrong version of FreeTDS.

I found the key on the official page of the FreeTDS website http://www.freetds.org/faq.html#Does.FreeTDS.support.Microsoft.servers .

There is a table of versions of the TDS protocol for the product http://www.freetds.org/userguide/choosingtdsprotocol.htm .

FreeTDS will use this version up to 7.1 for backward compatibility, but this should be avoided due to future compatibility issues. See Note below for obsolete versions.

If you use pymssql with FreeTDS on linux, I think you need to check the configuration file "freetds.conf" on the path / etc / freetds /.

This is my configuration for Azure SQL Server below:

 # A typical Microsoft server [egServer70] host = <database_name>.database.windows.net port = 1433 tds version = 7.3 

You can try to test the connection to the Azure SQL server using the fretsds tool 'tsql' for the command ' tsql -H <database_name>.database.windows.net -U Username -D DatabaseName -p 1433 -P Password '.

Best wishes.