How do I create connection string with App Registration/Service Principal for a Web API using EF Core app with SQL Server using Microsoft Entra MFA

Question

I have a generic ASP.NET Core Web API that uses EF Core to connect to a SQL Server instance that uses Microsoft Entra MFA account.

The connectivity prompt for the SQL Server looks like this:

I have no problem connecting to this database locally using the following connection string and the code:

string connectionString = "Server=tcp:some-server.database.windows.net,1433;Authentication=Active Directory Interactive;Database=some-database;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";
    
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
      => optionsBuilder.UseSqlServer(connectionString, builder =>
        {
            builder.EnableRetryOnFailure(5, TimeSpan.FromSeconds(10), null);
        });

However, every time I connect locally, a browser opens with a prompt where I have to enter my password.

Obviously, that mode won't work once I deploy the app into an App Service or a container.

I know that I have to use a secret token and add Service Principal as SQL Server user somehow.

I have generated Service Principal Name, Secret, Application (Client) ID, Tenant ID, Subscription ID, Object ID, and the Directory ID.

How do I generate a proper connection string for this connection and what other steps I should take to properly deploy the app?

Thank you very much in advance

This question is similar to: Connecting to Azure DB with Active Directory-Universal with MFA Support Authentication in C#. If you believe it’s different, please edit the question, make it clear how it’s different and/or how the answers on that question are not helpful for your problem. — siggemannen
– siggemannen, Commented Nov 4 at 17:42
@DaleK not this and question in particular, and whatever I asked was deleted anyway as I asked this question in a wrong topic — James
– James, Commented Nov 4 at 17:53
It was posted as an opinion-based question, @DaleK , but (as the OP mentions) was deleted as they aren't seeking opinions. (Then the latter duplicate was deleted for the same reason.) — Thom A
– Thom A ♦, Commented Nov 4 at 18:00
Since you have a service principal with a client_id and client_secret have you tried using Authentication=Active Directory Service Principal yet? Ref: Connect to Azure SQL with Microsoft Entra authentication and SqlClient — AlwaysLearning
– AlwaysLearning, Commented Nov 4 at 21:33

James · Accepted Answer · 2025-11-05 00:11:29Z

1

I found a solution, here is the connection string that I had to use and SQL commands I had to run in the SQL Server

string connectionString = "Server=tcp:<server name>.database.windows.net,1433;Initial Catalog=<database name>;Persist Security Info=False;User ID=<application_client_id>;Password=<application_client_secret>;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Authentication=Active Directory Service Principal;";

CREATE USER [sql_user] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [sql_user];
ALTER ROLE db_datawriter ADD MEMBER [sql_user];

answered Nov 5 at 0:11

James

1,3455 gold badges22 silver badges47 bronze badges

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

Collectives™ on Stack Overflow

How do I create connection string with App Registration/Service Principal for a Web API using EF Core app with SQL Server using Microsoft Entra MFA

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related