NLog database target and SQL Server application role

Question

We are replacing a custom logging "framework" with NLog in an existing application. Trouble is the application makes use of SQL Server application roles, which means that every time it opens a connection to the SQL Server it executes the sp_setapprole sproc.

We've tried simulating this behaviour in the database target configuration:

<commandText>
  exec sp_setapprole @roleName, @password; --first, set app. role
  INSERT dbo.Log --etc.
</commandText>
<parameter name="@roleName" value="...">
<parameter name="@password" value="...">
-- other logging parameters follow

However, this yields the error:

Application roles can only be activated at the ad hoc level.

I would prefer to avoid writing my own target since the database target can do everything I need, except setting the application role. So I'm hoping that it is possible to somehow hook up to the StateChange event of the connection that is created by NLog internally or do some other tricks.

TL;DR - How do I switch to an application role when logging via the NLog database target?

Answer 1

We ended up creating our own target based on (a trimmed version of) the NLog DatabaseTarget source and code from this blog post.

See source code here.

The target is configured in (roughly) the same manner as the NLog database target, though with fever features.