NLOG Database Target can't connect

I have an ASP.NET Core 7 web api in which I am attempting to add an NLOG database target. It is definitely not logging and the internal log has provided me with two different messages, depending on what I have tried so far. Something is not right about the configuration but I really don't understand what.

First attempt:

Error DatabaseTarget(Name=DatabaseSiteLog): Error when writing to database. Exception: Microsoft.Data.SqlClient.SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
 ---> System.ComponentModel.Win32Exception (2): The system cannot find the file specified.
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open()
   at NLog.Targets.DatabaseTarget.OpenConnection(String connectionString, LogEventInfo logEventInfo)
   at NLog.Targets.DatabaseTarget.EnsureConnectionOpen(String connectionString, LogEventInfo logEventInfo)
   at NLog.Targets.DatabaseTarget.WriteLogEventSuppressTransactionScope(LogEventInfo logEvent, String connectionString)
ClientConnectionId:00000000-0000-0000-0000-000000000000
Error Number:2,State:0,Class:20

Recent Attempts:

Error DatabaseTarget(Name=DatabaseSiteLog): Error when writing to database. Exception: System.InvalidOperationException: The ConnectionString property has not been initialized.
   at Microsoft.Data.SqlClient.SqlConnection.PermissionDemand()
   at Microsoft.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)
   at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open()
   at NLog.Targets.DatabaseTarget.OpenConnection(String connectionString, LogEventInfo logEventInfo)
   at NLog.Targets.DatabaseTarget.EnsureConnectionOpen(String connectionString, LogEventInfo logEventInfo)
   at NLog.Targets.DatabaseTarget.WriteLogEventSuppressTransactionScope(LogEventInfo logEvent, String connectionString)

appsettings.json

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning",
      "Microsoft.EntityFrameworkCore.Database.Comman": "Information"
    }
  },
  "ConnectionStrings": {
    "ApiContext": "Data Source = (localdb)\\MSSQLLocalDB; Initial Catalog = SiteDb"
  }
}

NLog.config target:

<target xsi:type="Database"
        name="DatabaseSiteLog"
        connectionString="${configsetting:name=ConnectionStrings.ApiContext}">
    <commandText>
        INSERT INTO dbo.Log columns Values values
    </commandText>
    <parameter name="@columns" layout="${values}" />
</target>

It's hard for us to find the root cause in your issue. So I create a brand new project and write this tutorial for you, you can according the steps to check the issue in your side.

Before we check the steps, we need to make sure we have installed the packages like below.

Step 1: create a database in localdb or in your SQL Server, let's name it NLogDB. And create a stored procedure and a table first.

/****** Object: Table [dbo].[NLog] Script Date: 7/20/2023 4:41:17 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[NLog] 
(
    [ID]          INT            IDENTITY (1, 1) NOT NULL,
    [MachineName] NVARCHAR (200) NULL,
    [Logged]      DATETIME       NOT NULL,
    [Level]       VARCHAR (5)    NOT NULL,
    [Message]     NVARCHAR (MAX) NOT NULL,
    [Logger]      NVARCHAR (300) NULL,
    [Properties]  NVARCHAR (MAX) NULL,
    [Callsite]    NVARCHAR (300) NULL,
    [Exception]   NVARCHAR (MAX) NULL
);

/****** Object: SqlProcedure [dbo].[NLog_AddEntry_p] Script Date: 7/20/2023 4:41:48 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

 
CREATE PROCEDURE [dbo].[NLog_AddEntry_p] (
  @machineName nvarchar(200),
  @logged datetime,
  @level varchar(5),
  @message nvarchar(max),
  @logger nvarchar(300),
  @properties nvarchar(max),
  @callsite nvarchar(300),
  @exception nvarchar(max)
) AS
BEGIN
  INSERT INTO [dbo].[NLog] (
    [MachineName],
    [Logged],
    [Level],
    [Message],
    [Logger],
    [Properties],
    [Callsite],
    [Exception]
  ) VALUES (
    @machineName,
    @logged,
    @level,
    @message,
    @logger,
    @properties,
    @callsite,
    @exception
  );
END

Step 2: make sure you can connect the db in VS2022. You can click View ->SQL Server Object Explorer. Then find the database and right click it, and check the properties. And find the connection string.

In my side the connection string like below, if you are using SQL Server you also can use this way to generate the connection string.

Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=NLogDB;Integrated Security=True;Connect Timeout=30;Encrypt=False;Trust Server Certificate=False;Application Intent=ReadWrite;Multi Subnet Failover=False

Step 3: create a nlog.config file in your project.

<?xml version="1.0" encoding="utf-8" ?>
<nlog xmlns="http://www.nlog-project.org/schemas/NLog.xsd"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      autoReload="true"
      internalLogLevel="Info"
      throwConfigExceptions="true"
      internalLogFile="c:\temp\internal-nlog-AspNetCore7.txt">

  <!-- enable asp.net core layout renderers -->
  <extensions>
    <add assembly="NLog.Web.AspNetCore"/>
  </extensions>

  <!-- the targets to write to -->
  <targets>
    <!-- File Target for all log messages with basic details -->
    <target xsi:type="File" name="allfile" fileName="c:\temp\nlog-AspNetCore7-all-${shortdate}.log"
            layout="${longdate}|${event-properties:item=EventId:whenEmpty=0}|${level:uppercase=true}|${logger}|${message} ${exception:format=tostring}" />

    <!-- File Target for own log messages with extra web details using some ASP.NET core renderers -->
    <target xsi:type="File" name="ownFile-web" fileName="c:\temp\nlog-AspNetCore7-own-${shortdate}.log"
            layout="${longdate}|${event-properties:item=EventId:whenEmpty=0}|${level:uppercase=true}|${logger}|${message} ${exception:format=tostring}|url: ${aspnet-request-url}" />

    <!--Console Target for hosting lifetime messages to improve Docker / Visual Studio startup detection -->
    <target xsi:type="Console" name="lifetimeConsole" layout="${MicrosoftConsoleLayout}" />
  <!-- the targets to write to -->
 
    <target name="db"
            xsi:type="Database"
            connectionString="${configsetting:item=ConnectionStrings.NLog}"
            dbProvider="Microsoft.Data.SqlClient.SqlConnection, Microsoft.Data.SqlClient"
            commandType="StoredProcedure"
            commandText="[dbo].[NLog_AddEntry_p]"
            >
      <parameter name="@machineName"    layout="${machinename}" />
      <parameter name="@logged"         layout="${date}" />
      <parameter name="@level"          layout="${level}" />
      <parameter name="@message"        layout="${message}" />
      <parameter name="@logger"         layout="${logger}" />
      <parameter name="@properties"     layout="${all-event-properties:separator=|}" />
      <parameter name="@callsite"       layout="${callsite}" />
      <parameter name="@exception"      layout="${exception:tostring}" />
    </target>
  </targets>

  <!-- rules to map from logger name to target -->
  <rules>
    <!--All logs, including from Microsoft-->
    <logger name="*" minlevel="Trace" writeTo="allfile" />

    <!--Output hosting lifetime messages to console target for faster startup detection -->
    <logger name="Microsoft.Hosting.Lifetime" minlevel="Info" writeTo="lifetimeConsole, ownFile-web" final="true" />

    <!--Skip non-critical Microsoft logs and so log only own logs (BlackHole) -->
    <logger name="Microsoft.*" maxlevel="Info" final="true" />
    <logger name="System.Net.Http.*" maxlevel="Info" final="true" />

    <logger name="*" minlevel="Trace" writeTo="ownFile-web" />
   <logger name="*" minlevel="Trace" writeTo="db" />
  </rules>
</nlog>

Step 4: register it in your Program.cs file.

using NLog.Web;

namespace Asp.Net.Core7.Nlog
{
    public class Program
    {
        public static void Main(string[] args)
        {
            var builder = WebApplication.CreateBuilder(args);

            // Add NLog for Logging
            builder.Logging.ClearProviders();
            builder.Host.UseNLog();

            // Add services to the container.

            builder.Services.AddControllers();
            // Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle
            builder.Services.AddEndpointsApiExplorer();
            builder.Services.AddSwaggerGen();

            var app = builder.Build();

            // Configure the HTTP request pipeline.
            if (app.Environment.IsDevelopment())
            {
                app.UseSwagger();
                app.UseSwaggerUI();
            }

            app.UseHttpsRedirection();

            app.UseAuthorization();


            app.MapControllers();

            app.Run();
        }
    }
}

Step 5: add connection string in appsettings.json file.

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "ConnectionStrings": {
    "NLog": "Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=NLogDB;Integrated Security=True;Connect Timeout=30;Encrypt=False;Trust Server Certificate=False;Application Intent=ReadWrite;Multi Subnet Failover=False"
  },
  "AllowedHosts": "*"
}

Step 6: inject NLog in the controller:

using Microsoft.AspNetCore.Mvc;

namespace Asp.Net.Core7.Nlog.Controllers
{
    [ApiController]
    [Route("[controller]")]
    public class WeatherForecastController : ControllerBase
    {
        private static readonly string[] Summaries = new[]
        {
        "Freezing", "Bracing", "Chilly", "Cool", "Mild", "Warm", "Balmy", "Hot", "Sweltering", "Scorching"
    };

        private readonly ILogger<WeatherForecastController> _logger;

        public WeatherForecastController(ILogger<WeatherForecastController> logger)
        {
            _logger = logger;
            _logger.LogTrace(1, "NLog injected into HomeController");
        }

        [HttpGet(Name = "GetWeatherForecast")]
        public IEnumerable<WeatherForecast> Get()
        {
            _logger.LogInformation("Hello, this is the index!");
            return Enumerable.Range(1, 5).Select(index => new WeatherForecast
            {
                Date = DateOnly.FromDateTime(DateTime.Now.AddDays(index)),
                TemperatureC = Random.Shared.Next(-20, 55),
                Summary = Summaries[Random.Shared.Next(Summaries.Length)]
            })
            .ToArray();
        }
    }
}

Step 7: run the application and test the api method, then check the logs in files:

Step 8: check the logs in the database:

That's all the steps and the code, if you can find the root cause in your side, like SQL Server configuration related or others, you can add the comment below, it could help more users when they find this tutorial.