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, Sep 12 '19 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, Sep 12 '19 at 11:20
You could try to store your password in keyvault, and it is easier. — Joey Cai, Sep 13 '19 at 08:39
Which version of entity framewrok are you using ? Also did you add the `SqlAuthenticationProviders` in your web.config ? — Thomas, Sep 15 '19 at 06:33

score 1 · Answer 1 · answered May 17 '20 at 12:22

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 xxx@yyy.com 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

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

1 Answers1