Search code examples
sql-serverazurepymssql

Pymssql Write to the server failed - 20006

I am using pymssql=2.1.1 to connect Azure database from python. Due to idle connection for few minutes, i am getting error (Write to the server failed) and not able to fetch the data.

I am using connect method of pymssql to create connection to Azure DB.

conn = pymssql.connect(server=v_host, user=v_user, password=v_passwd, database=v_db)
cursor = self.conn.cursor(as_dict=True)
cursor.execute(query)

The error looks like (20006, b'DB-Lib error message 20006, severity 9:\nWrite to the server failed\nNet-Lib error during Connection reset by peer (104)\n')

Solution

  • If you want to us pymssql connect to Azure SQL database, make sure the following requirements: enter image description here

    Examples: 

    import pymssql
conn=pymssql.connect("xxx.database.windows.net", "username@xxx", "password", "db_name")
cursor = conn.cursor()
cursor.execute(query)

    For more details, please see: Connecting to Azure SQL Database. Starting with version 2.1.1 pymssql can be used to connect to Microsoft Azure SQL Database. And you can troubleshoot the error by pymssql Frequently asked questions.

    Another way, you also can try the pyodbc example:

    import pyodbc
server = '<server>.database.windows.net'
database = '<database>'
username = '<username>'
password = '<password>'
driver= '{ODBC Driver 17 for SQL Server}'
cnxn = pyodbc.connect('DRIVER='+driver+';SERVER='+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()
cursor.execute(query)

    Here is the Azure document: Quickstart: Use Python to query an Azure SQL database.

    Hope this helps.