How to specify a username using connection string in Entity Framework

Question

My ASP.Net Core app keeps trying to login into database using a "default" username even though I specify a different user in a connection string, which I am using. That is I keep getting the same exception that says:

An unhandled exception occurred while processing the request. SqlException: Cannot open database "testDB" requested by the login. The login failed. Login failed for user 'DESKTOP-555DDD\skorejen'.

Even though I am using a connection string that has a different username defined.

How can I fix it so that my app logins with the connection-string specified username?

Connection String:

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=testDB;User Id=myusername;Trusted_Connection=True;"
  },

ASP.Net Core Startup.cs file:

public void ConfigureServices(IServiceCollection services)
        {
             services.AddDbContext<OrderContext>(options => options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));
        }

When using a Tructed Connection, the credentials of the authenticated Windows user is used; the username and password options will be ignored. You either need to have the application run under the appropriate AD account (via impersonation), add the users that would be using the application to the valid logins (which would likely be easier by using AD groups, rather than adding each of their logins) or use SQL Authetication instead. — Thom A
– Thom A ♦, Commented Jul 17, 2019 at 11:14

Reyan Chougle · Accepted Answer · 2020-08-24 11:40:55Z

14

Your ConnectionStrings has to be in below formats:

With credentials (SQL authentication):

"ConnectionStrings": {
  "DefaultConnection": "Server=myServerAddress; Database=myDataBase; User Id=myUsername; Password=myPassword; Trusted_Connection=false; MultipleActiveResultSets=true;"
}

Without credentials (Windows authentication):

"ConnectionStrings": {       
  "DefaultConnection": "Server=myServerAddress; Database=myDataBase; Trusted_Connection=True; MultipleActiveResultSets=true"        
}

edited Aug 24, 2020 at 11:40

answered Jul 17, 2019 at 11:18

Reyan Chougle

5,3813 gold badges37 silver badges65 bronze badges

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

1 Comment

Stephen Hosking Over a year ago

Thanks! I'm using a XAML app.config so had to adapt from this json - which was quite trivial. '<connectionStrings> <add name="AppNameForDb" connectionString="Server= ... MultipleActiveResultSets=true;" providerName="System.Data.SqlClient"/> </connectionStrings>'

Stefan · Accepted Answer · 2019-07-17 11:12:14Z

5

You will need to enable sql authentication mode, add the user to the sql server logins and specify the password in the connection string.

Example connection string:

Server=myServerAddress;Database=myDataBase;User Id=myUsername; Password=myPassword;

(source)

Eanabling sql authentication mode:

answered Jul 17, 2019 at 11:12

Stefan

17.7k11 gold badges65 silver badges86 bronze badges

Collectives™ on Stack Overflow

How to specify a username using connection string in Entity Framework

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related