How to configure pyodbc connection string to authenticate Azure SQL database server in Azure Functions?

Ash Peng 40 Reputation points
2024-02-09T00:09:33.1566667+00:00

We have a connection to an Azure SQL database in a Python function that runs locally using pyodbc. We are now migrating this function to Azure Functions using a connection string like this:

'Driver={ODBC Driver 17 for SQL Server};Server=<Azure-SQL-db-server-name>;Database=<database-name>;'

Can we use SQL authentication for Azure SQL databases? If so how do we configure the connection string? If, on the other hand, we want to use Microsoft Entra ID for authentication, how do we configure the connection string? Thank you.

Azure SQL Database
Azure Functions
Azure Functions
An Azure service that provides an event-driven serverless compute platform.
4,006 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Amira Bedhiafi 12,141 Reputation points
    2024-02-10T16:23:15.1633333+00:00

    For SQL Server Authentication, you need to include the username and password in the connection string:

    Driver={ODBC Driver 17 for SQL Server};Server=<Azure-SQL-db-server-name>;Database=<database-name>;Uid=<your-username>;Pwd=<your-password>;
    

    For AAD password authentication, use an Azure AD account's username and password:

    Driver={ODBC Driver 17 for SQL Server};Server=<Azure-SQL-db-server-name>;Database=<database-name>;Authentication=ActiveDirectoryPassword;UID=<your-AzureAD-username>;PWD=<your-AzureAD-password>;
    

    If your Azure Functions app is running in Azure, you can use Managed Identity for a more secure and seamless authentication. This method does not require storing credentials in your connection string. First, you need to assign a Managed Identity to your Azure Functions app and grant it access to the Azure SQL Database. Then, your connection string in the function app settings would look something like this:

    Driver={ODBC Driver 17 for SQL Server};Server=<Azure-SQL-db-server-name>;Database=<database-name>;Authentication=ActiveDirectoryMsi;
    
    
    1 person found this answer helpful.
    0 comments No comments