Database connection not created in IdentityDbConte

Question

I'm trying to authentication Asp.Net Core 2 Web API with Asp.Net Identity 2. Here's my code for the AuthController and the databasecontext:

public AuthController(UserManager<ApiUser> userManager, SignInManager<ApiUser> signInManager, RoleManager<ApiRole> roleManager
        , IPasswordHasher<ApiUser> passwordHasher, IConfiguration configurationRoot, ILogger<AuthController> logger)
    {
        _userManager = userManager;
        _signInManager = signInManager;
        _roleManager = roleManager;
        _logger = logger;
        _passwordHasher = passwordHasher;
        _configurationRoot = configurationRoot;
    }

public class SecurityContext : IdentityDbContext<ApiUser, ApiRole, int>
{
    public SecurityContext(DbContextOptions<SecurityContext> options)
        : base(options)
    {
    }
}

At runtime, when I inspect the options inside the SecurityContext, I can see the connectionstring property set. But when I debug the _userManager, the database connection is not set.

I have configured the database connection from the Startup.cs as well:

        services.AddDbContext<SecurityContext>(options =>
 options.UseSqlServer(Configuration.GetConnectionString("SystemDbContext")));

        services.AddIdentity<ApiUser, ApiRole>()
            .AddEntityFrameworkStores<SecurityContext>()
            .AddDefaultTokenProviders();

Database connection is set in the appsettings.json:

{
  "ConnectionStrings": {
    "SystemDbContext": "Server=xxx;Database=xxxx;user id=sa;password=xxx;Trusted_Connection=True;MultipleActiveResultSets=true"
  }
}

The error I'm getting when executing the Login endpoint is - 'Login failed for user "domain user account", whereas I have set my SQL Connection to connect with 'sa'. Seems it's not property setting the connection string of the data context.

Update: On further investigation, I see that although the DbContextOptions has the SQLConnectionString correct, the connection is null.

Can someone point out what's missing here? Thanks in advance.

Answer 1

Your ConnectionString is not right, you need to drop Trusted_Connection=True part. Set appsettings connection string as the following, and it will work

{
  "ConnectionStrings": {
    "SystemDbContext": "Server=xxx;Database=xxxx;user id=sa;password=xxx;MultipleActiveResultSets=true"
  }
}