Cannot open database "Identity" requested by the login. The login failed. Login failed for user 'DESKTOP\User'

Question

So I've been getting this error when I try to run my project... The project was sent to me by one of my group members.

" Cannot open database "Identity" requested by the login. The login failed. Login failed for user "

The project itself is a web application using MVC. We have a database for a list of clubs/players. All I am trying to do currently is just to run it to see what my group has completed, but it keeps showing me the error above. I've searched online extensively to no avail which is why I am posting here to seek help.

SQL Server Object Explorer

The following are the:

IdentitySeedData.cs

using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Identity;
using Microsoft.Extensions.DependencyInjection;

namespace P0.Models
{
    public static class IdentitySeedData
    {
        private const string adminUser = "Admin";
        private const string adminPassword = "Secret123$";
        public static async void EnsurePopulated(IApplicationBuilder app)
        {
            UserManager<IdentityUser> userManager = app.ApplicationServices
                .GetRequiredService<UserManager<IdentityUser>>();

            IdentityUser user = await userManager.FindByNameAsync(adminUser);

            if (user == null)
            {
                user = new IdentityUser("Admin");
                await userManager.CreateAsync(user);
            }
        }
    }
}

AppIdentityContext.cs

using Microsoft.AspNetCore.Identity;
using Microsoft.AspNetCore.Identity.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore;


namespace P0.Models
{
    public class AppIdentityDbContext : IdentityDbContext<IdentityUser>
    {
        public AppIdentityDbContext(DbContextOptions<AppIdentityDbContext> options)
            : base(options) { }
    }
}

Program.cs

using Microsoft.AspNetCore;
using Microsoft.AspNetCore.Hosting;

namespace P0
{
    public class Program
    {
        public static void Main(string[] args)
        {
            BuildWebHost(args).Run();
        }

        public static IWebHost BuildWebHost(string[] args) =>
            WebHost.CreateDefaultBuilder(args)
                .UseStartup<Startup>()
                .UseDefaultServiceProvider(options =>
                options.ValidateScopes = false)
                .Build();
    }
}

Startup.cs

using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.Extensions.DependencyInjection;
using P0.Models;
using Microsoft.Extensions.Configuration;
using Microsoft.EntityFrameworkCore;
using Microsoft.AspNetCore.Identity;

namespace P0
{
    public class Startup
    {
        public Startup(IConfiguration configuration) =>
            Configuration = configuration;

        public IConfiguration Configuration { get; }
        // This method gets called by the runtime. Use this method to add services to the container.
        // For more information on how to configure your application, visit https://go.microsoft.com/fwlink/?LinkID=398940
        public void ConfigureServices(IServiceCollection services)
        {
            services.AddDbContext<ApplicationDbContext>(options =>
                options.UseSqlServer(
                    Configuration["Data:ConnectionStrings:SoccerClubs"]));
            services.AddDbContext<AppIdentityDbContext>(options =>
                options.UseSqlServer(
                    Configuration["Data:ConnectionStrings:AppIdentityDbContext"]));
            services.AddIdentity<IdentityUser, IdentityRole>()
                .AddEntityFrameworkStores<AppIdentityDbContext>()
                .AddDefaultTokenProviders();
            services.AddTransient<IClubRepository, EFClubRepository>();
            services.AddTransient<IPlayerRepository, EFPlayerRepository>();
            services.AddMvc();
        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IHostingEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }

            app.UseStaticFiles();
            app.UseAuthentication();
            app.UseMvc(routes =>
            {
                routes.MapRoute(
                    name: "default",
                    template: "{controller=Home}/{action=Index}/{id?}");
            });
            SeedData.EnsurePopulated(app);
            IdentitySeedData.EnsurePopulated(app);
        }
    }
}

appsetting.json

{
  "Data": {
    "ConnectionStrings": {
      "SoccerClubs": "Server=(localdb)\\MSSQLLocalDB;Database=SoccerClubs;Trusted_Connection=True;MultipleActiveResultSets=true",
      "AppIdentityDbContext": "Server=(localdb)\\MSSQLLocalDB;Database=Identity;Trusted_Connection=True;MultipleActiveResultSets=true"
    }
    }
}

From your screenshot,It seems you do not have the Identity database which caused the error.Please add-migration and update-database to create the database.Refer to:https://stackoverflow.com/a/43795831/11398810 — Rena, Apr 10 '20 at 02:05

score 0 · Accepted Answer · answered Apr 09 '20 at 23:11

Your application is using a "Trusted connection" to the SQL Server database. So it is trying to login with the current user of the computer.

You need to go into SQL Server Object Explorer or SQL Server Management Studio and add 'DESKTOP\User' as a login to SQL Server and a user in your database.

Here are the steps

https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/create-a-database-user?view=sql-server-ver15#SSMSProcedure

Screenshot of the add user page

https://serverfault.com/questions/366492/how-can-i-add-myself-to-my-local-sql-servers-windows-authentication

Make sure to go into the "User Mapping" tab and tick the database you want to use. If this is development you can tick "db_owner" so you have all permissions.

score 0 · Answer 2 · answered Apr 11 '20 at 14:13

Thanks for all the help. I didn't want to mark this resolved without posting what I did to have my problem fixed...

I got my fix by force adding the database into the server manually via SQL Server Object Explorer, doing the in Package Manager Console:

update-database Initial -context AppIdentityDBContext

Followed by the SQL Query into the Server/Database:

CREATE TABLE [dbo].[AspNetRoles] (
    [Id]                NVARCHAR(256) NOT NULL,
    [ConcurrencyStamp]  NVARCHAR(MAX) NULL,
    [Name]              NVARCHAR(256) NULL,
    [NormalizedName]    NVARCHAR(256) NULL,
    CONSTRAINT [PK_AspNetRoles] PRIMARY KEY ([Id])
);

CREATE TABLE [dbo].[AspNetUsers] (
[Id]                   NVARCHAR (256) NOT NULL,
[AccessFailedCount]    INT            NOT NULL,
[ConcurrencyStamp]     NVARCHAR (MAX) NULL,
[Email]                NVARCHAR (256) NULL,
[EmailConfirmed]       BIT            NOT NULL,
[LockoutEnabled]       BIT            NOT NULL,
[LockoutEnd]           DATETIME       NULL,
[NormalizedEmail]      NVARCHAR (256) NULL,
[NormalizedUserName]   NVARCHAR (256) NULL,
[PasswordHash]         NVARCHAR (MAX) NULL,
[PhoneNumber]          NVARCHAR (MAX) NULL,
[PhoneNumberConfirmed] BIT            NOT NULL,
[SecurityStamp]        NVARCHAR (MAX) NULL,
[TwoFactorEnabled]     BIT            NOT NULL,
[UserName]             NVARCHAR (256) NOT NULL,
CONSTRAINT [PK_AspNetUsers] PRIMARY KEY CLUSTERED ([Id] ASC)
);

CREATE TABLE [dbo].[AspNetRoleClaims] (
[Id]         INT            IDENTITY (1, 1) NOT NULL,
[ClaimType]  NVARCHAR (MAX) NULL,
[ClaimValue] NVARCHAR (MAX) NULL,
[RoleId]     NVARCHAR (256) NOT NULL,
CONSTRAINT [PK_AspNetRoleClaims] PRIMARY KEY CLUSTERED ([Id] ASC),
CONSTRAINT [FK_AspNetRoleClaims_AspNetRoles_RoleId] FOREIGN KEY ([RoleId]) REFERENCES [dbo].[AspNetRoles] ([Id]) ON DELETE CASCADE
);

CREATE TABLE [dbo].[AspNetUserClaims] (
[Id]         INT            IDENTITY (1, 1) NOT NULL,
[ClaimType]  NVARCHAR (MAX) NULL,
[ClaimValue] NVARCHAR (MAX) NULL,
[UserId]     NVARCHAR (256) NOT NULL,
CONSTRAINT [PK_AspNetUserClaims] PRIMARY KEY CLUSTERED ([Id] ASC),
CONSTRAINT [FK_AspNetUserClaims_AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE
);

CREATE TABLE [dbo].[AspNetUserLogins] (
[LoginProvider] NVARCHAR (128) NOT NULL,
[ProviderKey]   NVARCHAR (128) NOT NULL,
[ProviderDisplayName]   NVARCHAR (128) NULL,
[UserId]        NVARCHAR (256) NOT NULL,
CONSTRAINT [PK_AspNetUserLogins] PRIMARY KEY CLUSTERED ([LoginProvider] ASC, [ProviderKey] ASC, [UserId] ASC),
CONSTRAINT [FK_AspNetUserLogins_AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE
);

CREATE TABLE [dbo].[AspNetUserRoles] (
[UserId]    NVARCHAR (256) NOT NULL,
[RoleId]    NVARCHAR (256) NOT NULL,
CONSTRAINT [PK_AspNetUserRoles] PRIMARY KEY CLUSTERED ([UserId] ASC, [RoleId] ASC),
CONSTRAINT [FK_AspNetUserRoles_AspNetRoles_RoleId] FOREIGN KEY ([RoleId]) REFERENCES [dbo].[AspNetRoles] ([Id]) ON DELETE CASCADE,
CONSTRAINT [FK_AspNetUserRoles_AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE
 );

CREATE TABLE [dbo].[AspNetUserTokens] (
[UserId]    NVARCHAR (256) NOT NULL,
[LoginProvider] NVARCHAR (256) NOT NULL,
[Name]  NVARCHAR (256) NOT NULL,
[Value] NVARCHAR (MAX) NOT NULL,
CONSTRAINT [PK_AspNetUserTokens] PRIMARY KEY CLUSTERED ([UserId] ASC, [LoginProvider] ASC, [Name] ASC),
CONSTRAINT [FK_AspNetUserTokens_AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE
 );

GO
CREATE NONCLUSTERED INDEX [IX_AspNetRoleClaims_RoleId]
ON [dbo].[AspNetRoleClaims]([RoleId] ASC);

GO
CREATE UNIQUE NONCLUSTERED INDEX [RoleNameIndex]
ON [dbo].[AspNetRoles]([NormalizedName] ASC)
WHERE [NormalizedName] IS NOT NULL;

GO
CREATE NONCLUSTERED INDEX [IX_AspNetUserClaims_UserId]
ON [dbo].[AspNetUserClaims]([UserId] ASC);

GO
CREATE NONCLUSTERED INDEX [IX_AspNetUserLogins_UserId]
ON [dbo].[AspNetUserLogins]([UserId] ASC);

GO
CREATE NONCLUSTERED INDEX [IX_AspNetUserRoles_RoleId]
ON [dbo].[AspNetUserRoles]([RoleId] ASC);

GO
CREATE NONCLUSTERED INDEX [EmailIndex]
ON [dbo].[AspNetUsers]([NormalizedEmail] ASC);

GO
CREATE UNIQUE NONCLUSTERED INDEX [UserNameIndex]
ON [dbo].[AspNetUsers]([NormalizedUserName] ASC)
WHERE [NormalizedUserName] IS NOT NULL;

Cannot open database "Identity" requested by the login. The login failed. Login failed for user 'DESKTOP\User'

2 Answers2