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>