Python Azure Function app to On Prem Microsoft SQL Server

Question

I have an Azure Function app in Python 3.9 that connects to an API and inserts data into a microsoft sql database. When running the code locally it works as expected. However when the app is running/hosted in Azure it gets an error:

sqlalchemy.exc.DBAPIError: (pyodbc.Error) ('HY000', '[HY000] [Microsoft][ODBC Driver 17 for SQL Server]SSPI Provider: No Kerberos credentials available (default cache: FILE:/tmp/krb5cc_0) (851968) (SQLDriverConnect)')
(Background on this error at: https://sqlalche.me/e/20/dbapi)

From the research I have done most of what is being suggested to do is to configure Kerberos on the linux machine that is hosting the application. I'm pretty new to Azure, and these serverless functions but my impression is that there isn't like a dedicated machine that is hosting these that I can go and configure as many of the suggestions online I have found are pointing to as a solution.

We have a virtual network set up, and a subnet for the function app. It has been tested by the infrastructure team and has been given the green light that it works and can successfully connect to the on premise infrastructure from Azure Land.

My code:

    user = "my_user"
    password = "my_pass"
    db_name = "my_db"
    port = "default_port"
    host = "fully_qualified_host"
    driver = "ODBC Driver 17 for SQL Server"
    
    sql_statement = "TRUNCATE TABLE adp.stg_hr_workers"

    connection_url = URL.create(
        "mssql+pyodbc",
        query={
            "username": user,
            "password": password,
            "host": host,
            "port": port,
            "database": db_name,
            "driver": driver
        },
    )
    
    engine = create_engine(connection_url, echo=False)

    Session = sessionmaker(bind=engine)

    with Session.begin() as session:
        session.execute(text(sql_statement))

When running locally this truncates the table. When hosting in Azure this give the Kerberos credentialing error.

Answer 1

The error you are encountering is related to authentication when connecting to the on-premises SQL Server. Thank you for the comment @AlwaysLearning.

• Code refers from Python to query a database - Azure SQL Database & SQL Managed Instance.

In MSSql Server local:

server_name = 'sampath23'
database_name = 'sampath'
username = 'sampath'
password = 'Ra@80muravi'
connection_string = f"Driver={{ODBC Driver 17 for SQL Server}};Server={server_name};Database={database_name};Uid={username};Pwd={password};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;"

• Add Trusted_Connection=yes for Trusted_Connection connection for this reference link.
• Create an Azure SQL Sever and Azure database .

import  logging
import  azure.functions  as  func
import  pyodbc
def  main(req:  func.HttpRequest)  ->  func.HttpResponse:
logging.info('Python HTTP trigger function processed a request.')
server_name = 'sampath23.database.windows.net'
database_name = 'sampath'
username = 'sampath'
password = 'Ra@80muravi'
connection_string = f"Driver={{ODBC Driver 17 for SQL Server}};Server={server_name};Database={database_name};Uid={username};Pwd={password};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;"
try:
with  pyodbc.connect(connection_string)  as  conn:
logging.info("Connection to the database established successfully.")
with  conn.cursor()  as  cursor:
try:
cursor.execute("TRUNCATE TABLE stg_hr_workers")
conn.commit()
logging.info("Table 'stg_hr_workers' truncated successfully.")
return  func.HttpResponse("Table 'stg_hr_workers' truncated successfully.",  status_code=200)
except  pyodbc.Error  as  e:
error_message = f"Error occurred while truncating the table: {str(e)}"
logging.error(error_message)
return  func.HttpResponse(error_message,  status_code=500)
except  pyodbc.Error  as  ex:
error_message = f"Error connecting to the database: {str(ex)}"
logging.error(error_message)
return  func.HttpResponse(error_message,  status_code=500)

Output:

In Azure:

token = credential.get_token("https://database.windows.net/.default").token.encode("UTF-16-LE")
token_struct = struct.pack(f'<I{len(token)}s', len(token), token)

# Connect with the token
SQL_COPT_SS_ACCESS_TOKEN = 1256
connString = f"Driver={{ODBC Driver 17 for SQL Server}};SERVER=<server-name>.database.windows.net;DATABASE=<database-name>"
conn = pyodbc.connect(connString, attrs_before={SQL_COPT_SS_ACCESS_TOKEN: token_struct})

• We use Driver={ODBC Driver 18 for SQL Server};Server={server};UID=myuser;PWD=myPass;Authentication=ActiveDirectoryPassword;Encrypt=yes; from the reference MSDOC for more details refer this SO.For ODBC 17 refer this MSDOC.