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>

Reinhart

Any errors in your app (enable trace logging)? Any errors with your database connections? Also, take a look at the readme of the sink (github.com/serilog/serilog-sinks-mssqlserver) , for example, the note of the timeout/batching. Then also look at the samples folder and make them work for your scenario. If the samples also do not work, create an issue in the repo and link it here :) – Remmer

application does not give me any errors. and my database connection is correct. For example, ı used other connections "Data Source = localhost; Initial Catalog = SHARED_NOTE; User ID = sa; Password = sql123" that ı can use repo. Also logDB string is correct. – Reinhart

You can check the Serilog.Sinks.MSSqlServer documentation for the master branch: If you don't use the auto-table-creation feature, you'll need to create a log event table in your database. And, if you create your log event table ahead of time, the sink configuration must exactly match that table, or errors are likely to occur. So, you can try to enable the auto-table-creation feature and format the log format. Since the issue relates Serilog.Sinks.MSSqlServer, you can post it on Github issue if have any further question. – Microampere

Answer 1

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();
});

Baliol

@Thanks for the debugging tip, it was very helpful! – Dactylo

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();
    });

Paleozoology

Answer 3

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

Ehling