none
NPSODBC with 2008 R2 NAP and intermittent errorr 0x80040e57 RRS feed

  • Question

  • I am logging 2008 R2 NPS to SQL using Microsoft's (officially unsupported) method of how to convert the XML output from NPS to a useful database format.  I haven't customized any of the creation steps in the URL, but I have added additional tables, stored procedures, and a trigger on INSERT.  I deleted the trigger completely to verify that the trigger on INSERT wasn't the cause of the problems described below the URL link.  Here's the example from MSDN that transforms the NPS XML:

    http://msdn.microsoft.com/en-us/library/bb960723(VS.85).aspx

    Since upgrading to 2008 R2 and promoting this configuration to production where wireless authentication requests easily surpasses development, I now see the error message at the tail of this post, at intervals varying between 2 to 30 minutes. The error code suggests a data transformation problem.  NPS logs directly to a locally installed SQL 2008 Express database on each NPS server, and I have ensured the databases are not over the 4GB capacity.  Each SQL database is primarily used as data crucible and is purged of records daily. I also scrutinized performance considerably with perfmon while observing the recurring warning, but I am far from being a DBA.  I saw no indications of performance problems; I also toyed with the concurrent connections setting in NPS. Nothing I have done has changed the situation.

    After logging the warning event, NPS follows on quickly stating it was able to reconnect, but I am wondering if there are any experts that might be able to lend me assistance in identifiying what data is not being converted correctly from the XML. Is there a way I can catch the error and then log the attempt? Or is there a better way to achieve my desired 'highly available yet centrally logged and easily searched' configuration using native Microsoft software? ...

    Currently, I employ a custom trigger to distill only the relevant information while excluding health monitors authentications from 3rd party load balancers. I admit my solution is a bit heavy weighted, but has saved me the trouble of writing a completely in-house NPS data transformation app. On INSERT to accounting_data, the custom trigger raises an SQL error with only the desired information from authentication and accounting requests, thusly logging to the Application log where a third party agent running on the NPS server then forwards Application log messages to a central logging solution, whereby AAA data from multiple NPS servers can easily be searched.  Accounting data doesn't appear to ever be logged anywhere except via SQL or logfiles, whereas Authentication requests end up in the Security log.  Furthermore, other than native NLB and a 3rd party log harvesting agent, I haven't found any means by which accomplish the desired configuration.  Perhaps there's a better approach to providing NPS redundancy and consolidate auditing while only logging relevant information (such as success/error, specific NAS info, username, proxy/network policy names).  But if not, here's the error

    System/Event ID 4404/Task Category None/Warning

    NPS cannot log accounting information in the primary data store (.\SQLEXPRESS). NPS will continue to process connection requests without logging accounting information in this data store. Error information: 0x80040e57.

     

     

    Thursday, June 3, 2010 3:25 AM

Answers

  • I got to the bottom of the issue.  It had to do strictly with logged accounting data and the packets/octets colums only being defined as INT.  I redefined the columns to BIGINT and updated the stored procedure accordingly.

    The way I tracked this down was to place a TRY/CATCH around almost the entire procedure and INSERT the @doc ntext into a newly defined table, and lastly RAISERROR and log the ERROR_MESSAGE to the event log.

    Problem solved for now, or at least until users stay connected for a really long time.

     

    Sunday, June 13, 2010 7:03 AM

All replies

  • I got to the bottom of the issue.  It had to do strictly with logged accounting data and the packets/octets colums only being defined as INT.  I redefined the columns to BIGINT and updated the stored procedure accordingly.

    The way I tracked this down was to place a TRY/CATCH around almost the entire procedure and INSERT the @doc ntext into a newly defined table, and lastly RAISERROR and log the ERROR_MESSAGE to the event log.

    Problem solved for now, or at least until users stay connected for a really long time.

     

    Sunday, June 13, 2010 7:03 AM
  • need to change the table and the report_event procedure
    Thursday, April 2, 2020 11:58 AM