Question 103816 in Azure Question posted in
The official documentation to get you started can be found here.

Azure managed identity: Passwordless connection string to Azure SQL database connection error

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:
enter image description here

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:

enter image description here
enter image description here

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:
enter image description here

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    I created a Azure SQL database with below firewall rules:

    enter image description here

    enter image description here

    I set the admin for sql server:

    enter image description here

    I tried to connect to the database without password in SSMS

    enter image description here

    I got sign in page

    enter image description here

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

    enter image description here

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search