Function App throws error when attempting to connect to Azure SQL Server

Question

Overview of problem

• I'm building a python function in VSCode that connects to an Azure SQL Server database, creates dataframes from queries, and saves those dataframes as CSVs to blob storage. SQL Server credentials are stored in a key vault. Connections to the key vault and storage blob are managed with my admin credentials (local) or service principals (deployed in Azure).
• The entire function works as expected in my local environment
• When I deploy to Azure, the function fails when it attempts to access the SQL Server. It throws the message, "exception caught attempting to connect to SQL DB"
  • The deployed function is able to collect the credentials from Key Vault and is able to connect to the storage blob.
  • Run doesn't catch specific SQLAlchemy exceptions (removed SQLAlchemy try/catch from code)
• On Azure SQL Server, Allow Azure services and resources to access the server is checked.

What I'm looking for

• How to diagnose the problem. Am I missing an exception catch, are there logs that will tell me more about the problem accessing the server, etc?

App code

import azure.functions as func
import logging
from azure.identity import DefaultAzureCredential
from azure.keyvault.secrets import SecretClient
import pandas as pd
from azure.storage.blob import *
from sqlalchemy import URL
from sqlalchemy import create_engine
from sqlalchemy.exc import DataError, IntegrityError, OperationalError, SQLAlchemyError

app = func.FunctionApp(http_auth_level=func.AuthLevel.ANONYMOUS)

@app.route(route="odh_to_snowblob")
def odh_to_snowblob(req: func.HttpRequest) -> func.HttpResponse:
    logging.info('Python HTTP trigger function processed a request.')

    # parameters
    # tables: tables to move
    # env: dev or prod
 
    name = req.params.get('tables')
    if not name:
        try:
            req_body = req.get_json()   
        except ValueError:
            pass
        else:
            name = req_body.get('tables')

    env = req.params.get('env')
    if not env:
        try:
            req_body = req.get_json()   
        except ValueError:
            pass
        else:
            env = 'dev' #req_body.get('env')
    
    if name and env:
        credential = DefaultAzureCredential()

        #get secrets from key vault
        #cached credentials (local) or managed identity
        try:
            key_vault_url = "https://name-of-vault"
            client = SecretClient(vault_url=key_vault_url, credential = credential)
            
            sqlusername = client.get_secret("odh-user").value
            sqlpassword = client.get_secret("odh-pwd").value

        except: 
            logging.info("exception caught attempting to get secrets from AKV")
            return func.HttpResponse("exception caught attempting to get secrets from AKV", status_code=500)    

        if env == 'prod':
            dbname = "name of prod database"
        else:
            dbname = "name of dev database" 
    
        #connect to blob storage 
        try:  
            storage_blob_url = "https://storage blob name"
            blob_service = BlobServiceClient(storage_blob_url, credential=credential)

        except:
            logging.info("exception caught attempting to connect to blob storage")
            return func.HttpResponse("exception caught attempting to connect to blob storage", status_code=500) 

        try:
            odh_connection_string="DRIVER={ODBC Driver 18 for SQL Server};Server=tcp:" + dbname + ",1433;Database=odh_main;Uid=" + sqlusername + ";Pwd="+ sqlpassword + ";Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;"
            connection_url = URL.create("mssql+pyodbc", query={"odbc_connect": odh_connection_string})

            engine = create_engine(connection_url)

        except:
            logging.info("exception caught attempting to connect to SQL DB") 
            return func.HttpResponse("exception caught attempting to connect to SQL DB", status_code=500)

# connects to database and reads tables
# code removed

    else:
        return func.HttpResponse("This HTTP triggered function executed successfully. Pass a name in the query string or in the request body for a personalized response.",status_code=200)

Answer 1

Hello @adm-dsch

To diagnose the problem, you can check the logs to see if there are any specific SQLAlchemy exceptions that are being caught.

You can also try adding a try/except block around the code that connects to the SQL Server to catch any exceptions that might be occurring.

Additionally, you can check if the Allow Azure services and resources to access the server option is enabled on your Azure SQL Server.

This option needs to be enabled to allow your function app to access the SQL Server.

I hope this helps.