Entity Framework code-first Azure SQL connection in .net Web API

Question

I am getting below error while connecting to Azure SQL from App Service on Azure. Using ASP.NET WEB API not .net core.

Exception Details: System.Data.SqlClient.SqlException: Login failed for user ''. [InvalidOperationException: This operation requires a connection to the 'master' database. Unable to create a connection to the 'master' database because the original database connection has been opened and credentials have been removed from the connection string. Supply an unopened connection.]

Web.config -

connectionString="Server=tcp:xxxxx;Database=xxx;" providerName="System.Data.SqlClient"/>

TestDBContext.cs -

public TestDBContext() : base("name=" + ConfigurationManager.AppSettings["ContextName"]) {

        var conn = (System.Data.SqlClient.SqlConnection)Database.Connection;
        var credential = new Azure.Identity.DefaultAzureCredential(new DefaultAzureCredentialOptions { ManagedIdentityClientId = "xxxxxxxxxxxx" });
        var token = credential.GetToken(new Azure.Core.TokenRequestContext(new[] { "https://database.windows.net/.default" }));
        conn.AccessToken = token.Token;
    }

Answer 1

To connect an Azure SQL Server from an Azure asp.net Web API with managed identity authentication, provide the connection string in the following format in Appsettings.json:

"ConnectionStrings": {
        "QuotesDatabase": "Server=tcp:<servername>.database.windows.net,1433; Database=<databasename>;" }

Use the code below for the connection:

var connectionString = Configuration.GetConnectionString("<connectionstringname>");
                services.AddTransient(a =>{
                    var sqlConnection = new SqlConnection(connectionString);
                    var credential = new DefaultAzureCredential();
                    var token = credential
                            .GetToken(new Azure.Core.TokenRequestContext(
                                new[] { "https://database.windows.net/.default" }));
                    sqlConnection.AccessToken = token.Token;
                    return sqlConnection;

Set admin as desired on the SQL Server:

Choose an administrator account for Azure service authentication to retrieve the token credentials.

Image for reference:

Enable the system-assigned managed identity in the "on" state of the Azure app service.

Log in to the SQL Server with an administrator account, add a user to the database, and assign a role to the user:

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

The database successfully connects to the app.

Image for reference: