Querying database table using user assigned managed identity

Question

I'm trying to query data from an Azure SQL database using an Azure Web API, and I can't seem to determine how for format the SQL connection string.

The user assigned managed identity has been added to "Access control" for the SQL Server and added as an Identity for the Web API. In addition, the user assigned managed identity has been added as an identity for the server.

I also added the user assigned managed identity as a contained login and role using:

CREATE USER [<Name of user assigned identity>] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [<Name of user assigned identity>];

I've tried using the following as a connection string:

'Server=tcp:mxtestdbserver.database.windows.net,1433;Initial Catalog={databaseName};Persist Security Info=False;User ID={userAssignedClientName};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Authentication=\"Active Directory Integrated\";'

But I get the following error:

Failed to authenticate the user <userAssignedClientName> in Active Directory (Authentication=ActiveDirectoryIntegrated). Error code 0xunknown_user_type Unsupported User Type 'Unknown'. Please see https://aka.ms/msal-net-up.

NOTE:
I've also tried the following:

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

However, I couldn't see where to specify the User Assigned Managed Identity and I'm getting the following error:

'A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)'

The SQL Server is configured to allow access by azure resources.

Any insight is greatly appreciated.

Answer 1

There is no real difference in the System assigned managed identity vs user assigned managed identity.

You have already assigned the managed identity datareader rights on the database. That is good. Now, in your function (assuming you are using a function; similar for container or other compute resources) you create a connection string only containing the server and the database name. That is it.

Example is documented here: https://learn.microsoft.com/en-us/azure/app-service/tutorial-connect-msi-sql-database?tabs=windowsclient%2Cef%2Cdotnet

If your code if creating a token, it requests the token using the defined managed identity on the resource. No need for you to change your code when switching from system managed to user managed identity.

Answer 2

Got it working using the following + the article provided by baspruijn-alten: https://learn.microsoft.com/en-us/azure/app-service/tutorial-connect-msi-sql-database?tabs=windowsclient%2Cef%2Cdotnet

Azure Settings (Not sure if they are all required)

The UAMI has 'SQL DB Contributor' access to the database.
Is configured as an Identity on the Web App and SQL Server.
Is configured to allow Azure services and resources to access the server.
Is configured as a contained user in the database and has read/write roles.

C# Code
SqlConnection conn = new SqlConnection($"server=tcp:<sqlservername>.database.windows.net;database={databaseName};");
var credential = new DefaultAzureCredential(new DefaultAzureCredentialOptions
{
ManagedIdentityClientId = userAssignedClientId,
ExcludeInteractiveBrowserCredential = true,
ExcludeAzureCliCredential = true,
ExcludeEnvironmentCredential = true,
ExcludeSharedTokenCacheCredential = true,
ExcludeVisualStudioCodeCredential = true,
ExcludeVisualStudioCredential = true,
ExcludeAzurePowerShellCredential = true
});

var token = credential.GetToken(new Azure.Core.TokenRequestContext(new[] { "https://database.windows.net/.default" }));
conn.AccessToken = token.Token;
conn.Open();

string query = "SELECT name FROM sys.columns WHERE object_id = OBJECT_ID('Data')";
using (SqlCommand command = new SqlCommand(query, conn))
{
using (SqlDataReader reader = command.ExecuteReader())
{
...
}
}