How do I connect locally to Azure SQL database with Microsoft Entra ID registered app in C#?

Question

Everything I am coding in C# locally using Visual Studio Code, everything else I connect to using C#. I have a Microsoft Entra ID which is an app registration which has a secret assigned, I have the clientId and clientSecret in my local.settings.json, and I use new DefaultAzureCredentials() and everything is done, when I upload my code everything reverts to managed identity and it works.

But I can't get Azure SQL database to work in the same way locally, I have a Microsoft Entra ID, with reader and contributor permissions, I can get the token, but when it opens the connection, I get the error:

Login failed for user ''

This is before any SQL code has been executed.

The Microsoft Entra ID is also set to the admin although happy for to change.

The code used to connect to the database is this:

private readonly string _connectionString = Environment.GetEnvironmentVariable("DB_CONNECTIONSTRING") ?? throw new ArgumentNullException("DB_CONNECTIONSTRING cannot be null");

public async Task ExecuteAsync(DatabaseCommand command, string universeId)
{
    var credential = new DefaultAzureCredential();
    var tokenRequestContext = new TokenRequestContext(new[] { "https://database.windows.net/.default" });

    AccessToken token = await credential.GetTokenAsync(tokenRequestContext);

    using (SqlConnection connection = new SqlConnection(_connectionString))
    {
        connection.AccessToken = token.Token;  // Set the token

        await connection.OpenAsync(); // Open the connection once
        // .....
    }
}

Any suggestions would be appreciated.

Answer 1

ConnectionError: Login failed for user 'token-identified-principal'.

According to the MS document, if the user is not valid in the database or if the user is not the AAD Server Admin, you may get the above error while connecting to the database. Even though your Entra Id has permission, your service principal also should have permissions.

Login into Azure SQL database as administrator add user of service principle to the database and assign role to the user using below query:

create user [<appName>] from external provider;
alter role db_datareader add member [<appName>];
alter role db_datawriter add member [<appName>];

The database will connect successfully as shown below:

For more information you can refer this. You can check the document to know about integrated authentication for Azure SQL database.