We worked on a service request that our customer trying to enable their Python application, hosted on Azure App Service, to connect securely to Azure SQL Database using a user-assigned managed identity. They attempted to use the Microsoft ODBC Driver for SQL Server with the managed identity for authentication.
We worked on a service request that our customer trying to enable their Python application, hosted on Azure App Service, to connect securely to Azure SQL Database using a user-assigned managed identity. They attempted to use the Microsoft ODBC Driver for SQL Server with the managed identity for authentication.
During our troubleshooting process we found several issues/error messages causing by an incorrect settings in the connection string:
- The initial connection string used the 'ActiveDirectoryInteractive' authentication method, which is not compatible with managed identities.
The correct approach is to use 'ActiveDirectoryMsi' or 'ActiveDirectoryManagedIdentity' for system/user-assigned managed identities.
-
Switching to 'ActiveDirectoryMsi' led to a pyodbc error: pyodbc.Error: (FA005, [FA005] [Microsoft][ODBC Driver 18 for SQL Server]Cannot use Access Token with any of the following options: Authentication, Integrated Security, User, Password. (0) (SQLDriverConnect)). The FA005 error message indicated a mismatch between the use of an access token and the connection string properties. Specifically, when passing an access token, the connection string must not include conflicting authentication parameters such as User, Password, or Integrated Security.
Microsoft