Serilog does not write logs to SQL Server using Serilog.Sinks.MssqlServer

Question

I'm trying to set up a logging mechanism using Serilog. I want to write the logs to both the file and a SQL Server database.

Currently I can write the logs to the file system, but I cannot write to the database. I also did the same simple setup as in the Serilog documentation

Thank you.

public class Program
{
        public static void Main(string[] args)
        {
            var logDB = @"Server=localhost;Initial Catalog=SHARED_NOTE;User ID=sa;Password=sql123;";
            var sinkOpts = new MSSqlServerSinkOptions();
            sinkOpts.TableName = "Logs";
            var columnOpts = new ColumnOptions();
            columnOpts.Store.Remove(StandardColumn.Properties);
            columnOpts.Store.Add(StandardColumn.LogEvent);
            columnOpts.LogEvent.DataLength = 2048;
            columnOpts.TimeStamp.NonClusteredIndex = true;

            Log.Logger = new LoggerConfiguration()
                .WriteTo.File(new CompactJsonFormatter(), "Log.json", rollingInterval: RollingInterval.Day)
                .WriteTo.Console(restrictedToMinimumLevel:Serilog.Events.LogEventLevel.Information)
                .WriteTo.MSSqlServer(
                        connectionString: logDB,
                        sinkOptions: sinkOpts,
                        columnOptions: columnOpts
                 )
                .CreateLogger();

            try
            {
                Log.Information("Application starting up.");

                CreateHostBuilder(args).Build().Run();
            }
            catch (Exception ex)
            {
                Log.Fatal(ex, "The application failed to start up correctly.");
            }
            finally
            {
                Log.CloseAndFlush();
            }
        }

        public static IHostBuilder CreateHostBuilder(string[] args) =>
            Host.CreateDefaultBuilder(args)
                .UseSerilog()
                .ConfigureWebHostDefaults(webBuilder =>
                {
                    webBuilder.UseStartup<Startup>();
                });
    }

I added AddLogging to startup.cs:

services.AddLogging();

All packages is setup for a Web API project:

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

  <PropertyGroup>
    <TargetFramework>net5.0</TargetFramework>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="FluentValidation" Version="10.3.6" />
    <PackageReference Include="FluentValidation.AspNetCore" Version="10.3.6" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="5.0.12">
      <PrivateAssets>all</PrivateAssets>
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
    </PackageReference>
    <PackageReference Include="Serilog.AspNetCore" Version="4.1.0" />
    <PackageReference Include="Serilog.Formatting.Compact" Version="1.1.0" />
    <PackageReference Include="Serilog.Sinks.File" Version="5.0.0" />
    <PackageReference Include="Serilog.Sinks.MSSqlServer" Version="5.6.1" />
    <PackageReference Include="Swashbuckle.AspNetCore" Version="5.6.3" />
  </ItemGroup>

  <ItemGroup>
    <ProjectReference Include="..\..\Core\SharedNote.Application\SharedNote.Application.csproj" />
    <ProjectReference Include="..\..\Infrastructure\SharedNotes.Persistence\SharedNotes.Persistence.csproj" />
  </ItemGroup>

  <ItemGroup>
    <Folder Include="wwwroot\Images\" />
    <Folder Include="wwwroot\Docs\" />
  </ItemGroup>

</Project>

Answer 1

I could resolve this issue by adding "TrustServerCertificate=True;Encrypt=True;" to the connection string.

Answer 2

In My case I did not realise that I needed to grant Select as well as Insert to the AppLog table.

Turning on the self logger helped diagnose this

    public static ILogger Logger => LazyInitializer.EnsureInitialized(ref _logger, () =>
    {
        Trace.WriteLine("Creating static Logger");

        // Self-log to flushing file stream
        var stream = new StreamWriter(Path.Combine(Path.GetTempPath(), "_selflog.txt"));
        stream.AutoFlush = true;
        Serilog.Debugging.SelfLog.Enable(TextWriter.Synchronized(stream));

        return new LoggerConfiguration()
            .ReadFrom.Configuration(Config)
            .CreateLogger();
    });

Answer 3

Not enough reputation for a comment, have you tried following this article? Serilog log to SQL.

You haven't added the logging table but I'm going to assume that you followed the Sink and it resembles or is a match to this one?

TABLE [Log] (

   [Id] int IDENTITY(1,1) NOT NULL,
   [Message] nvarchar(max) NULL,
   [MessageTemplate] nvarchar(max) NULL,
   [Level] nvarchar(128) NULL,
   [TimeStamp] datetimeoffset(7) NOT NULL,
   [Exception] nvarchar(max) NULL,
   [Properties] xml NULL,
   [LogEvent] nvarchar(max) NULL

   CONSTRAINT [PK_Log]
     PRIMARY KEY CLUSTERED ([Id] ASC)

)

Also, from the same article, you can add the following code right after the Logger setup to debug the SQL connection

Serilog.Debugging.SelfLog.Enable(msg =>
{
    Debug.Print(msg);
    Debugger.Break();
});

So in you code it would be

Log.Logger = new LoggerConfiguration()
    .WriteTo.File(new CompactJsonFormatter(),
    "Log.json",
    rollingInterval: RollingInterval.Day)
            .WriteTo.Console(restrictedToMinimumLevel:Serilog.Events.LogEventLevel.Information)
            .WriteTo.MSSqlServer(
                    connectionString: logDB,
                    sinkOptions: sinkOpts,
                    columnOptions: columnOpts
             )
            .CreateLogger();

Serilog.Debugging.SelfLog.Enable(msg =>
{
    Debug.Print(msg);
    Debugger.Break();
});