Serilog cannot log on to my local database-CodePudding

I'm trying to use Serilog for logging in my .NET project, but I noticed that the errors weren't being written to the database. I turned on Serilog's SelfLog and this is the error that shows up:

2022-11-01T15:58:26.9744639Z Unable to write 1 log events to the database due to following 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.)

This is my set up for Serilog in my Program.cs:

var columnOptions = new ColumnOptions();
        columnOptions.Store.Remove(StandardColumn.Properties);
        columnOptions.Store.Remove(StandardColumn.MessageTemplate);
        columnOptions.TimeStamp.NonClusteredIndex = true;
Log.Logger = new LoggerConfiguration()
                    .ReadFrom.Configuration(Config)
                    .Enrich.FromLogContext()
                    .Enrich.WithExceptionDetails()
                    .WriteTo.Console()
                    .WriteTo.MSSqlServer(
                            connectionString: Config.GetSection("DbConfiguration")["ConnString"],
                            sinkOptions: new MSSqlServerSinkOptions
                            {
                                TableName = Config.GetSection("Serilog")["TableName"],
                                SchemaName = "dbo",
                                AutoCreateSqlTable = false,
                                BatchPeriod = TimeSpan.FromMilliseconds(5)
                            },
                            columnOptions: columnOptions
                        )
                    .CreateLogger();

I've confirmed that the connection string I'm giving Serilog is the same as the one I use to access my database normally and I am creating the table manually, however even if I set it to true and have Serilog auto create it, it throws the same error about being unable to login. I've found a similar version on this error on other pages online, but I can't find one involving Serilog and the other pages weren't relevant to what I am doing.

Can someone help me understand what is going on?

CodePudding user response：

Starting with v5 of Serilog.Sinks.MSSqlServer, it uses Microsoft.Data.SqlClient rather than System.Data.SqlClient to talk to SQL Server. Starting with v4 of Microsoft.Data.SqlClient encryption get enabled by default, and the validity of the certificate used for encryption is verified. You have several options:

Give your SQL Server instance a valid certificate - or have your machine trust the cert
Use an older version of Serilog.Sinks.MSSqlServer that either uses System.Data.SqlClient or uses an old version of Microsoft.Data.SqlClient that doesn't enable encryption by default. I only mention this here because someone will probably suggest it - do not take this approach!
Adjust the connection string so that it ignores certificate validation errors. This is not a good idea either!
Adjust the connection string to not use encryption - this is not a good idea!

Likely as you update your own code to use Microsoft.Data.SqlClient instead of System.Data.SqlClient, you'll run into the same issues. Might as well bite the bullet now and use a certificate that your machine trusts.