Azure SQL Managed Identity

Question

I am trying to access an Azure SQL database using a user managed identity with no success. I followed instructions in

https://learn.microsoft.com/en-us/entra/identity/managed-identities-azure-resources/tutorial-windows-vm-access-sql with the exception that I am using a User Managed Identity not the service principal of a VM.

• Step 1 I created the User Managed Identity, lets call it <UserManagedIdentity>
• Step 2 In SQL Database I created a Contained used and added role access like so

Create User <UserManagedIdentity> From External provider
Alter role db_owner
add member <UserManagedIdentity>

1. Step 3 - I attempted to connect to the database using a C# Dot Net FrameWork App 8,.1 using the latest Microsoft.Data.SQLClient (5.1.2) using the following connection string Server=tcp:myserver.database.windows.net,1433;Initial Catalog=MyDatabase;Persist Security Info=False;MultipleActiveResultSets=True;Encrypt=True;TrustServerCertificate=False;Authentication="Active Directory Managed Identity";Packet Size=32767;User Id = 'UMI Client ID GUID'

When I attempt to connect to the database, I get the following error.

ManagedIdentityCredential authentication unavailable. The requested identity has not been assigned to this resource.

How can I get this process to work, thanks.

Answer 1

Hi Lee Taylor,

Welcome to Microsoft Q&A forum.

As I understand, you are getting error when trying to access Azure SQL Database using Managed Identity.

Could you please ensure that to perform the required resource creation and role management, your account needs "Owner" permissions at the appropriate scope (your subscription or resource group). If you need assistance with role assignment, see Assign Azure roles to manage access to your Azure subscription resources.

The code must run on the VM to be able to access the VM's system-assigned managed identity's endpoint. .NET Framework 4.6.2 or higher or .NET Core 3.1 or higher is required to use this method. Replace the values of AZURE-SQL-SERVERNAME and DATABASE accordingly and add a NuGet reference to the Microsoft.Data.SqlClient library.

using Microsoft.Data.SqlClient;

try

{

//

// Open a connection to the server using Active Directory Managed Identity authentication.

//

string connectionString = "Data Source=<AZURE-SQL-SERVERNAME>; Initial Catalog=<DATABASE>; Authentication=Active Directory Managed Identity; Encrypt=True";

SqlConnection conn = new SqlConnection(connectionString);

conn.Open();

Please let us know if this helped or you have further queries.

Thanks