Does Timed Trigger Azure Function in Isolated Process support identity-based sql connection for EF6

Question

Hi!

So I have a simple .NET6 Azure Function (v4) isolated process with TimerTrigger which connect to an Azure SQL database with Entity Framework. The problem is that it can't connect to the database using managed identity the same way I did for Azure App Services.

Here is the error message from Microsoft.Data.SqlClient.SqlException

Exception while executing function: MyFunction Login failed for user '<token-identified principal>'.

Since I'm just getting familiar with Azure Function hosting context, I start wondering if it's supported ?

The FunctionApp in Azure is able to access a keyvault with managed identity but not the sql database.

The sql database connection string is read from that same keyvault which actually that same connection is read by web app service using managed identity successfully.

The sql database user was created using the managed identity name (just like the other working app service...) and grant the proper roles to (db_datareader, db_datawriter).

Here is the azure function app package references

<PackageReference Include="Microsoft.Azure.Functions.Worker" Version="1.20.0" />
<PackageReference Include="Microsoft.Azure.Functions.Worker.Extensions.Timer" Version="4.1.0" />
<PackageReference Include="Microsoft.Azure.Functions.Worker.Sdk" Version="1.16.2" />
<PackageReference Include="Microsoft.ApplicationInsights.WorkerService" Version="2.21.0" />
<PackageReference Include="Microsoft.Azure.Functions.Worker.ApplicationInsights" Version="1.0.0" />
<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="6.0.25" />

Thanks you and let me know if you need more information.

Answer 1

Jacques Boivin Thanks for posting your question in Microsoft Q&A. Azure Functions Isolated model supports identity-based connections that includes SQL and here is doc: Tutorial: Connect a function app to Azure SQL with managed identity and SQL bindings for triggers and bindings.

From the description above, you are using EntityFramework with timer trigger and I assume your code follows code snippet available in https://github.com/Azure/azure-functions-dotnet-worker/tree/main/samples/EntityFramework. If so, please validate Connection String should be similar like below:

For more info about managed identity connection string for SQL, refer Using managed identity authentication. Then validate the managed identity of Azure Function (system-assigned or user-assigned) has access to SQL database (like App Service).

Update for the community:

Jacques Boivin resolved the issue by deleting and recreating the sql db user and role assignment. Refer https://techcommunity.microsoft.com/t5/azure-database-support-blog/aad-auth-error-login-failed-for-user-lt-token-identified/ba-p/1417535 for troubleshooting guide.

---

If you found the answer to your question helpful, please take a moment to mark it as Yes for others to benefit from your experience. Or simply add a comment tagging me and would be happy to answer your questions.

Answer 2

After working around the problem assisted by @MuthuKumaranMurugaachari-MSFT , I found a mismatch between the FunctionName("MyFunction") and the Azure deployed function name.

Note I'm using bicep to deploy azure resource and the function name was here.

resource function 'Microsoft.Web/sites/functions@2020-12-01' = {
  parent: functionApp
  name: 'MyFunction'
  properties: {...}

Then I test but seems to not work still at first... then recreate the SQL database user that match the Azure App Function identity name and add proper permissions using this script

DECLARE @USERNAME nvarchar(128)

SET @USERNAME = 'my-function-system-assign-identity-name'

IF not exists(select * from sys.database_principals where name = @USERNAME)
BEGIN
    EXECUTE('CREATE USER "' + @USERNAME + '" FROM EXTERNAL PROVIDER');
    EXECUTE('ALTER ROLE db_datareader ADD MEMBER "' + @USERNAME + '"');
    EXECUTE('ALTER ROLE db_datawriter ADD MEMBER "' + @USERNAME + '"');
END

And then the Azure function did connect successfully using managed identity with the database.