Connect to Azure SQL from Azure function using pypyodbc

Question

I am trying to connect to an azure sql db from an azure function. I am using pypyodbc. I am not able to find the syntax for connecting to it. I have tried

import pypyodbc

def main(req):
    # Set up the database connection
    connection_string = 'Driver={ODBC Driver 17 for SQL Server};Server=tcp:<server_name>.database.windows.net,1433;Database=<database_name>;Authentication=ActiveDirectoryManagedIdentity;'
    connection = pypyodbc.connect(connection_string)

    # Execute a SQL query
    cursor = connection.cursor()
    cursor.execute('SELECT * FROM <table_name>')
    rows = cursor.fetchall()

    # Close the database connection
    connection.close()

    # Return the results
    return str(rows)

This gives the error

(08001, '[08001] [Microsoft][ODBC Driver 17 for SQL Server]Invalid value specified for connection string attribute Authentication') Traceback (most recent call last):

What is the correct syntax, cannot find it.

Answer 1

This is the correct code when using pypyodbc to connect from azure functions to azure sql db using managed idenitity

import pypyodbc

server = '<your_server_name>.database.windows.net'
database = '<your_database_name>'
driver = '{ODBC Driver 17 for SQL Server}'
auth = 'ActiveDirectoryMsi'

connection_string = (
    'DRIVER=' + driver + ';'
    'Server=' + server + ';'
    'Database=' + database + ';'
    'Authentication=' + auth + ';'
)

cnxn = pypyodbc.connect(connection_string)

print("Connected")