Data Factory + pyodbc: login timeout expired error

Question

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!

Answer 1

@Anonymous

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:

Answer 2

As it is not specified where the SQL Server is located, I want to emphasize that with SQL Server in Azure VM you have two IPs to connect: internal one and a public one.

If you want to connect via a public IP, you need to go to the VM settings in Azure Portal and check the networking rules. Your Function App outbound IPs should be white-listed there. That's the most common root cause of the issue you mentioned.

You may also use Virtual Network integration for your Function App and use internal IP to connect. Usually it is the more common and secure way to achieve your goal.