Azure Function App 8.0 Isolated - SQL exception

Question

I have a function app that connects to an Azure SQL Server through managed identities and Entity Framework. Everything works locally. But I get following exception:

Failure Exception: Microsoft.Data.SqlClient.SqlException (0x80131904): A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - An existing connection was forcibly closed by the remote host.) ---> System.ComponentModel.Win32Exception (10054): An existing connection was forcibly closed by the remote host

This is my connection string:

Server=sql-xxx-test.database.windows.net;Database=XXX-Dev;Authentication=Active Directory Managed Identity;Encrypt=False;

Function App is running in 8.0 isolated. EF is 8.0.4.

Managed Identity has been added to DB:

CREATE USER [func-int-xxx-dev] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [func-int-xxx-dev];
ALTER ROLE db_datawriter ADD MEMBER [func-int-xxx-dev];

Any pointers how to troublshoot or solve this?

Answer 1

This worked for me.

My connectionstring is

"Server=tcp:<servername>.database.windows.net,1433;Initial Catalog=<databasename>;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Authentication=Active Directory Managed Identity";

For reference check this document.

Note :-

• Make sure your Ip address is allowed for connection
• Make sure you are using latest version of EntityFramework and Microsoft.Data.SqlClient.
• Make sure you have assigned role correct managed identity.
• Make sure you are using minimum TLS version 1.2

Enable Managed identity in Azure Function. One Enterprise application Service Principal will be created with your function name.

Assign the role using the Query.

DECLARE @USERNAME nvarchar(128)

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

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

My code:

using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Azure.Functions.Worker;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;


namespace FunctionApp3
{
    public class Function
    {
        private readonly ILogger<Function> _logger;
        private readonly MyDbContext _context;

        public Function(ILogger<Function> logger, MyDbContext context)
        {
            _logger = logger;
            _context = context;
        }

        [Function("Function")]
        public async Task<IActionResult> Run([HttpTrigger(AuthorizationLevel.Anonymous, "get", "post")] HttpRequest req)
        {
            _logger.LogInformation("C# HTTP trigger function processed a request.");

            var data = await _context.Test.ToListAsync();

            // Process or log the fetched data
            foreach (var item in data)
            {
                Console.WriteLine($"Id: {item.Id}, Name: {item.Name}");
            }

            return new OkObjectResult("Welcome to Azure Functions!");
        }
    }
    public class MyDbContext : DbContext
    {
        public MyDbContext(DbContextOptions<MyDbContext> options) : base(options) { }
        public DbSet<Test> Test { get; set; }
    }

    public class Test
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }
}

Program.cs:

using FunctionApp3;
using Microsoft.Azure.Functions.Worker;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Logging;
using Microsoft.EntityFrameworkCore;

var host = new HostBuilder()
    .ConfigureFunctionsWebApplication()
    .ConfigureServices(services =>
    {
        services.AddApplicationInsightsTelemetryWorkerService();
        services.ConfigureFunctionsApplicationInsights();
        services.AddDbContext<MyDbContext>(option => 
        {
            option.UseSqlServer("Server=tcp:<servername>.database.windows.net,1433;Initial Catalog=<databasename>;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Authentication=Active Directory Managed Identity");
        });
    })
    .ConfigureLogging(logging =>
    {
        logging.Services.Configure<LoggerFilterOptions>(options =>
        {
            LoggerFilterRule defaultRule = options.Rules.FirstOrDefault(rule => rule.ProviderName
                == "Microsoft.Extensions.Logging.ApplicationInsights.ApplicationInsightsLoggerProvider");
            if (defaultRule is not null)
            {
                options.Rules.Remove(defaultRule);
            }
        });
    })
    .Build();

host.Run();

.csproj:

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <TargetFramework>net8.0</TargetFramework>
    <AzureFunctionsVersion>v4</AzureFunctionsVersion>
    <OutputType>Exe</OutputType>
    <ImplicitUsings>enable</ImplicitUsings>
    <Nullable>enable</Nullable>
  </PropertyGroup>
  <ItemGroup>
    <FrameworkReference Include="Microsoft.AspNetCore.App" />
    <PackageReference Include="Microsoft.Azure.Functions.Worker" Version="1.20.1" />
    <PackageReference Include="Microsoft.Azure.Functions.Worker.Extensions.Http" Version="3.1.0" />
    <PackageReference Include="Microsoft.Azure.Functions.Worker.Extensions.Http.AspNetCore" Version="1.2.0" />
    <PackageReference Include="Microsoft.Azure.Functions.Worker.Sdk" Version="1.16.4" />
    <PackageReference Include="Microsoft.ApplicationInsights.WorkerService" Version="2.21.0" />
    <PackageReference Include="Microsoft.Azure.Functions.Worker.ApplicationInsights" Version="1.1.0" />
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="8.0.4" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="8.0.4" />
  </ItemGroup>
  <ItemGroup>
    <None Update="host.json">
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
    </None>
    <None Update="local.settings.json">
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
      <CopyToPublishDirectory>Never</CopyToPublishDirectory>
    </None>
  </ItemGroup>
  <ItemGroup>
    <Using Include="System.Threading.ExecutionContext" Alias="ExecutionContext" />
  </ItemGroup>
</Project>

Data:

OUTPUT:

Log stream:

If you are still facing the same error, I would request to create support request with azure