.NET Core 6.0与Serilog配置问题：写入SQL Server时登录失败

问题描述 #

配置.NET Core 6.0与Serilog时，Serilog尝试写入SQL Server数据库触发「登录失败」错误，但所用登录信息与应用程序其他模块完全一致。

原配置文件（appsettings.json） #

{
  "ConnectionStrings": {
    "SqlServer": "Server=127.0.0.1,1433;Database=MyDb;User ID=MyUser;Password=MyPass;MultipleActiveResultSets=true;Max Pool Size=500;Persist Security Info=True;Encrypt=False;TrustServerCertificate=False;"
  },
  "AppSettings": {
  },
  "Logging": {
    "LogLevel": {
      "Default": "Debug",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "Serilog": {
    "MinimumLevel": "Debug",
    "Enrich": [ "FromLogContext", "WithMachineName", "WithThreadId", "WithExceptionDetails" ],
    "WriteTo": [
      {
        "Name": "MSSqlServer",
        "Args": {
          "connectionString": "Server=127.0.0.1,1433;Database=MyDb;User ID=MyUser;Password=MyPass;MultipleActiveResultSets=true;Max Pool Size=500;Persist Security Info=True;Encrypt=False;TrustServerCertificate=False;"
          "restrictedToMinimumLevel": "Information",
          "sinkOptionsSection": {
            "tableName": "Serilog",
            "schemaName": "dbo",
            "autoCreateSqlTable": false,
            "batchPostingLimit": 100
          },
          "columnOptionsSection": {
            "removeStandardColumns": [ "MessageTemplate" ],
            "additionalColumns": [
              {
                "ColumnName": "ApplicationName",
                "DataType": "nvarchar",
                "DataLength": 200,
                "AllowNull": true
              }
            ]
          }
        }
      }
    ],
    "Properties": {
      "ApplicationName": "SampleName"
    }
  },
  "AllowedHosts": "*"
}

排查与解决方案 #

1. 修复JSON语法错误 #

原配置中Serilog的connectionString行末尾缺少逗号，这会导致配置解析失败，Serilog无法正确读取连接字符串，进而使用错误的凭据尝试登录。修正后该行应为：

"connectionString": "Server=127.0.0.1,1433;Database=MyDb;User ID=MyUser;Password=MyPass;MultipleActiveResultSets=true;Max Pool Size=500;Persist Security Info=True;Encrypt=False;TrustServerCertificate=False;",

2. 验证连接字符串有效性 #

单独测试Serilog配置中的连接字符串：编写简单控制台程序，使用相同连接字符串直接连接SQL Server，确认是否能正常登录，排除连接字符串本身的问题。

3. 检查SQL Server权限 #

确保MyUser账户拥有MyDb数据库的写入权限，尤其是dbo.Serilog表的INSERT权限。应用程序其他模块可能仅需读取权限，但Serilog需要写入权限，这是常见的权限差异点。

4. 排查连接池问题 #

连接池可能存在失效连接，可临时在连接字符串中添加Pooling=false（测试用，生产环境不建议），或重启应用与SQL Server清空连接池后重试。

5. 确认包版本兼容性 #

确保Serilog.Sinks.MSSqlServer包版本与.NET Core 6.0兼容，过时的包可能存在连接字符串解析或身份验证的bug，建议更新到最新兼容版本。

修正后的完整配置 #

{
  "ConnectionStrings": {
    "SqlServer": "Server=127.0.0.1,1433;Database=MyDb;User ID=MyUser;Password=MyPass;MultipleActiveResultSets=true;Max Pool Size=500;Persist Security Info=True;Encrypt=False;TrustServerCertificate=False;"
  },
  "AppSettings": {
  },
  "Logging": {
    "LogLevel": {
      "Default": "Debug",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "Serilog": {
    "MinimumLevel": "Debug",
    "Enrich": [ "FromLogContext", "WithMachineName", "WithThreadId", "WithExceptionDetails" ],
    "WriteTo": [
      {
        "Name": "MSSqlServer",
        "Args": {
          "connectionString": "Server=127.0.0.1,1433;Database=MyDb;User ID=MyUser;Password=MyPass;MultipleActiveResultSets=true;Max Pool Size=500;Persist Security Info=True;Encrypt=False;TrustServerCertificate=False;",
          "restrictedToMinimumLevel": "Information",
          "sinkOptionsSection": {
            "tableName": "Serilog",
            "schemaName": "dbo",
            "autoCreateSqlTable": false,
            "batchPostingLimit": 100
          },
          "columnOptionsSection": {
            "removeStandardColumns": [ "MessageTemplate" ],
            "additionalColumns": [
              {
                "ColumnName": "ApplicationName",
                "DataType": "nvarchar",
                "DataLength": 200,
                "AllowNull": true
              }
            ]
          }
        }
      }
    ],
    "Properties": {
      "ApplicationName": "SampleName"
    }
  },
  "AllowedHosts": "*"
}

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