Data Factory + pyodbc: login timeout expired error

Torralba-Resino Javier 36 Reputation points
2023-01-24T15:49:36.96+00:00

Hi!

I have developed an Azure Function, based in python, that is able to connect to an Azure Table Storage and create a similar SQL Server table in an SQL Server DB. In order to do that, I have used the pyodbc library.

The code works perfectly when I execute it in local with VSCode: the Function is able to access to the Storage, get the data, create the SQL Table and insert all the rows. The problem arrives when I try to execute this function app in a ADF pipeline, where I obtain this error:

('HYT00', '[HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')

I’m using an IR that works with other ADF elements and allows to works with the SQL Server DB, and the Azure Function plan we are using is the premium one. I have isolate the error and it happens in the moment of the DB connection (what is very logic to the error description).

Here you can see the code that works executing it in local. “DBAccess” contains the connection chain separated by “;”.

        dbAccess=os.getenv("DBAccess")
        dbAccess=dbAccess.split(";")
        server = dbAccess[0]
        database = dbAccess[1]
        username = dbAccess[2]
        password = dbAccess[3]
        driver= "ODBC Driver 17 for SQL Server"
        global conn
        conn=pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=tcp:'+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password)

Anyone knows what can be happening here?

Thanks!

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
6,305 questions
Azure Functions
Azure Functions
An Azure service that provides an event-driven serverless compute platform.
2,604 questions
{count} votes

1 answer

Sort by: Most helpful
  1. MughundhanRaveendran-MSFT 11,621 Reputation points Microsoft Employee
    2023-01-25T06:15:10.7566667+00:00

    @Torralba-Resino Javier

    Thanks for reaching out to Q&A forum.

    Azure python function worker does come with a ODBC 17 driver, so there are no issues with the drivers

    https://github.com/Azure/azure-functions-docker/blob/dev/host/3.0/buster/amd64/python/python37/python37.Dockerfile#L52

    This issue might occur due to the below reasons

    • The instance of the SQL Server Database Engine is not running. so please check if the SQL server is up and running. Also please check if there are any issues at the SQL side.
    • When there are network problems or firewall protection at the SQL side that is preventing the Function app/ADF from accessing the DB

    Also please try to specify the connection string with pipe mode and check if it helps

    conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};ServerName=Provider=SQLOLEDB.1;SERVER=.\testsv;DATABASE=test;Persist Security Info=False;UID=sa;PWD=xxxxx;Data Source=np: