Question 104716 in Azure Question posted in
The official documentation to get you started can be found here.

User Assigned Managed Identity for Azure Sql Server

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

Tags:

2

Answers


  1. 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

    Login or Signup to reply.
  2. 0

    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>
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search