I created an Azure function via the portal and want to open a connection to a Sql Server using an Active Directory Managed Identity. In order for this connection string to work, I have to use Microsoft.Data.SqlClient. But when I try to use this library inside of Azure, the function stops working.

It is as simple as adding:

using Microsoft.Data.SqlClient

to the top of a newly created Azure function inside the portal. I can't use the standard System.Data.SqlClient namespace as when I do a new connection string, it doesn't like the connection string of

Server=db.database.windows.net; Authentication=Active Directory Managed Identity;Encrypt=True;Database=myDb

How can I open a Sql Connection using the Active Directory Managed Identity authentication method inside an Azure function?

CodePudding user response：

You need to add package references in function.proj file and upload it into function app then packages will be added to code. Below are steps I followed to connect sql database and retrieving data from a table.

1. Have created sql data base with one table.

2. Next created a function app with run time stack .Net and version 3.1.

3. Added Sql data base connection string to function app configuration as shown below.

4. Created a Http trigger function and replaced default code with following code,

#r "Newtonsoft.Json"

#r "System.Configuration"

using System;

using System.Data;

using System.Net;

using Newtonsoft.Json;

using System.Data.SqlClient;

using Microsoft.Extensions.Configuration;

using System.Configuration;

using Microsoft.AspNetCore.Mvc;

using Microsoft.Extensions.Primitives;

public  static  async Task<object> Run(HttpRequestMessage req, ILogger log)

{

string responseMessage;

//We retrieve the id field, which comes as a parameter to the function, by deserializing req.Content.

string jsonContent = await req.Content.ReadAsStringAsync();

dynamic data = JsonConvert.DeserializeObject(jsonContent);

//If there is no username, we return the error message.

var connectionString = Environment.GetEnvironmentVariable("SqlConnection", EnvironmentVariableTarget.Process);

//Azure SQLDB Log

var logAdded = true;

try

{

//We get the Connection String in the Function App Settings section we defined.

using(SqlConnection connection = new SqlConnection(connectionString))

{

//Opens Azure SQL DB connection.

connection.Open();

string qs = $"SELECT * FROM [dbo].[Persons] where [Personid] = 1";

SqlCommand command = new SqlCommand(qs, connection);

string queryop = "";

using (SqlDataReader reader = command.ExecuteReader())

{

queryop = sqlDatoToJson(reader);

}

responseMessage = (queryop);

connection.Close();

}

}

catch(Exception e)

{

logAdded = false;

log.LogError(e.ToString());

responseMessage = e.ToString();

// connection.Close();

}

return  new OkObjectResult(responseMessage);

}

static String sqlDatoToJson(SqlDataReader dataReader)

// transform the returned data to JSON

{

var dataTable = new DataTable();

dataTable.Load(dataReader);

string JSONString = string.Empty;

JSONString = JsonConvert.SerializeObject(dataTable);

return JSONString;

}

5. Added below code in a file with name function.proj and uploaded to function using upload option.

<Project Sdk="Microsoft.NET.Sdk">
<PropertyGroup>
<TargetFramework>netstandard2.0</TargetFramework>
</PropertyGroup>
<ItemGroup>
<PackageReference Include="Dapper" Version="2.0.78" />
<PackageReference Include="Microsoft.Data.SqlClient" Version="4.2.1.0" />
<PackageReference Include="Microsoft.WindowsAzure.ConfigurationManager" Version="3.2.3" />
</ItemGroup>
</Project>

6. Once file uploaded, you can see file in dropdown. 7. Tested function and able to get data from data base, Reference link