I'm trying to connect to an Azure SQL Database from a Container App and I'm using the 'Authentication=ActiveDirectoryMsi' setting in my connection string. However, I'm encountering a login failure error:
When using pyodbc, I have the following connection string set up:
```python
conn_str = (
"DRIVER={ODBC Driver 18 for SQL Server};"
"Server=,1433;"
"Database=;"
"Encrypt=yes;TrustServerCertificate=no;"
"HostNameInCertificate=*.database.windows.net;"
"Authentication=ActiveDirectoryMsi;"
"Connection Timeout=30;"
)
conn = pyodbc.connect(conn_str)
credential = ManagedIdentityCredential()
token = credential.get_token("https://database.windows.net/.default").token
token_bytes = token.encode("UTF-16-LE")
token_struct = struct.pack("<I", len(token_bytes)) + token_bytes
SQL_COPT_SS_ACCESS_TOKEN = 1256
conn_str = (
"DRIVER={ODBC Driver 18 for SQL Server};"
"SERVER=,1433;"
"DATABASE=;"
"Encrypt=yes;"
"TrustServerCertificate=no;"
"Connection Timeout=30;"
)
conn = pyodbc.connect(conn_str, attrs_before={SQL_COPT_SS_ACCESS_TOKEN: token_struct})
```
Both attempts lead to the following error:
Connection failed: ('28000', "[28000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Login failed for user ''. (18456) (SQLDriverConnect)").
I've already checked:
- My container app exists as EXTERNAL_USER in the database.
- SQL Server allows Azure Services access.
- My container app has system assigned identity enabled.
What could be going wrong? Everything seems set up correctly, yet I am receiving an error.
3 Answers
Make sure you've assigned the proper access for the Managed Service Identity (MSI) on the database itself. Without the right permissions, the connection will fail.
Instead of using 'Database=', try 'Initial Catalog='. It’s worth a shot!
Actually, for ODBC you should stick with 'Database=
If you're not strictly tied to using pyodbc, consider switching to the new Microsoft driver. It simplifies managing Entra ID tokens and works similarly to pyodbc. Check it out here: [Microsoft SQL Driver](https://github.com/microsoft/mssql-python). This should help streamline your connection process!
I'll definitely take a look, thanks for the suggestion!
Yes, I did assign the proper access.