4

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

Thomas
  • 24,234
  • 6
  • 81
  • 125
Early Bird
  • 73
  • 7
  • so you have a user assigned identity called `UMI1` ? You attached it to the function app ? Also how does your connectionstring looks like ? – Thomas Oct 19 '22 at 20:42
  • you could follow this article to setup your connectionstring with managed identity: https://learn.microsoft.com/en-us/sql/connect/ado-net/sql/azure-active-directory-authentication?view=sql-server-ver16#using-active-directory-managed-identity-authentication – Thomas Oct 19 '22 at 20:46
  • This requires using the `Microsoft.Data.SqlClient` package: https://github.com/dotnet/SqlClient – Thomas Oct 19 '22 at 20:47
  • ConnectionString="Server=demo.database.windows.net; Database=testdb". I have also tried "Server=demo.database.windows.net; Authentication=Active Directory MSI; User Id=UM1; Database=testdb", and having an error that can not generate token if connection string has User Id. If I remove the User Id then also having an error that due to authentication in connection string it can not generate Token. – Early Bird Oct 27 '22 at 11:40
  • Are you using the `Microsoft.Data.SqlClient` library ? – Thomas Oct 27 '22 at 18:35
  • Yes. I'm using Microsoft.Data.SqlClient library for SqlConnection. – Early Bird Oct 28 '22 at 06:49
  • Do you have the issue locally or when the app is deployed ? – Thomas Oct 28 '22 at 06:50
  • also what type of app are you dpeloying? web app / function app ? something else ? – Thomas Oct 28 '22 at 07:04
  • `User Id=UM1`, this should be the cient_id of the managed identity => `User Id=client_id` – Thomas Oct 30 '22 at 06:14

2 Answers2

4

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

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>
Thomas
  • 24,234
  • 6
  • 81
  • 125
  • Yes. when working with User Managed Identity. We need to provide Client ID,, it can authenticate based on the client ID. In case if database can not find proper user. It throws above error – Early Bird Nov 07 '22 at 11:14
0

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:

enter image description here

Pravallika KV
  • 2,415
  • 2
  • 2
  • 7