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

Question

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.

Answer 1

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;