SqlException: Login failed for user 'xx\yy' in a newly created ASP.NET Core 2.2 app

Question

I created a new ASP.NET Core 2.2 MVC app with a Visual Studio 2017 (15.9.7) wizard. I specified Individual Accounts option for Authentication.

After that, the only thing I added to the default template is services.AddAuthentication().AddGoogle in Startup.ConfigureServices method.

Running the app (the same problem under Kestrel and IISExpress), I'm getting this exception when Google authorization is complete:

An unhandled exception occurred while processing the request. SqlException: Login failed for user 'xx\yy'. System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, object providerInfo, string newPassword, SecureString newSecurePassword, bool redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, bool applyTransientFaultHandling, string accessToken)

My appsettings.json is just as it was created by the wizard:

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=aspnet-WebApplicationNetCore2-7F2E7A20-8AE2-4AB3-833A-256AC218C69E;Trusted_Connection=True;MultipleActiveResultSets=true"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Warning"
    }
  },
  "AllowedHosts": "*"
}

Found dozens of solutions on Google but neither of them worked for me.

I tried setting Trusted_Connection=False, running Update-Database in Nuget console but the same error occurs.

Found suggestions to run context.Database.EnsureCreated in Initialize method (ASP.Net Core : Cannot open localdb requested by the login) but I even don't have this method at all in my Startup class.

I also found that "aspnet-WebApplicationNetCore2-7F2E7A20-8AE2-4AB3-833A-256AC218C69E*.*" file does not exist anywhere under C:\Users tree. Does this mean the wizard didn't create the database at all? Didn't even try? Or did try but it failed? Not sure how to determine that.

Trusted Connection implies windows credentials for login. Using .net Core the windows account associated with the process running, is usually the application pool created for that website. Either use an application pool user account that is known to the database or provide credentials to login to the SQL server instance. — Adwaenyth
This is not related imho to .ne core, but the authentication used in the sql server connection string. Look for "Integrated Security=true" vs "user id=XXX;Password=YYY" — Cleptus
@Adwaenyth It's localdb (from what I see in the connection string created by the wizard). xx\yy denotes me. Won't localdb work with my own user? — Alex
@bradbury9 I added "Integrated Security=true" but this changed nothing. The main thing which troubles me is that it's the defaults set by the wizard which don't work.. — Alex
Depends on the environment. If you're using Visual Studio in debug mode, the IIS / Kestrel launched usually would run in your user context and thus use your windows ticket to contact your SQL Server instance. If you run a dedicated IIS it will use the IIS AppPoolIdentity associated. Either way, that user account that is used has to have a proper credentials (with windows login) associated in SQL Server. If the localDB instance isn't running under the same account, you have to create a SQL Server Login associated with that windows account or use username and password to authenticate. — Adwaenyth

Jacob Landis Jacob Landis · Accepted Answer · 2019-05-21T13:35:44

I'm using Identity for my project. Lines/blocks involving AppIdentity include:

In ConfigureServices():

services.AddDbContext<AppIdentityDbContext>
     (options => options.UseSqlServer(
          Configuration["Data:Identity:ConnectionString"]    
     )); // could use raw connection string here. This just references appsetting.json

services.AddIdentity<IdentityUser, IdentityRole>(
     opts =>
     {
          opts.Password.RequiredLength = 4;
          opts.Password.RequireDigit = false;
          opts.Password.RequireUppercase = false;
          opts.Password.RequireNonAlphanumeric = false;
     })
     .AddEntityFrameworkStores<AppIdentityDbContext>();

// unsure, but these might be necessary
services.AddMemoryCache();
services.AddSession();

In Configure():

app.UseSession(); // only use if session and memory cache are indeed necessary
app.UseIdentity(); // I get a warning to use UseAuthentication(), because this will be replaced in the future, but it works for now

I'm not sure this is what you need but I hope it helps. Good luck!

SqlException: Login failed for user 'xx\yy' in a newly created ASP.NET Core 2.2 app

1 Answers