Failing to update database schema using Entity Framework CLI

Advertisements

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

>Solution :

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:

Leave a ReplyCancel reply