Failed to connect to Azure SQL Database using Managed Identity: ('HYT00', '[HYT00] [Microsoft][ODBC Driver 18 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')

Question

Hello,

I'm building an Azure Function in Python, this one will update an Azure SQL database. I've done this several times before successfully but this is the first time I have to do it with managed identity and it fails with Timeout.

My Azure Function (xazurefunctioname) has a System-Assigned Managed Identity.

I granted Db Permissions to the DB for the Azure Function.

  CREATE USER [xazurefunctionname] FROM EXTERNAL PROVIDER;
  ALTER ROLE db_datareader ADD MEMBER [xazurefunctionname];
  ALTER ROLE db_datawriter ADD MEMBER [xazurefunctionname];

my SQL server has Microsoft Entra authentication only

When I connect using Azure Data Studio and I use my personal (admin) Credentials, I can connect fine.
When I run my Azure Function locally with the connection string :

self.connection_string = "Driver={ODBC Driver 18 for SQL Server};Server=tcp:xsqlserver.database.windows.net,1433;Database=xdatabase;Authentication=ActiveDirectoryMsi;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=60;"

It always times out.

Failed to connect to Azure SQL Database using Managed Identity: ('HYT00', '[HYT00][Microsoft][ODBC Driver 18 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')

Error in function execution: ('HYT00', '[HYT00] [Microsoft][ODBC Driver 18 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')
Error in function execution: ('HYT00', '[HYT00] [Microsoft][ODBC Driver 18 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')

That's all I get...

I'm struggling already a couple of hours with this.... any clues ?

I'm running Azure Data Studio, VSC all on a Mac.
One of the questions I have is, how is the SQL server aware of the identity that is approaching it?

Answer

Hi Philippe Vlaemminck,

Thanks for reaching out to Microsoft Q&A.

Check if that your SQL Server has appropriate firewall rules to allow traffic from your azure function app (even though it uses MSI). Make sure the necessary IP addresses and Azure services have access.
Ensure that the MI permissions were applied to the correct database, and that the identity you created matches exactly the one assigned to your Azure Function.
Test if the MSI is working properly by acquiring a token from it and testing other services (like Key Vault).

You can use the following Python code jor this:

from azure.identity import ManagedIdentityCredential
credential = ManagedIdentityCredential()
token = credential.get_token("https://database.windows.net/.default")
print(token.token)

Your connection string looks good, but make sure thatAuthentication=ActiveDirectoryMsi is correctly set.
Verify the function app can reach your SQL Server by setting up a diagnostic to test network traffic between the function and the SQL Server.
If your Azure SQL Database is in a VNET, ensure that your Function App is properly integrated into that VNET

How SQL Server Recognizes MI?

When you connect using ActiveDirectoryMsi, azure SQL identifies the Managed Identity from the token provided by AAD. The ODBC driver, in combination with the ManagedIdentityCredential, will obtain a token from AAD, which SQL Server uses to verify the identity. You can confirm this by checking the token retrieved in the ManagedIdentityCredential.

Please 'Upvote'(Thumbs-up) and 'Accept' as an answer if the reply was helpful. This will benefit other community members who face the same issue.

Share via

Failed to connect to Azure SQL Database using Managed Identity: ('HYT00', '[HYT00] [Microsoft][ODBC Driver 18 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')

1 answer

Your answer