Log4Net AdoNetAppender using SQL Server Express DB instead of LocalDB

Problem

After turning on Log4Net debugging by adding the followings to the windows service project App.config file:

  <appSettings>
   <add key="log4net.Internal.Debug" value="true"/>
  </appSettings>
  <system.diagnostics>
    <trace autoflush="true">
      <listeners>
        <add
          name="textWriterTraceListener"
          type="System.Diagnostics.TextWriterTraceListener"
          initializeData="C:\Temp\log4net.log" />
      </listeners>
    </trace>
  </system.diagnostics>

Configuring Log4Net AdoNetAppender with LocalDB  has not worked  due to the following error:

System.Data.SqlClient.SqlException (0x80131904): Cannot open database "Log4NetDB" requested by the login. The login failed.
Login failed for user 'NT AUTHORITY\SYSTEM'.


Solution

Instead of using LocalDb, use SQL Server 2017 Express as follows:

1. Download and install SQL Server 2017 Express

2. Create a new database (i.e. Log4NetDB) and grant NT AUTHORITY\System db_owner.

3. Run the following script to create the table:

CREATE TABLE [dbo].[Log] (
    [Id] [int] IDENTITY (1, 1) NOT NULL,
    [Date] [datetime] NOT NULL,
    [Thread] [varchar] (255) NOT NULL,
    [Level] [varchar] (50) NOT NULL,
    [Logger] [varchar] (255) NOT NULL,
    [Message] [varchar] (4000) NOT NULL,
    [Exception] [varchar] (2000) NULL
)

4. Add the followings to your App.config file

 <log4net>
    <appender name="AdoNetAppender" type="log4net.Appender.AdoNetAppender">
      <bufferSize value="1" />
      <connectionType value="System.Data.SqlClient.SqlConnection, System.Data, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
      <connectionString value="data source=localhost\SQLEXPRESS;initial catalog=Log4NetDB;integrated security=true" />
      <commandText value="INSERT INTO Log ([Date],[Thread],[Level],[Logger],[Message],[Exception]) VALUES (@log_date, @thread, @log_level, @logger, @message, @exception)" />
      <parameter>
        <parameterName value="@log_date" />
        <dbType value="DateTime" />
        <layout type="log4net.Layout.RawTimeStampLayout" />
      </parameter>
      <parameter>
        <parameterName value="@thread" />
        <dbType value="String" />
        <size value="255" />
        <layout type="log4net.Layout.PatternLayout">
          <conversionPattern value="%thread" />
        </layout>
      </parameter>
      <parameter>
        <parameterName value="@log_level" />
        <dbType value="String" />
        <size value="50" />
        <layout type="log4net.Layout.PatternLayout">
          <conversionPattern value="%level" />
        </layout>
      </parameter>
      <parameter>
        <parameterName value="@logger" />
        <dbType value="String" />
        <size value="255" />
        <layout type="log4net.Layout.PatternLayout">
          <conversionPattern value="%logger" />
        </layout>
      </parameter>
      <parameter>
        <parameterName value="@message" />
        <dbType value="String" />
        <size value="4000" />
        <layout type="log4net.Layout.PatternLayout">
          <conversionPattern value="%message" />
        </layout>
      </parameter>
      <parameter>
        <parameterName value="@exception" />
        <dbType value="String" />
        <size value="2000" />
        <layout type="log4net.Layout.ExceptionLayout" />
      </parameter>
      <threshold value="INFO"/>
    </appender>
    <root>
      <level value="DEBUG"/>
       <appender-ref ref="AdoNetAppender"/>
    </root>
  </log4net>

5. Finish


Explanation

<bufferSize value="1" /> is to get Log4Net to write to database as soon as there's an entry. We use this for debugging purpose. For release version, increase it to 100 or more to save expensive db-write operations.
Source: https://logging.apache.org/log4net/release/config-examples.html

When working with LocalDB for local development, you can use SqlLocalDb.exe to create/configure/delete db instance.
Source: https://docs.microsoft.com/en-us/sql/tools/sqllocaldb-utility?view=sql-server-ver15