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;
    }

Try adding the setting Persist Security Info = True; to your connection string. — Pravallika KV
– Pravallika KV, Commented Oct 6, 2023 at 14:30
Thanks for the response. Getting same error with below connectionString connectionString="Server=tcp:test.database.windows.net;Database=test;Persist Security Info=True;" providerName="System.Data.SqlClient — Prithvi
– Prithvi, Commented Oct 6, 2023 at 16:29
Tried adding Integrated Security=true; to the conn string. Getting this error - Windows logins are not supported in this version of SQL Server. — Prithvi
– Prithvi, Commented Oct 6, 2023 at 17:54

Bhavani · Accepted Answer · 2023-10-17 11:26:54Z

0

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;

enter image description here

Set admin as desired on the SQL Server:

enter image description here

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

Image for reference:

enter image description here

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

enter image description here

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>];

enter image description here

The database successfully connects to the app.

Image for reference:

enter image description here

answered Oct 17, 2023 at 11:26

Bhavani

5,4762 gold badges6 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Prithvi Over a year ago

thanks Bhavani. This is asp.net web api and not .net core. I have edited the original question.

Thomas Over a year ago

also you dont need to inject the token manually: learn.microsoft.com/en-us/sql/connect/ado-net/sql/…

Prithvi Over a year ago

thanks Thomas. Getting error invalid key Authentication.

Bhavani Over a year ago

Could you please provide the whole error?

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related