Search code examples
pythonazureazure-functionsazure-sql-databaseazure-managed-identity

How Can an Azure Timer Function Write Data Received from an API to an Azure Database with Managed Identity

I am working with Azure for the first time and no one else in the company has any idea how functions work so I'm coming here.

I have a task split into 2 phases:
Phase 1) Hit an API on a timer (once per day) and put the data into a pandas dataframe
Phase 2) insert that dataframe into a sql table

Phase 1 is working fine. I have an Azure function set up, it hits the API, the dataframe is made. I can't get phase 2 to work for the life of me though.

The code that I wrote to test locally works fine. It will write to a sqlite db in memory no problem using the built-in Pandas .to_sql function. However, I'm running into problems when I try to replicate that with the connection string for the Azure database.

I tried setting up the connection that would go into the create_engine for sqlalchemy as such:

params = urllib.parse.quote_plus (r"Driver={ODBC Driver 18 for SQL Server};Server=[azure server]; Authentication=Active Directory Managed Identity; Database=[azure database]")
conn_str = 'mssql+pyodbc:///?odbc_connect={}'.format(params)
engine = create_engine(conn_str, echo = False)

Then I just reference the function I wrote that would work with the sqllite and I get a bunch of errors:

2023-02-09T16:47:19.204Z] System.Private.CoreLib: Exception while executing function: Functions.[functionname]. System.Private.CoreLib: Result: Failure
Exception: InterfaceError: (pyodbc.InterfaceError) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

The Database is behind a private endpoint, but using the connection string from the function resource should mean that it can connect, unless I'm entirely mistaken.

I'm running this in VS Code using the Run and Debug because it would give an error otherwise saying it couldn't run.

Solution

  • I created one Azure SQL DB with Azure VM acting as its managed identity like below:-

    Added managed identity VM as a User to my Azure SQL like below:-

    CREATE USER  azurevmmanagedidentity FROM EXTERNAL PROVIDER

ALTER ROLE db_datawriter ADD MEMBER azurevmmanagedidentity

    enter image description here

    Allowed VM's IP in the Azure SQL Networking like below:-

    enter image description here

    For Private endpoint make sure you create a V-net with a bastion host and VM in the same V-net as your Private endpoint and connect to your Azure SQL DB with the VM. Use the same VM as managed identity for your Azure SQL Server by adding it as a user with above SQL Script.

    I ran the below code with Azure Functions Timer trigger without installing ODBC driver inside my VM which is acting as managed identity and received the same error code as yours :-

    Code:-

    
import  datetime

import  logging

  

import  pyodbc

  

import  azure.functions  as  func

  
  

def  main(mytimer: func.TimerRequest) -> None:

utc_timestamp = datetime.datetime.utcnow().replace(

tzinfo=datetime.timezone.utc).isoformat()

  

if  mytimer.past_due:

logging.info('The timer is past due!')

  

logging.info('Python timer trigger function ran at %s', utc_timestamp)

  

conn = pyodbc.connect('Driver={ODBC Driver 17 for SQL Server};'

'Server=tcp:sql-server.database.windows.net,1433;'

'Database=sql-db;'

'Authentication=ActiveDirectoryMsi'

)

  
  

cursor = conn.cursor()

cursor.execute('SELECT * FROM StudentReviews')

  

for  i  in  cursor:

print(i)

  

cursor.close()

conn.close()

  
  

logging.info(cursor.execute)

logging.info('Scheduled task completed')

    Error :-

    enter image description here

    I installed the Microsoft ODBC Driver 17 for SQL Server from the link below in my VM:-

    Download ODBC Driver for SQL Server - ODBC Driver for SQL Server | Microsoft Learn

    enter image description here

    Now, I ran the above code again and got connected to my Azure SQL DB successfully like below:-

    Added Storage account string from Azure Functions > Configuration Settings like below:-

    enter image description here

    
{

"IsEncrypted": false,

"Values": {

"AzureWebJobsStorage": "DefaultEndpointsProtocol=https;AccountName=siliconrgb96f;AccountKey=xxxxxxxxxxxxxxxxxxxxxxxixxyxxCxx5xVxxZxtxkxEESN2/zfO2CYQfBeRHp5q8+6QrqKYRuOlui7OEt+AStGHlbuw==;EndpointSuffix=core.windows.net",

"FUNCTIONS_WORKER_RUNTIME": "python"

}

}

    Ran the timer trigger again and the function ran successfully and gave SQL query output like below:-

    Output:-

    enter image description here

    You can make use of any SQL queries in the cursor.execute line to update your data in SQL with Insert Statements.

    Example code:-

    if ReviewText:

cursor.execute("INSERT INTO Table (Column1, Column2) VALUES (CURRENT_TIMESTAMP, ?)", text )

else:

cursor.execute("INSERT INTO Table (Column1, Column2) VALUES (CURRENT_TIMESTAMP, 'WRONG_RECORD')")

conn.commit()

    Reference:-

    Tutorial: Connect to an Azure SQL server using an Azure Private Endpoint - Azure portal | Microsoft Learn

    Tutorial: Use a managed identity to access Azure SQL Database - Windows - Azure AD - Microsoft Entra | Microsoft Learn