Failing to update database schema using Entity Framework CLI

Question

I have created an empty dotnet new webapi project on my machine using .NET CLI. I have setup ApplicationDbContext as well as a connection string in appsettings.json and created a model that I would like to create in the database.

The project successfully builds and when run the command

dotnet ef migrations add InitialCreate

it generates the Migrations folder with the migration script in it.

The problem arises when running the script and updating the database using the dotnet ef database update where it throws the following error:

Entity Framework Core 6.0.6 initialized 'ApplicationDbContext' using provider 'Microsoft.EntityFrameworkCore.SqlServer:6.0.6' with options: None ...
...
...
ClientConnectionId:#@#@#@@-####-####-@###-#@#@#@#@
Error Number:18456,State:1,Class:14
Login failed for user 'abc###\Guest'.

I am not sure why it is throwing this error and how it can be fixed.

appsettings.json:

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Database=BookClub;Trusted_Connection=True;"
  }
}

BoockClub.csproj:

<Project Sdk="Microsoft.NET.Sdk.Web">

  <PropertyGroup>
    <TargetFramework>net6.0</TargetFramework>
    <Nullable>enable</Nullable>
    <ImplicitUsings>enable</ImplicitUsings>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Dapper" Version="2.0.123" />
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="6.0.6" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="6.0.6">
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
      <PrivateAssets>all</PrivateAssets>
    </PackageReference>
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="6.0.6" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="6.0.6">
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
      <PrivateAssets>all</PrivateAssets>
    </PackageReference>
    <PackageReference Include="System.Data.SqlClient" Version="4.8.3" />
  </ItemGroup>
</Project>

ApplicationDbContext.cs:

using BookClub.Models;
using Microsoft.EntityFrameworkCore;

namespace BookClub.Data;

public class ApplicationDbContext : DbContext
{
  public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options)
  {
  }

  public DbSet<Category> Categories { get; set; }
}

CategoryController.cs:

using BookClub.Data;
using Microsoft.AspNetCore.Mvc;

namespace BookClub.Controllers;

public class CategoryController : Controller
{
  private readonly ApplicationDbContext _db;

  public CategoryController(ApplicationDbContext db)
  {
    _db = db;
  }

  public IActionResult Index()
  {
    var categoryList = _db.Categories.ToList();
    return View();
  }
}

System:

macOS 12.2.1

Applications:

VS Code
Docker - SQL Server
SQL Server extension for VS Code

tinonetic · Accepted Answer · 2022-09-26 09:44:28Z

4

The issue is your connection string.

Trusted_Connection=True; implies that your app is authenticating using Windows Credentials, which won't work unless you're running Windows in Docker with all necessary configs.

You need to create a SQL Authenticated DB user (with Username and Password) then change the connection string

Old Value

"ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Database=BookClub;Trusted_Connection=True;"
  }

New Value

"ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Database=BookClub;User Id=MyUsername;Password=MyPassword;"
  }

If you're using SQL Server Management Studio, use the following options to create the username and password:

edited Sep 26, 2022 at 9:44

answered Jun 17, 2022 at 13:19

tinonetic

8,03411 gold badges57 silver badges84 bronze badges

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

2 Comments

Izanagi Over a year ago

@GurpreetSingh There is this website that helps you create the Connection String with additional info connectionstrings.com/sql-server

Gurpreet Singh Over a year ago

That is brilliant. Cheers for that. 🙌🏽

Collectives™ on Stack Overflow

Failing to update database schema using Entity Framework CLI

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related