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://docs.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

Jim Xu Jim Xu · Accepted Answer · 2020-01-03T07:51:51

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:

Create function app project in VS2019
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>;"
  }

}

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.
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;
        }

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

Create an Azure Function
Configure MSI for function app

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>]

Add your connection string in Azure Function app application settings
Publish it with Visual Studio

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

1 Answers