Nlog: Configure and write to Azure SQL server with manage Identity token

[Cook-code]

I want to connect with Managed Identity and write the logs in Azure SQL. How can I configure this. As manage identity login has an Access token.

NLog version: 4.6.6

Platform: . .NET Core 2

Current NLog config (4.6.6)

• Why do we need it?
• Need to log error with Managed Identity in Azure SQL

[welcome[bot]]

Hi! Thanks for opening your first issue here! Please make sure to follow the issue template - so we could help you better!

[snakefoot]

NLog DatabaseTarget prefer that you provide the user-authentication using the ConnectionString.

Guess you have to ask Microsoft how to create a ConnectionString that supports "managed identity token".

See also: https://docs.microsoft.com/en-us/azure/storage/common/storage-configure-connection-string#configure-a-connection-string-for-an-azure-storage-account

[snakefoot]

@Cook-code You can also try out my suggestion here for injecting Access Token in SqlConnection: https://stackoverflow.com/a/57597332/193178 (And use the injection in NLog DatabaseTarget)

[304NotModified]

@snakefoot

Can't we add support for https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnection.accesstoken ?

Doesn't seem difficult to delegate the accesstoken property, or am I missing something?

[snakefoot]

@304NotModified Doesn't seem difficult to delegate the accesstoken property, or am I missing something?

You are welcome to add an IList<DatabaseConnectionProperty> to NLog DatabaseTarget, that uses the NLog-reflection logic to assign different property values after having performed connection-open.

Maybe the same DatabaseConnectionProperty can used to configure DbCommand that should be executed on connection-open. See also #3551 (PRAGMA key = 'redacted';)

[tedmatuszewski]

I am experiencing this very issue. Has anybody found a solution or work-around for this?

[Cook-code]

work around for me - I had a blob storage so strong the logs in in BLOB now.

[tedmatuszewski]

work around for me - I had a blob storage so strong the logs in in BLOB now.

I could set it up to use BLOB storage, however I would rather not as consuming the logs from BLOB storage takes a little bit more doing that just reading from a database table.

[anxkha]

Has anyone started on this or considered it for implementation? I have a potentially solid theory I can implement and add to the DatabaseTarget if not.

[snakefoot]

@anxkha You are very welcome to make a pull-request. Only have 3 requirements:

• AccessToken value should come from a NLog Layout (Supporting ${configsetting} etc.)
• AccessToken should only be assigned when opening the SqlConnection.
• There should not be any new dependencies for the NLog-nuget-package.

Right now the only work-around is creating your own Custom DbProvider-Connection-Factory, that generates SqlConnections with AccessToken pre-assigned. See also https://stackoverflow.com/a/57597332/193178

[anxkha]

@snakefoot Thanks, I will definitely create a pull request.

My only concern about making the token be driven from the layout is if it periodically needs to be updated. I am trying to find documentation from MS that says one way or another if one is supposed to get a new access token every time you open a SqlConnection, or if one time is sufficient. So far no luck on that, though, as there seems to be 0 documentation on the actual AzureServiceTokenProvider().GetAccessTokenAsync call.

[snakefoot]

@anxkha Was hoping you knew more about this token business than me. But the JWT-token should stay valid until revoked. I'm guessing tokens are revoked when they have been stolen, or if the token was generated as temporary for limited time access and has expired.

[anxkha]

@snakefoot I just did an experiment and it looks like the same token is returned by AzureServiceTokenProvider().GetAccessTokenAsync for subsequent calls and restarting the Azure App Service does not cause the token to change. Now, whether the token has an expiration associated with it remains to be seen, but the fact that it doesn't change across different scenarios for the same App Service is promising.

I'll submit a PR with just support for a static token and I'll watch over time to see if I see evidence of a long-lived token being a Bad Idea (TM).

[snakefoot]

@anxkha Make sure that the call to AzureServiceTokenProvider happens in an external layoutrenderer, or explicitly called by the application at startup and manually injected into NLog GlobalDiagnosticsContext (Should not become a dependency of the NLog-package)

[anxkha]

@snakefoot So after more experimentation and googling, I discovered the following documentation:

https://docs.microsoft.com/en-us/azure/key-vault/service-to-service-authentication

The AzureServiceTokenProvider class caches the token in memory and retrieves it from Azure AD just before expiration. So, you no longer have to check the expiration before calling the GetAccessTokenAsync method. Just call the method when you want to use the token.

I also found this documentation (under the Access Token section):

https://docs.microsoft.com/en-us/azure/active-directory/develop/active-directory-configurable-token-lifetimes

The default is 1 hour - after 1 hour, the client must use the refresh token to (usually silently) acquire a new refresh token and access token.

I also confirmed that now, a day later, my access token has indeed changed from what it was yesterday. Armed with this knowledge I believe we can safely assert that storing a static access token is insufficient, as it will expire and have to be refreshed. I propose the following two changes:

• Support a static Access Token in the layout for non-Azure scenarios that might actually have a static Access Token
• Add a property to DatabaseTarget that takes a simple, no-parameter delegate that returns a string, which the host application can supply with a function that makes whatever call is necessary to provide an access token. This delegate would be called in OpenConnection to assign the access token to the connection instance if ConnectionType is a SqlConnection.

Is the second bullet's approach sane? I will admit that while I have been using NLog for years this is the first time I have attempted to modify it in any way, so I'm not an expert on this side of it ;)

[snakefoot]

@anxkha To me bullet one and two is the same. The smart thing about an NLog Layout is that it can retrieve its value from whatever LayoutRenderer. A LayoutRenderer can also be a simple delegate. It is just a matter of deciding when to call Layout.Render()

[anxkha]

@snakefoot Ah! I get it now! I spent some time looking more in depth and see I see what ya'll did there. Kudos to whoever built that pattern ;)

I have some implementation questions, but I'm not sure if discussing them on this issue thread is the right place. Is there a better place to discuss specifics of implementation for something or is here fine?

[snakefoot]

Think you should create a pull request with your initial suggestion. Even if it is just pseudo code in code comments.

Think discussion on how NLog is designed will side-track the actual feature request.

[anxkha]

I attempted to push a branch but I got access denied from github. I looked at the documentation for contributing and I didn't miss anything based on what I read. I assume someone needs to give me permissions to push a branch?

[snakefoot]

@anxkha First you make a fork of this repository (See Fork-button here at the top), so you have your own copy of the NLog-repository.

Instead of performing a checkout of the official NLog-repository, then you should create a checkout of your own fork/copy of the NLog-repository. And then make your changes there to the wanted branch.

After you have pushed a branch to your own copy of the NLog-repository then you can navigate here and it should show a yellow-box with create pull-request to the official NLog-repository.

[snakefoot]

NLog 4.7 has been released, along with a nuget-package with Layout to lookup/refresh AccessToken:

https://www.nuget.org/packages/NLog.Extensions.AzureAccessToken/

You can configure it like this:

<extensions>
  <add assembly="NLog.Extensions.AzureAccessToken" /> 
</extensions>

<targets>
  <target xsi:type="Database" connectionString="...">
    <dbProvider>Microsoft.Data.SqlClient.SqlConnection, Microsoft.Data.SqlClient</dbProvider>
    <connectionProperty name="AccessToken" layout="${AzureAccessToken:ResourceName=${gdc:DatabaseHostSuffix}}"  />
  </target>
</targets>

And setup the ResourceName like this:

c# NLog.GlobalDiagnosticsContext.Set("DatabaseHostSuffix", $"https://{DatabaseHostSuffix}/"); NLog.LogManager.LoadConfiguration("nlog.config");

Please try it out, and provide any feedback that you might have.

[snakefoot]

@ChoppyBubbles AccessToken feature is ready for you

[dadoadk]

This seems to be a good answer but I still cannot get it to work. Updated my packages, got the newest NLog version. Added NLog.Extensions.AzureAccessToken package. Followed the instructions

<target name="my-db"
        xsi:type="Database"
        dbProvider="System.Data.SqlClient"        
        connectionString="${var:connectionStringNLog}"
        commandType="StoredProcedure"
        commandText="[dbo].[LogEntry]">

      <dbProvider>Microsoft.Data.SqlClient.SqlConnection, Microsoft.Data.SqlClient</dbProvider>
      <connectionProperty name="AccessToken" layout="${AzureAccessToken:ResourceName=${gdc:https://database.windows.net/}}"  />


      <parameter name="@level"              layout="${level}" />
      <parameter name="@logged"             layout="${date:universalTime=true}" />
      <parameter name="@remoteAddress"      layout="${aspnet-request:serverVariable=REMOTE_ADDR}:${aspnet-request:serverVariable=REMOTE_PORT}" />
      <parameter name="@serverName"         layout="${aspnet-request:serverVariable=SERVER_NAME}" />
      <parameter name="@component"          layout="${mdc:item=component" />
      <parameter name="@executableVersion"  layout="${mdc:item=executableVersion" />
      <parameter name="@url"                layout="${aspnet-request:serverVariable=HTTP_URL}" />
      <parameter name="@facility"           layout="${mdc:item=facility}" />
      <parameter name="@user"               layout="${mdc:item=user}" />
      <parameter name="@method"             layout="${event-properties:item=method}" />
      <parameter name="@message"            layout="${message}" />
      <parameter name="@elapsed"            layout="${event-properties:item=elapsed}" />
      <parameter name="@resultCode"         layout="${event-properties:item=resultCode}" />
      <parameter name="@resultData"         layout="${event-properties:item=resultData}" />
      <parameter name="@exceptionDetails"   layout="${exception:tostring}" />
    </target>

Then in my ContextFactory.cs I have this:

SqlConnection sqlconn = new SqlConnection(conn.ConnectionString);
sqlconn.AccessToken = accessToken;

LogManager.Configuration.Variables["connectionStringNLog"] = sqlconn.ConnectionString;
NLog.GlobalDiagnosticsContext.Set("DatabaseHostSuffix", $"https://database.windows.net/");
NLog.LogManager.LoadConfiguration("nlog.config");

What am I missing?

[snakefoot]

@dadoadk I'm sad to see that you have not fully understood how NLog Layout actually works.

First Issue - DbProvider
You need to choose the correct DbProvider. Right now you have selected two, since you are using both dbProvider-attribute and dbProvider-element. Please choose one, and install the relevant nuget package (Notice that Microsoft.Data.SqlClient is the future):

<target name="my-db"
        xsi:type="Database"
        dbProvider="System.Data.SqlClient">
 <dbProvider>Microsoft.Data.SqlClient.SqlConnection, Microsoft.Data.SqlClient</dbProvider>
</target>

Second Issue - AccessToken
You have done something very strange here. You have specified the actually value, but decided to wrap it into GDC. So actually you are doing a lookup in the GDC using the key-value https://database.windows.net/):

<connectionProperty name="AccessToken" layout="${AzureAccessToken:ResourceName=${gdc:https://database.windows.net/}}"  />

I guess you want to skip use of GDC. And just do this:

<connectionProperty name="AccessToken" layout="${AzureAccessToken:ResourceName=https\://database.windows.net/}"  />

Third issue - ConnectionString
You should avoid using NLog Config Variables for values you want to change at runtime. Instead you should use GDC:

<target name="my-db"
        xsi:type="Database"
        connectionString="${gdc:connectionStringNLog}">

And then assign GDC like this:

c# NLog.GlobalDiagnosticsContext.Set("connectionStringNLog", conn.ConnectionString); NLog.LogManager.LoadConfiguration("nlog.config");

Fourth issue - Read the wiki
There is a wiki-guide available, when needing to troubleshoot NLog:

https://github.com/NLog/NLog/wiki/Logging-troubleshooting

The best weapon is ofcourse the NLog InternalLogger: https://github.com/NLog/NLog/wiki/Internal-logging

[dadoadk]

@snakefoot I'm amazed at how condescending you can sound. You don't have to be sad for me. I guess that's a trait that most programmers/developers share in this industry. Just post and answer if you care to help, other comments are not necessary.

Happy to tell you that I got it working before you posted this comment. Although the NLog has good documentation there's nowhere it says that using gdc is better than variables at runtime. If you can elaborate I'd be glad to learn.

My solution: remove the LoadConfiguration after the DatabaseHostSuffix and connectionStringNLog variables are assigned in ContextFactory.cs.

Thank you, I hope these comments help other developers.

[snakefoot]

@dadoadk I would really like to remove all pitfalls, that makes NLog illogical. So I'm sincerely sad when the documentation / examples confuses fellow developers.

I guess in your example case, then I found so many issues, that I decided that you didn't have a firm grasp on how NLog Layout or NLog Config is working. And then started to explain from that starting point.

I'm happy that you have found the issue, and you can confirm that the new feature is working. Maybe you can post your final working XML along with and code-behind you have been doing?

I have been writing a long rant about NLog Config Variables here:

https://github.com/NLog/NLog/wiki/Var-Layout-Renderer

If you can show places in the Wiki where it would be natural to also explain the shortcomings on NLog-Config-Variables then please tell. I have a small hope on trying to improve the user-experience for NLog-Config-Variables in NLog 5.0 (Will be a breaking change, but hopefully become more intuitive).

[snakefoot]

@dadoadk Although the NLog has good documentation there's nowhere it says that using gdc is better than variables at runtime

I have now updated:

• https://github.com/NLog/NLog/wiki/Configuration-file#variables

So it references and matches:

• https://github.com/NLog/NLog/wiki/Configure-from-code#update-config-in-code

If you see more places, then please tell.