Azure SQL trigger for Functions configuration problem

Question

I want to read SQL connection string for Azure Function from Azure App Configuration. I do load setting with:

builder.Configuration.AddAzureAppConfiguration(...);

And sqldata is in configuration variables, but the trigger works only if sqldata is defined in local.settings.json.

[SqlTrigger("[dbo].[Function1]", "sqldata")] IReadOnlyList<SqlChange<ToDoItem>> changes, FunctionContext context)

I get this error:

Microsoft.Azure.WebJobs.Host: Error indexing method 'Functions.Function1'. Microsoft.Azure.WebJobs.Extensions.Sql: ConnectionStringSetting 'sqldata' is missing in your function app settings, please add the setting with a valid SQL connection string.

Is it possible to set connection string dynamically? It seems like initialization is done before FunctionsApplicationBuilder is run.

Answer 1

It is not supported to implement Azure App Configuration with Isolated Azure function. Refer the response provided by @fabiocav in GitHub.

I got the same error when I tried to use the SQL connection string fetched from Azure App Configuration in Function signature as below:

public void Run(
    [SqlTrigger("[dbo].[table1]", "sqlconnection")] IReadOnlyList<SqlChange<ToDoItem>> changes,
        FunctionContext context)

• Add the Connection String as the app setting (or) Environment variable in local.settings.json to use it as attribute in function.

• However, you can use the SQL connection string fetched from App configuration in the function code to implement your logic.

Example:

I have created an Isolated Azure function and added App configuration's connection string as Environment variable in local.settings.json.

local.settings.json:

{
    "IsEncrypted": false,
  "Values": {
    "AzureWebJobsStorage": "UseDevelopmentStorage=true",
    "FUNCTIONS_WORKER_RUNTIME": "dotnet-isolated",
    "AppConfigConnectionString": "<App_Configuration_Connection_string>"
  }
}

Program.cs:

var host = new HostBuilder()
    .ConfigureFunctionsWebApplication()
    .ConfigureServices(services =>
    {
        services.Configure<KestrelServerOptions>(options =>
        {
            options.AllowSynchronousIO = true;
        });
    }
    )
    .ConfigureAppConfiguration(options =>
    {
        var connectionString = Environment.GetEnvironmentVariable("AppConfigConnectionString");
        options.AddAzureAppConfiguration(connectionString);
    })
    .Build();

host.Run();

function.cs:

 [Function("Function1")]
 public HttpResponseData Run([HttpTrigger(AuthorizationLevel.Anonymous, "get", "post")] HttpRequestData req)
 {
     _logger.LogInformation("C# HTTP trigger function processed a request.");

     var response = req.CreateResponse(HttpStatusCode.OK);
     response.Headers.Add("Content-Type", "text/plain; charset=utf-8");

     string keyName = "sqlconnection";
     string message = _configuration[keyName];
     using (var connection = new SqlConnection(message))
     {
         connection.Open();
         //Run the query
         _logger.LogInformation("Connected to SQL Server successfully.");
     }

     response.WriteString(message ?? $"Please create a key-value with the key '{keyName}' in Azure App Configuration.");

     return response;
 }

Response:

Functions:

        Function1: [GET,POST] http://localhost:7029/api/Function1

For detailed output, run func with --verbose flag.
[2025-01-28T08:15:42.604Z] Executing 'Functions.Function1' (Reason='This function was programmatically called via the host APIs.', Id=684e4dfe-a6bc-4265-ad26-8dcd8ce6e9ac)
[2025-01-28T08:15:43.206Z] Host lock lease acquired by instance ID '000000000000000000000000F72731CC'.
[2025-01-28T08:15:43.449Z] C# HTTP trigger function processed a request.
[2025-01-28T08:15:55.069Z] Connected to SQL Server successfully.
[2025-01-28T08:16:03.539Z] Executed 'Functions.Function1' (Succeeded, Id=684e4dfe-a6bc-4265-ad26-8dcd8ce6e9ac, Duration=21014ms)