pymssql on Windows can connect to local SQL Server but not to Azure SQL

2
votes

Trying to connect to an Azure SQL DB (v12) using pymssql, I am able to connect query and write to my local MSSQL instance using pymssql.

I am getting an "Adaptive Server connection failed" failed error and my research seems to point to FreeTDS, but I have not installed FreeTDS on my machine.

Using the tsql utility, i am able to connect to the Azure SQL instance

Why would I fail to connect using pymssql?

pymssql connection string

import pymssql
conn = pymssql.connect(server='<severname>.database.windows.net',user='mickey@in4live', password='<pass>', database='tesdb')
cursor = conn.cursor()
cursor.execute("select 1")

Outputs

conn = pymssql.connect(server='.database.windows.net',user='mickey@', database='tesdb') File "pymssql.pyx", line 641, in pymssql.connect (pymssql.c:10824) pymssql.OperationalError: (20002, 'DB-Lib error message 20002, severity 9:\nAdaptive Server connection failed (:1433)\n'

However

tsql -H <servername>.database.windows.net -p 1433 -U mickey@<servername> -P <pass>

Connects successfully

2
azure-sql-databasepymssql
Actually, you are using FreeTDS. pymssql is a "simple database interface for Python that builds on top of FreeTDS" (ref: here). – Gord Thompson
Gord, From what I have read FreeTDS doesn't come bundled with pymssql, it's installed separately. I haven't installed it,I am confused as to how I could be using it – Glitch
My understanding is that the default Windows builds of pymssql that would be installed using pip install pymssql (except for pymssql version 2.1.2, which turned out to be an anomaly) are statically linked to FreeTDS and therefore include the required FreeTDS components as an integral part of the pymssql install. (I believe that they are compiled into "_mssql.pyd".) – Gord Thompson
Great, i actually have 2.1.3 installed, so if the FreeTDS can connect to my local MSSQL instance, what do I alter to make it find the azure instance? Most of the documentation points to /etc/freetds.conf, which I don't have in my setup. – Glitch
The thing is that the default "statically linked" builds of pymssql do not include support for SSL connections, and Azure probably requires an encrypted connection. You may need to uninstall your current pymssql and then do something like this, the link to which I found here. – Gord Thompson

2 Answers

4
votes

The default Windows builds of pymssql that get installed using pip install pymssql are statically linked to FreeTDS, so the pymssql binaries (.pyd files) include the required FreeTDS components to establish unencrypted connections. This is sufficient for connecting to many (most?) local SQL Server installs where secure connections are not required.

However, all connections to Azure SQL Database require encryption (ref: here), so a basic pip install pymssql on Windows will not be able to connect to an Azure SQL database. Instead, we need to install pymssql with SSL support as described in the following MSDN article

Step 1: Configure development environment for pymssql Python development

where "Step 1" refers to the first step in Microsoft's tutorial for Azure

Connect to SQL Database by using Python

1
votes

@Wasky, I also recommend you use pyodbc instead of pymssql. The latest version of pymssql on Windows has some issues with ssl that cause ssl linking issues.

Step 1: Install the Microsoft ODBC Driver 11, 13 or 13.1: https://www.microsoft.com/en-us/download/details.aspx?id=50420

Step 2: Install pyodbc

> cd C:\Python27\Scripts>  
> pip install pyodbc

Step 3: Run your sample

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

Let me know how this goes.