Entity framework throw connection error with azure SQL database with managed Identity

Question

The connection string in web config saves the user name and password but I don't want to save Password in web config.

I am using Entity Framework in my application. Now I am trying to use the Azure SQL database with managed identity.

I tried to use the Secure Azure SQL Database connection from App Service using a managed identity tutorial in the docs

My connection string in web config:-

add name="Context" connectionString="metadata=res:///PubsuiteModel.csdl|res:///PubsuiteModel.ssdl|res://*/PubsuiteModel.msl;provider=System.Data.SqlClient;provider connection string="server=servername.database.windows.net;database=dbname;UID=AnyString;Authentication=Active Directory Interactive;MultipleActiveResultSets=True;App=EntityFramework"" providerName="System.Data.EntityClient"

When I am trying to fetch the records from the db I am getting the below error:-

Error:-The underlying provider failed on Open.

InnerException = Failed to instantiate an authentication provider with type 'Microsoft.Azure.Services.AppAuthentication.SqlAppAuthenticationProvider, Microsoft.Azure.Services.AppAuthentication' for 'ActiveDirectoryInteractive'.

How do I create a connection string for entity framework with azure SQL database with managed Identity?

It''s the one you used, but copied incorrectly. You didn't post your code though, or what you tried, or even if you use ASP.NET or ASP.NET Core. That tutorial shows how to use either ASP.NET or ASP.NET Core. In both cases it shows how to add the necessary provider. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Sep 12, 2019 at 11:17
I am using asp.net.What I have tried is initially I have created Entity framework using azure sql db. It stores username and password in web config but that I don't want. So I have tried the above link but when I fetching any record from the db it is throwing the error. — Abhinav Sharma
– Abhinav Sharma, Commented Sep 12, 2019 at 11:20
You could try to store your password in keyvault, and it is easier. — Joey Cai
– Joey Cai, Commented Sep 13, 2019 at 8:39

Michael · Accepted Answer · 2020-05-17 12:22:28Z

The guide you are referring to is helpful but overly complicated. I was struggling with it myself. What you have to do is actually fairly simple (once you figure it out).

First of all your connection string should obviously be free of any kind of user and password, and should simply look like this below:

Server=tcp:<sql-server-name>.database.windows.net,1433;Initial Catalog=<sql-db-name>;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

Replace the <sql-server-name> and the <sql-db-name> with the names of the corresponding resources in Azure.

Pass a connection to EF instead of the connection string. This is needed as you need to add the access token runtime. See the code snippet below.

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{            
    var connection = new SqlConnection();
    connection.ConnectionString = dbConnString;

    var tokenProvider = new AzureServiceTokenProvider();
    var resourceId = "https://database.windows.net/";
    var token = tokenProvider.GetAccessTokenAsync(resourceId).ConfigureAwait(false).GetAwaiter().GetResult();
    connection.AccessToken = token;
    optionsBuilder.UseSqlServer(connection);                
}

Don't forget to run the SQL commands for binding your managed identity and potential real life DB users to the database.

Set an AD admin user on the SQL server resource, and log in as this user.

Run the queries below and replace <azure-resource-name> when the name of the MI for your app(s). The MI name is default the app name if it is system assigned. Instead of an MI name you can use a principal user name or AAD group name such as [email protected] or "my-ad-group".

CREATE USER "<azure-resource-name>" FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER "<azure-resource-name>";
ALTER ROLE db_datawriter ADD MEMBER "<azure-resource-name>";
ALTER ROLE db_ddladmin ADD MEMBER "<azure-resource-name>";
GO

Collectives™ on Stack Overflow

Entity framework throw connection error with azure SQL database with managed Identity

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related