I got the certificate error when I want to migrate my database

Question

I have an ASP.NET Core 6.0 MVC web app project, and my Entity Framework Core version is 7.0.9.

For example this is one of my class:

using System.ComponentModel.DataAnnotations.Schema;
using System.ComponentModel.DataAnnotations;

namespace BusinessEntity
{
    public class CantactUs
    {
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        [Key]
        public Guid Id { get; set; }

        [Required, Column(TypeName = "nvarchar(50)")]
        public string title { get; set; }

        [Required, Column(TypeName = "nvarchar(200)")]
        public string email { get; set; }

        [Required, Column(TypeName = "nvarchar(5000)")]
        public string definition { get; set; }

        public DateTime time { get; set; }

        public bool read { get; set; }
    }
}

I create my Dbcontext like this:

namespace DataAcessLayer
{
    public class DB : IdentityDbContext<User>
    {
        public DB() : base() { }

        public DB(DbContextOptions<DB> options) : base(options)
        {
        }

        protected override void OnConfiguring(DbContextOptionsBuilder OptionsBuilder)
        {
            OptionsBuilder.UseSqlServer(@"data source=.;initial catalog = stor; integrated security = true;");
            base.OnConfiguring(OptionsBuilder);
        }

        //public DbSet<Book> Books { set; get; }

        public DbSet<CantactUs> cantactUs { get; set; }
        public DbSet<Comment> comments { get; set; }
}

I added my connection string in appsettings.json:

"ConnectionStrings": {
    "CON1": "data source=.;initial catalog = stor; integrated security = true;"

But when I am using add-migration and update-database, I get this 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.)

I do not know how to handle this, but after some search I find, I can change my connection string to this:

@"data source=.;initial catalog = stor; integrated security = true;TrustServerCertificate=True;"

The TrustServerCertificate=True; part solved my problem, but I have another problem now. I got this error after running update-database command

The size (5000) given to the parameter 'definition' exceeds the maximum allowed (4000).

but I if right the maximum number of character of nvarchar is "2^31-1" - how can handle the certificate and size error?

Answer 1

Use nvarchar(max):

[Required, Column(TypeName = "nvarchar(max)")]
public string definition { get; set; }

From SQL Server docs:

nvarchar [ ( n | max ) ] Variable-size string data. n defines the string size in byte-pairs, and can be a value from 1 through 4,000

As for the fixed problem - it is covered in EF Core 7 breaking changes - Encrypt defaults to true for SQL Server connections:

Mitigations

There are three ways to proceed:

1. Install a valid certificate on the server. Note that this is an involved process and requires obtaining a certificate and ensuring it is signed by an authority trusted by the client.
2. If the server has a certificate, but it is not trusted by the client, then TrustServerCertificate=True to allow bypassing the normal trust mechanims.
3. Explicitly add Encrypt=False to the connection string.

You have applied the second one. For non-development environments you should look into the first one.