DotNet7 connecting to SQL Server

DotNet7 connecting to SQL Server

I have a little sample app, that uses my generic repository library. I've recently upgraded both to DotNet7 [.Net7] and immediately hit an issue.

I was getting the error:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.

A search of StackOverflow led me to believe that adding either Encrypt=False or TrustServerCertificate=True to the connection string should fix this issue, but neither helped.

I asked the question on StackOverflow and Mastadon, and most of the answers were the same, but to also considering adding a certificate to SQL Server. Since I'm running SQL Server in Docker this wasn't so easy, and also since I'm using the latest version of SQL Server shouldn't be necessary.

Over the course of my investigations, I saw that it is now best practice to inject IDbContextFactory<> into repositories instead of a DBContext object.

So I changed my Startup.cs to:

services.AddDbContextFactory<SampleAPIContext>(
    options => SqlServerDbContextOptionsExtensions.UseSqlServer(options, connectionString)
);

And I changed the Repository classes to receive the IDbContextFactory<>:

public BookRepository(IDbContextFactory<SampleAPIContext> dbContextFactory,
    IRestToLinqParser<Book> parser, 
    ILogger<BookRepository> logger) : base(dbContextFactory, parser, logger)
{
}

This change fixed the issue. I'm not sure why, but it did.