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.

Please read the tag summary when choosing them. This question has nothing to do with Azure DevOps. — Daniel Mann
– Daniel Mann, Commented Aug 1, 2023 at 19:45
Did you intend to put all of your URL.create() parameters inside query? They're meant to be named parameters to URL.create() itself, ref: sqlalchemy.dialects.mssql.pyodbc. The parameters inside query tend to get passed to the ODBC Driver as connection string properties so if you wanted to do it that way you'd need to use UID and PWD instead of Username and Password, etc.. (See the Pass through exact Pyodbc string example at the link.) — AlwaysLearning
– AlwaysLearning, Commented Aug 2, 2023 at 8:50
@DanielMann sorry about the mis-tag, I tagged this way because it was working on my local but was not working on Azure so I thought there may be a problem with the Azure configuration. I have removed those tags as the helpful comments have suggested some other options! Thanks for reading. — ZahlenZ
– ZahlenZ, Commented Aug 2, 2023 at 13:42
@AlwaysLearning I'm still pretty new to all of this, didn't realize I had the URL create incorrect. I have switched it out to having them as named parameters, with the query just having the driver. Now it looks like the service account that was set up may be incorrect as it isn't allowed to login. With the original way that I had my URL String set up, with everything in the query, when running this locally would it have just used the windows account I was logged in as on my machine to authenticate? If so this could be why it was working local, and not in azure. Thanks! — ZahlenZ
– ZahlenZ, Commented Aug 2, 2023 at 13:46
@ZahlenZ When you had everything in the query and ran the code locally, SQLAlchemy would have used your Windows account (the one you were logged in as on your machine) for authentication. This is because when you run code on your local machine, it typically uses your local credentials to access the resources. However, when the Azure Function app is running in the Azure cloud environment, it uses a different set of credentials or service account (Managed Service Identity or another account you specified) to authenticate with the on-premise SQL Server. — P Sampath
– P Sampath, Commented Aug 2, 2023 at 17:30

P Sampath · Accepted Answer · 2023-08-03 15:24:31Z

0

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.

enter image description here

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:

enter image description here

In Azure:

enter image description here

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.

edited Aug 3, 2023 at 15:24

answered Aug 2, 2023 at 11:01

P Sampath

4,2652 gold badges7 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ZahlenZ Over a year ago

Thanks for these tips, they were able to get the code working using a SQL account, but it doesn't work while using a Windows Account. The goal is to host the program in Azure and use a Windows Account from our active directory as the service account that will perform the database actions.

P Sampath Over a year ago

@ZahlenZ we use Driver={ODBC Driver 18 for SQL Server};Server={server};UID=myuser;PWD=myPass;Authentication=ActiveDirectoryPassword from the reference MSDOC for more details refer this SO. for ODBC 17 refer.

Collectives™ on Stack Overflow

Python Azure Function app to On Prem Microsoft SQL Server

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related