Can't connect to SQL Managed Instance from Azure Function App (Login timeout expired)

Question

I'm trying to connect to a SQL managed instance from an Azure Function App written in Python. The managed instance is under a Network Security Group and has multiple Inbound-rules that controls access to it. The function app is hosted in the same Azure subscription, and all the Outbound IP Addresses are white-listed in the said security group, with 3342 port access. The function app works when tested in local machine (local machine's IP is white-listed too). But when deployed in cloud, it shows '[HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')'.

I'm using the following code to connect to the database:

server = '<server_name>.windows.net,3342'
database = 'database'
username = 'Admin'
password = "strongpassword"
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';Uid='+username+';Pwd='+ password+';')

Function can be called anonymously, and when I do, there is an error and I see the following in the logs:

Result: Failure Exception: OperationalError: ('HYT00', '[HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)') Stack: File "/azure-functions-host/workers/python/3.9/LINUX/X64/azure_functions_worker/dispatcher.py", line 407, in _handle__invocation_request call_result = await self._loop.run_in_executor( File "/usr/local/lib/python3.9/concurrent/futures/thread.py", line 58, in run result = self.fn(*self.args, **self.kwargs) File "/azure-functions-host/workers/python/3.9/LINUX/X64/azure_functions_worker/dispatcher.py", line 649, in _run_sync_func return ExtensionManager.get_sync_invocation_wrapper(context, File "/azure-functions-host/workers/python/3.9/LINUX/X64/azure_functions_worker/extension.py", line 215, in _raw_invocation_wrapper result = function(**args) File "/home/site/wwwroot/dbConnTest/__init__.py", line 22, in main cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';Uid='+username+';Pwd='+ password+';Connection Timeout=30;')

What should I do? Is this related to ODBC driver version? Or am I using the wrong IP Addresses? I've checked both the IP addresses in Azure Portal > Networking and in Azure CLI with:

az webapp show --resource-group <resource group> --name <function app> --query outboundIpAddresses --output tsv

What am I missing?

Answer 1

Partially solved by Microsoft Support. Function apps on Consumption plans apparently have different Outbound IP addresses than what is given in the Azure portal. So the recommendation was to add the data center of the specific region (of the function app) to the whitelist.

Relevant Documentation

I followed the link, downloaded a JSON file, found the data center IP address prefixes (under 'AzureCloud.' key) and added them to the whitelist.

The connection to DB code is:

server = 'tcp:<server-name>.public.xxxxxxxxxxx.database.windows.net,3342'
database = 'database'
username = 'Admin'
password = "verystrongpass_cantbreakit_nananana"
conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=' + server + ';DATABASE=' + database+';UID=' + username + ';PWD=' + password)
cursor = conn.cursor()