Using Azure Active Directory Interactive when authenticating from Azure Functions to Azure SQL DB

Question

I'm trying to authenticate access from an Azure function to Azure SQL DB using am Azure Active Directory managed identity and the Active Directory Interactive . I've successfully used the instructions at https://learn.microsoft.com/en-us/azure/app-service/app-service-web-tutorial-connect-msi when trying to authenticate from an app service to Azure SQL DB, but in that case I can set up the authentication provider declaratively in the Web.config file. There seems to be no Web.config file for Azure Functions. How can I programmatically do the same thing as was done declaratively in the Web.config file for Azure Functions? Or is there a simpler approach to use here? I'm trying to avoid embedding secrets or using Key Value to store secrets, and I want a solution where I can still debug Azure Functions locally in Visual Studio, much as I can for app services.

Thanks, --Bonnie

Answer 1

---

How can I programmatically do the same thing as was done declaratively in the Web.config file for Azure Functions? Or is there a simpler approach to use here?

---

According to my test, if we want to use Azure MSI to connect Azure SQL in Azure function, please refer to the following steps:

1. Create function app project in VS2019

2. Configure local.setting.json

{
  "IsEncrypted": false,
  "Values": {
    "AzureWebJobsStorage": "<your storage connection string>",
    "FUNCTIONS_WORKER_RUNTIME": "dotnet"
  },
  "ConnectionStrings": {
    "SQLConnectionString": "Server=tcp:<server name>.database.windows.net,1433;Initial Catalog=<db name>;"
  }

}

2. Configure MSI for VS

   a. Sign in to Visual Studio and use Tools > Options to open Options.

   b. Select Azure Service Authentication, enter your Azure SQL admin account and select OK.

3. develop the function For example

/* please install sdk :
   Install-Package Microsoft.Azure.Services.AppAuthentication -Version 1.3.1
   Install-Package  System.Data.SqlClient -Version 4.6.1

*/
[FunctionName("Function1")]
        public static async Task<IActionResult> Run(
            [HttpTrigger(AuthorizationLevel.Anonymous, "get", "post", Route = null)] HttpRequest req,
            ILogger log)
        {
            log.LogInformation("C# HTTP trigger function processed a request.");
            string str = "SQLConnectionString";
            string conStr = GetSqlAzureConnectionString(str);
            var azureServiceTokenProvider = new AzureServiceTokenProvider();
            string accessToken = await azureServiceTokenProvider.GetAccessTokenAsync("https://database.windows.net/");

            var conn = new SqlConnection(conStr);
            conn.AccessToken = accessToken;
            string result = "";
            var sql = "select * from StarWars where episode=1";
            using (SqlCommand command = new SqlCommand(sql, conn))
            {
                conn.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {

                    while (reader.Read()) {

                        result = reader.GetString(2);
                    }
                }
            }
            return new OkObjectResult($"Hello, {result}");

        }

        private static string GetSqlAzureConnectionString(string SQLConnectionString)
        {
            string conStr = System.Environment.GetEnvironmentVariable($"ConnectionStrings:{SQLConnectionString}", EnvironmentVariableTarget.Process);
            if (string.IsNullOrEmpty(conStr)) // Azure Functions App Service naming convention
                conStr = System.Environment.GetEnvironmentVariable($"SQLAZURECONNSTR_{SQLConnectionString}", EnvironmentVariableTarget.Process);
            return conStr;
        }

4. Debug the function with Visual Studio. Please note that we need to install Azure Functions Core Tools before our debug it.

Besides, If you want to publish it after you debug, please refer to the following steps

1. Create an Azure Function

2. Configure MSI for function app

3. Configure Azure SQL

   a. Use your Azure Sql AD admin to connect Azure SQL vai SSMS

   b. Add the MSI to the database you need use

   USE [<db name>]
   GO
   create user [<function app name>] from external provider
   ALTER ROLE db_owner ADD MEMBER [<function app name>]
   

4. Add your connection string in Azure Function app application settings

5. Publish it with Visual Studio