Microsoft Azure provides a secure, credential-less connection string to Azure SQL databases using its managed identity feature. This feature enables users to securely connect to their Azure SQL database without having to use passwords. It also allows them to store and manage their secrets in the key vault, which is a secure storage service provided by Microsoft. Additionally, it allows them to access third party APIs securely without having to worry about the security of their credentials.

This illustrates well the current context:

The problem is that when trying to access the API running locally, without the App Service, trying to access the SQL Server database that is hosted in Azure, the request returns an error while trying to authenticate to the database it seems while returning this error: SqlException: Login failed for user '<token-identified principal>'.

Also getting the error when trying to connect to the DB using SSMS:

I am wondering what steps I should take to get this to work locally before testing that it works when packaged in a container and deployed to App Service.

Here is my code in Program.cs:

var keyVaultEndpoint = builder.Configuration.GetSection("KeyVault").GetValue<string>("VaultURI");

if (!string.IsNullOrEmpty(keyVaultEndpoint))
{
    var azureServiceTokenProvider = new AzureServiceTokenProvider();
    var keyVaultClient = new KeyVaultClient(new KeyVaultClient.AuthenticationCallback(azureServiceTokenProvider.KeyVaultTokenCallback));
    builder.Configuration.AddAzureKeyVault(keyVaultEndpoint, keyVaultClient, new DefaultKeyVaultSecretManager());
}


// Get the migrations assembly name using reflection
var migrationsAssemblyName = typeof(Program).GetTypeInfo().Assembly.GetName().Name;

// Add the database context, in this case Sql Server   Entity Framework Core DbContext
builder.Services.AddSqlServerDatabase(builder.Configuration.GetConnectionString("AzureDbConnection"), migrationsAssemblyName);

Here is my appsettings.json config:

CodePudding user response：

For managed idnetity follow the following steps:

• Enable managed idenity in your web app see the instructions here.
• In your Azure SQL allow Azure services to access the resource. See here
• The last step is to assign permissions against your identity in your SQL DB. Run the following commands:

CREATE USER [<identity-name>] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [<identity-name>];
ALTER ROLE db_datawriter ADD MEMBER [<identity-name>];
ALTER ROLE db_ddladmin ADD MEMBER [<identity-name>];
GO

Add appropriate roles as per your need. Here [<identity-name>] is the name of your managed identity.

You can find more detailed instructions here

CodePudding user response：

I created a Azure SQL database with below firewall rules:

I set the admin for sql server:

I tried to connect to the database without password in SSMS

I got sign in page

I selected password option and entered the password it connected successfully.

As per your error the user you have entered is not the AAD server admin. create an Azure AD-based contained database user (other than the server administrator that owns the database, connect to the database with an Azure AD identity. add the permissions to the user.

If you want to Connect to Azure SQL Database with Password less connection string. Refer this it may help for you.