azureazure-functionsazure-managed-identityazure-sql-server

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

Solution

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