.NET 6中Serilog自动创建SQL日志表失败求助

核心问题：配置结构错误 #

你的appsettings.json中，Using、Enrich、WriteTo这些Serilog关键配置不在Serilog节点内部，导致Serilog无法读取到SQL Sink的配置，自然不会执行建表操作。

步骤1：修正appsettings.json配置结构 #

将根节点下的Using、Enrich、WriteTo全部移到Serilog节点内，修正后的配置如下：

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=Test;Database=TestDb;Trusted_Connection=True;MultipleActiveResultSets=true;"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "Serilog": {
    "MinimumLevel": {
      "Default": "Information",
      "Override": {
        "Microsoft": "Warning",
        "Microsoft.Hosting.Lifetime": "Warning",
        "System": "Warning",
        "System.Net.Http.HttpClient": "Warning"
      }
    },
    "Using": [ "Serilog.Enrichers.ClientInfo", "Serilog.Sinks.MSSqlServer" ],
    "Enrich": [ "FromLogContext", "WithMachineName", "WithClientIp", "WithClientAgent" ],
    "WriteTo": [
      {
        "Name": "MSSqlServer",
        "Args": {
          "connectionString": "Server=Test;Database=TestDb;Trusted_Connection=True;MultipleActiveResultSets=true;",
          "sinkOptionsSection": {
            "tableName": "LogsTable",          
            "autoCreateSqlTable": true          
          },
          "restrictedToMinimumLevel": "Information",
          "columnOptionsSection": {
            "primaryKeyColumnName": "Id",
            "addStandardColumns": [ "LogEvent", "SourceContext" ],
            "additionalColumns": [
              {
                "ColumnName": "ClientIP",
                "PropertyName": "ClientIp",
                "DataType": "nvarchar"
              }            
            ]
          }
        }
      }
    ]
  }
}

注意：Using中添加了Serilog.Sinks.MSSqlServer，确保Serilog能找到SQL Sink的类型。

步骤2：简化Program.cs的Serilog配置 #

避免重复配置，利用WebApplication.CreateBuilder已加载的配置，无需手动重新构建配置文件：

using Serilog;
using Serilog.Events;

var builder = WebApplication.CreateBuilder(args);

// 优先配置Serilog
builder.Host.UseSerilog((context, configuration) => 
    configuration.ReadFrom.Configuration(context.Configuration));

// Add services to the container.
builder.Services.AddControllersWithViews();

var app = builder.Build();

// 开启Serilog请求日志
app.UseSerilogRequestLogging();

// Configure the HTTP request pipeline.
if (!app.Environment.IsDevelopment())
{
    app.UseExceptionHandler("/Home/Error");
}
app.UseStaticFiles();

app.UseRouting();

app.UseAuthorization();

app.MapControllerRoute(
    name: "default",
    pattern: "{controller=Home}/{action=Index}/{id?}");

// 开启Serilog自诊断日志，排查问题
Serilog.Debugging.SelfLog.ConsoleOut();

app.Run();

说明：builder.Host.UseSerilog的委托方式会自动读取配置，避免手动重复添加Enrich和Level配置，减少冲突。

步骤3：排查其他潜在问题 #

1. 数据库权限验证：确认运行程序的账户（开发环境为当前登录账户，生产环境为应用池账户）拥有TestDb数据库的CREATE TABLE权限。
2. 触发日志写入：Serilog默认在第一条符合级别的日志写入时才会创建表，可在HomeController中添加测试日志：

private readonly ILogger<HomeController> _logger;

public HomeController(ILogger<HomeController> logger)
{
    _logger = logger;
}

public IActionResult Index()
{
    _logger.LogInformation("Test log entry for Serilog SQL sink");
    return View();
}

3. 查看自诊断日志：开启Serilog.Debugging.SelfLog.ConsoleOut()后，控制台会输出Serilog内部的错误信息，比如连接失败、权限不足等问题，帮助定位根源。

内容的提问来源于stack exchange，提问作者Glenn singh