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
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
@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.
