User Assigned Managed Identity for Azure Sql Server

Question

I'm new to Azure Cloud. I'm trying to assign user assigned managed identity to Azure Sql Server for Function App Resource. I have added User who can access Azure Sql Server. Simultaneously I had tried to turn on system identity.

What is exactly being happened is:

1. When I turn on System Identity it shows Login for User '' error.
2. When I turn off System Identity it shows unable to load the proper Managed Identity.
3. I actually need User Assigned Identity. So for that I tried below command in SQL Server I created a user namely UMI1 and added this user in User assigned managed identity.

CREATE USER [UMI1] FROM EXTERNAL PROVIDER;
GO
ALTER ROLE db_datareader ADD MEMBER [UMI1];
ALTER ROLE db_datawriter ADD MEMBER [UMI1];
GO

Connection string contains User ID=UM1

So, I think I having problem in creating user in sql. Any reference or response regarding this issue would be helpful. Thank you in advance

Answer 1

If you are using the Microsoft.Data.SqlClient library (see nuget), you can configure the connectionstring to use managed identity:

• Using Active Directory Managed Identity authentication

The connectionstring will looks like that:

Server=demo.database.windows.net;
Database=testdb;
Authentication=Active Directory Managed Identity;
Encrypt=True;

When using user-assigned identity, you can specify the client_id by adding an extra connection property:

User ID=<Client/App Id of the managed identity>

Answer 2

I have tried with the System Assigned Managed Identity:

1. Created Azure Function App > Switched the System Assigned Managed Identity.
2. Created SQL Server > Selected Set Admin as my account > Then Created the Database > Created the table and added the sample data:

SE [Pravusqltestdb]
GO
CREATE TABLE items (id INT NOT NULL, name VARCHAR(50) NOT NULL, number INT);
INSERT into items VALUES(1, 'Electronics', 10),(2, 'Homeneeds', 20);
Select * from items;

Next the below Query executed:

CREATE USER [PravisNet6FunApp] FROM EXTERNAL PROVIDER;
GO
ALTER ROLE db_datareader ADD MEMBER [PravisNet6FunApp];
ALTER ROLE db_datawriter ADD MEMBER [PravisNet6FunApp];
GO

Here PravisNet6FunApp is the Function App Name.
Next, Modified the Function Code for fetching the access token using SQL Connection:

 using System;  
using System.IO;  
using System.Threading.Tasks;  
using Microsoft.AspNetCore.Mvc;  
using Microsoft.Azure.WebJobs;  
using Microsoft.Azure.WebJobs.Extensions.Http;  
using Microsoft.AspNetCore.Http;  
using Microsoft.Extensions.Logging;  
using Newtonsoft.Json;  
using Microsoft.Azure.Services.AppAuthentication;  
using System.Data.SqlClient;  

  

namespace PravuSystemIdentity  
{  
public static class Function1  
{  
[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.");
var tokenProvider = new AzureServiceTokenProvider();
string accessToken = await tokenProvider.GetAccessTokenAsync("https://pravusqldbserver.database.windows.net/.default");  
log.LogInformation($"accessToken = {accessToken}");  
var sqlconnection = Environment.GetEnvironmentVariable("sqlconnection");
using (SqlConnection conn = new SqlConnection(sqlconnection))  
{  
conn.AccessToken = accessToken;  
conn.Open();  
var statement = $"Select top 2 name from items";  
log.LogInformation($"{statement}");  
using (SqlCommand cmd = new SqlCommand(statement, conn))  
{  
using (SqlDataReader reader = cmd.ExecuteReader())  
{  
while (reader.Read())  
{  
log.LogInformation($"{reader.GetString(0)}");  
}  
}  
}  
}  
return new OkResult();  
}  
}  
}

In the local.settings.json, SQL Connection is defined., Published to Azure Portal Function App and defined the connection string under Function App > Configuration: