Unable to setup Function App to establish SQL connection

Question

Requirement is to automate synchronization of iMIS business objects to SQL Server tables using an Azure Function so that updates occur nightly without manual intervention.

For this, we created Function App in Azure with pay-as-you-go subscription and .Net runtime stack and then enabled system assigned managed identity to it. We also granted Function App access to access SQL and its been added to sql db group as well and allowed access in firewall that all services/ resources to access server. In azure, we are using Create funnction is portal using azure template, and used Timer Trigger for this. We are running simple code in timer trigger to check if function app is able to connect to SQL database and that connection is established successfully, but keep getting following error :

2025-01-10T19:23:58Z [Information] Executing 'Functions.TimerTrigger1' (Reason='This function was programmatically called via the host APIs.', Id=e10df4da-93b0-4d16-a7a2-7800801ab32d)

2025-01-10T19:23:58Z [Error] Function compilation error

2025-01-10T19:23:58Z [Error] run.csx(2,17): error CS0234: The type or namespace name 'Data' does not exist in the namespace 'Microsoft' (are you missing an assembly reference?)

2025-01-10T19:23:58Z [Error] run.csx(3,7): error CS0246: The type or namespace name 'Azure' could not be found (are you missing a using directive or an assembly reference?)

2025-01-10T19:23:58Z [Error] run.csx(20,34): error CS0246: The type or namespace name 'SqlConnection' could not be found (are you missing a using directive or an assembly reference?)

2025-01-10T19:23:58Z [Error] Executed 'Functions.TimerTrigger1' (Failed, Id=e10df4da-93b0-4d16-a7a2-7800801ab32d, Duration=8ms)

We have installed package Microsoft.Data.SqlClient in Developer tools, but still this error is not getting resolved and as a result of which i am not able to test connection from function app to sql. Following code gives above error :

using System;

using Microsoft.Data.SqlClient;

using Azure.Identity;

using Microsoft.Azure.WebJobs;

using Microsoft.Extensions.Logging;

public static class Function

{

[FunctionName("TestSqlConnectionFunction")]

public static void Run([TimerTrigger("0 */5 * * * *")] TimerInfo myTimer, ILogger log)

{

    log.LogInformation($"C# Timer trigger function executed at: {DateTime.Now}");



    // Connection string with your provided details

    string connectionString = "Server=tcp:ciccccicimis.database.windows.net,1433;Database=iMIS-PROD-Archive;";



    try

    {

        // Create a new SqlConnection object with the connection string

        var connection = new SqlConnection(connectionString);



        // Open the connection to the database

        connection.Open();

        log.LogInformation("SQL Connection successful!");



        // Close the connection

        connection.Close();

    }

    catch (Exception ex)

    {

        // Log any errors

        log.LogError($"Error: {ex.Message}");

    }

}

}

cant use visual studio or cli or other azure cli editor because license isn't there. this needs to run in Azure portal, azure function app itself

Answer 1

@College Service Account KP Thanks for posting your question in Microsoft Q&A, apologize for any inconvenience caused on this.

Based on the error message it seems like the nugget package related to Microsoft.Data.SqlClient is not found and that is the reason the function is failing to execute.

You can follow the below documentations to resolve this error message.

1. Manually install the extensions (Nugget packages).
2. To connect to SQL database from function app over system assigned identity you need to create a user in SQL db but in the above shared details I don't find any information about creating user.
3. I would suggest you follow these detailed documentation steps on how to create a User and connect to SQL from function app.
4. Also, I understand that you cannot use visual studio due to license, but you can leverage the visual studio community free edition which can help you to avoid these build errors.

Hope this helps, let me know if you have any further questions on this.