Invalid value specified for connection string attribute 'Authentication' (0) (SQLDriverConnect)

@Shashi Kumar Soni

Thank you for reaching out.

The detailed error message you provided indicates an issue with the authentication attribute in the connection string. Double-check the value and make sure it’s set to Authentication=ActiveDirectoryMsi.

Please check if you have enabled managed identity to azure functions.

https://video2.skills-academy.com/en-us/azure/azure-functions/functions-identity-access-azure-sql-with-managed-identity

Steps:

1. Create a Function App

2. Set up the managed identity in the new Function App by enable Identity and saving from portal. It will generate an Object(principal) ID for you automatically.

3. Assign role in Azure SQL database.

Search for your own account and save as admin.

Note: Alternatively, you can search for the function app's name and set it as admin, then that function app would own admin permission on the database and you can skip step 4 and 5 as well.

4. Got to Query editor in database and be sure to login using your account set in previous step rather than username and password. Or step 5 will fail with below exception.

"Failed to execute query. Error: Principal 'xxxx' could not be created. Only connections established with Active Directory accounts can create other Active Directory users."

5. Run below queries to create user for the function app and alter roles. You can choose to alter part of these roles per your demand.

CREATE USER "yourfunctionappname" FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER "yourfunctionappname"
ALTER ROLE db_datawriter ADD MEMBER "yourfunctionappname"
ALTER ROLE db_ddladmin ADD MEMBER "yourfunctionappname"

6. Leverage below sample code to build your own project and deploy to the function app.

Sample Code:

Below is the sample code on how to use Azure access token when run it from local and use managed identity when run in Function app. The token part needs to be replaced with your own. Basically, it is using "pyodbc.connect(connection_string+';Authentication=ActiveDirectoryMsi')" to authenticate with managed identity.

Also,  "MSI_SECRET" is used to tell if we are running it from local or function app, it will be created automatically as environment variable when the function app is enabled with Managed Identity.

The complete demo project can be found from: https://github.com/kevin808/azure-function-pyodbc-MI

import logging
import azure.functions as func
import os
import pyodbc
import struct

def main(req: func.HttpRequest) -> func.HttpResponse:
    logging.info('Python HTTP trigger function processed a request.')
    server="your-sqlserver.database.windows.net"
    database="your_db"
    driver="{ODBC Driver 17 for SQL Server}"
    query="SELECT * FROM dbo.users"
    # Optional to use username and password for authentication
    # username = 'name' 
    # password = 'pass'
    db_token = ''
    connection_string = 'DRIVER='+driver+';SERVER='+server+';DATABASE='+database
    #When MSI is enabled
    if os.getenv("MSI_SECRET"):
        conn = pyodbc.connect(connection_string+';Authentication=ActiveDirectoryMsi')
    
    #Used when run from local
    else:
        SQL_COPT_SS_ACCESS_TOKEN = 1256

        exptoken = b''
        for i in bytes(db_token, "UTF-8"):
            exptoken += bytes({i})
            exptoken += bytes(1)

        tokenstruct = struct.pack("=i", len(exptoken)) + exptoken
        conn = pyodbc.connect(connection_string, attrs_before = { SQL_COPT_SS_ACCESS_TOKEN:tokenstruct })
        # Uncomment below line when use username and password for authentication
        # conn = pyodbc.connect('DRIVER='+driver+';SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)

    cursor = conn.cursor()
    cursor.execute(query) 
    row = cursor.fetchone()

    while row:
        print(row[0])
        row = cursor.fetchone()

    return func.HttpResponse(
            'Success',
            status_code=200
    )