Cannot connect to Azure SQL with Python 3.10 in Automation Account

Question

Hello,

I set up an automation account to collect data from Azure and send them over to Azure SQL database I created. I can connect to it manually via Azure Data Studio, but when I use this simple code to test out connection:

#!/usr/bin/env python3
import pyodbc
import os

server = os.getenv('db_address')
database = os.getenv('db_name')
username = os.getenv('username')
password = os.getenv('password')
driver= '{ODBC Driver 17 for SQL Server}'

try:
    cnxn = pyodbc.connect(f'DRIVER={{ODBC Driver 17 for SQL Server}};SERVER={server};PORT=1433;DATABASE={database};UID={username};PWD={password}')
    cursor = cnxn.cursor()
    print('Connected to Azure SQL Server successfully')
except Exception as e:
    print("Connection failed. Error:", str(e))

I'm getting the error:

Traceback (most recent call last): File "/usr/src/tmp/df63c205-6140-4e3a-b330-0eebec50168b/runbooks/AdHunt_DBEditor.py", line 2, in <module> import pyodbc ImportError: libodbc.so.2: cannot open shared object file: No such file or directory

The python package I installed for ppyodbc is "pyodbc-4.0.39-cp310-cp310-manylinux_2_17_x86_64.manylinux2014_x86_64.whl"
When I try to execute the code above on my machine locally, I'm getting:

Connection failed. Error: ('08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]Named Pipes Provider: Could not open a connection to SQL Server [53]. (53) (SQLDriverConnect); [08001] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0); [08001] [Microsoft][ODBC Driver 17 for SQL Server]Invalid connection string attribute (0); [08001] [Microsoft][ODBC Driver 17 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. (53)')

I tried some code variations, but none of them worked.

Does anyone know how to solve it?

Answer 1

Please make sure you install the correct Python driver from here. Here you can find a great Python tutorial to connect to Azure SQL. Here you will also find how to create an Azure Automation Runbook with Python.

You can try the following Python code to connect from your computer to Azure SQL:

import pyodbc
server = 'sqlservertest.database.windows.net'
database = 'Mydatabase'
username ='ServerAdmin'
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("SELECT * FROM TableInYourDatabase")
row = cursor.fetchone()
while row:
    print (str(row[0]) + " " + str(row[1]))
    row = cursor.fetchone()

In addition, please make sure you have created a SQL login with permission to connect to the database. You can try creating a contained database user as shown below and used that contained login to connect to the database.

CREATE USER yourlogin WITH PASSWORD = 'Yh-EhGFjh+';
GO
exec sp_addRoleMember 'db_datareader', 'yourlogin'; 
GO

Make sure you have created a firewall rule as explained on this documentation and the server name and database names are correct.

Another option you have to schedule execution of Python code is using Azure Data Factory along with Azure Batch as explained here.

Answer 2

Alright. It seems there's no way to connect to SQL via Python is runbook, so I basically created a Hybrid Worker and now connect through it.

Regarding the error message I was getting:
"Connection failed. Error: ('08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]Named Pipes Provider: Could not open a connection to SQL Server [53]."

It is related to misconfiguration in the connection line. It seems Python cannot accept the variables I used, so I switched fully to full connection string line as follows:

    cnxn = pyodbc.connect(f'DRIVER={{ODBC Driver 17 for SQL Server}};Server=tcp:SERVERNAME.database.windows.net,1433;Database=DATABASENAME;UID=USERNAME;PWD={{PASSWORD}}')