locked
NPS SQL missing reason-code in Server 2012 R2 RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi

    It seems like several SQL fields in the 2012 R2 version of NPS logging has been removed.

    Amongst others the Reason-Code field.

    Most people only refer to log files to see why someone can't connect to the vpn server.

    In previous versions we had a web front end for an sql view that gave you the reason why the server denied access so that we do not need to give helpdesk personnel access to the log files and we also filter which operations vpn sessions they can see (via multiple vpn nps policies and then filter the sql view based on their permissions).

    Is there any way to restore the logging of this field to SQL as well as logging the source IP address of the request (our investors requires this and we get audited on it) with the issued IP (non DHCP). We are not allowed to run DHCP on this server since it is situated in the 3rd party datacenter.

    Regards

    Johan

    Thursday, February 6, 2014 11:09 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    Do you mean that there is a detail event log with reason code when VPN client fail to connect, but the feature missing in windows server 2012r2.

    What is the previous version of windows?

    For you reference:

    Configure Log File Properties

    http://technet.microsoft.com/en-us/library/cc730677.aspx

    Configure SQL Server Logging in NPS

    http://technet.microsoft.com/en-us/library/cc754123.aspx

    Hope this helps.

    Sunday, February 9, 2014 2:44 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Daniel

    Sorry for the late reply but I did not get the notification mail of your reply.

    In 2008R2 NPS logging to SQL it creates the reason code column and several other columns when you create the DB via NPS.

    In 2012R2 the reason code column is not created including several other columns.

    This reason code column contained the error code which you could use to determine why somebody could not connect to the VPN for eg. "Bad Password" or "Account locked out".

    Regards

    Johan

    Wednesday, February 12, 2014 9:43 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    This is what the 2012R2 report_event parses:

    FROM

    OPENXML(@idoc,'/Event')


    WITH

    (


    Timestampdatetime'./Timestamp',


    Computer_Namenvarchar(255)'./Computer-Name',


    Packet_Typeint'./Packet-Type',


    [User_Name]nvarchar(255)'./User-Name',


    Client_IP_Addressnvarchar(15)'./Client-IP-Address',


    Fully_Qualified_Machine_Namenvarchar(255)'./Fully-Qualified-Machine-Name',


    NP_Policy_Namenvarchar(255)'./NP-Policy-Name',


    MS_Quarantine_Stateint'./MS-Quarantine-State',


    MS_Extended_Quarantine_Stateint'./MS-Extended-Quarantine-State',


    System_Health_Resultnvarchar(4000),


    System_Health_ResultExnvarchar(MAX),


    MS_Network_Access_Server_Typeint'./MS-Network-Access-Server-Type',


    Called_Station_Idnvarchar(255)'./Called-Station-Id',


    MS_Quarantine_Grace_Timedatetime'./MS-Quarantine-Grace-Time',


    MS_Quarantine_User_Classnvarchar(255)'./MS-Quarantine-User-Class',


    Client_IPv6_Addressnvarchar(32)'./Client-IPv6-Address',


    Not_Quarantine_Capableint'./Not-Quarantine-Capable',


    AFW_Zoneint'./AFW-Zone',


    AFW_Protection_Levelint'./AFW-Protection-Level',


    Quarantine_Update_Non_Compliantint'./Quarantine-Update-Non-Compliant',


    MS_Machine_Namenvarchar(255)'./MS-Machine-Name',


    OS_Versionnvarchar(255)'./Machine-Inventory',


    MS_Quarantine_Session_Idnvarchar(255)'./MS-Quarantine-Session-Id'


    )


    • Edited by JohanBH Wednesday, February 12, 2014 10:03 AM
    Wednesday, February 12, 2014 10:02 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    And this is what Server 2008R2 had parsed in the default stored procedure:

    FROM

    OPENXML(@idoc, '/Event')

    WITH

    (

        Computer_Name

    nvarchar(255) './Computer-Name',

        Packet_Type

    int './Packet-Type',

        [User_Name]

    nvarchar(255) './User-Name',

        F_Q_User_Name

    nvarchar(255) './Fully-Qualifed-User-Name',

        Called_Station_Id

    nvarchar(255) './Called-Station-Id',

        Calling_Station_Id

    nvarchar(255) './Calling-Station-Id',

        Callback_Number

    nvarchar(255) './Callback-Number',

        Framed_IP_Address

    nvarchar(15) './Framed-IP-Address',

        NAS_Identifier

    nvarchar(255) './NAS-Identifier',

        NAS_IP_Address

    nvarchar(15) './NAS-IP-Address',

        NAS_Port

    int './NAS-Port',

        Client_Vendor

    int './Client-Vendor',

        Client_IP_Address

    nvarchar(15) './Client-IP-Address',

        Client_Friendly_Name

    nvarchar(255) './Client-Friendly-Name',

        Event_Timestamp

    datetime './Event-Timestamp',

        Port_Limit

    int './Port-Limit',

        NAS_Port_Type

    int './NAS-Port-Type',

        Connect_Info

    nvarchar(255) './Connect-Info',

        Framed_Protocol

    int './Framed-Protocol',

        Service_Type

    int './Service-Type',

        Authentication_Type

    int './Authentication-Type',

        NP_Policy_Name

    nvarchar(255) './NP-Policy-Name',

        Reason_Code

    int './Reason-Code',

        Class

    nvarchar(255) './Class',

       

    Session_Timeout int './Session-Timeout',

        Idle_Timeout

    int './Idle-Timeout',

        Termination_Action

    int './Termination-Action',

        EAP_Friendly_Name

    nvarchar(255) './EAP-Friendly-Name',

        Acct_Status_Type

    int './Acct-Status-Type',

        Acct_Delay_Time

    int './Acct-Delay-Time',

        Acct_Input_Octets

    int './Acct-Input-Octets',

        Acct_Output_Octets

    int './Acct-Output-Octets',

        Acct_Session_Id

    nvarchar(255) './Acct-Session-Id',

        Acct_Authentic

    int './Acct-Authentic',

        Acct_Session_Time

    int './Acct-Session-Time',

        Acct_Input_Packets

    int './Acct-Input-Packets',

        Acct_Output_Packets

    int './Acct-Output-Packets',

        Acct_Terminate_Cause

    int './Acct-Terminate-Cause',

        Acct_Multi_Session_Id

    nvarchar(255) './Acct-Multi-Session-Id',

        Acct_Link_Count

    int './Acct-Link-Count',

        Acct_Interim_Interval

    int './Acct-Interim-Interval',

        Tunnel_Type

    int './Tunnel-Type',

        Tunnel_Medium_Type

    int './Tunnel-Medium-Type',

        Tunnel_Client_Endpoint

    nvarchar(255) './Tunnel-Client-Endpt',

        Tunnel_Server_Endpoint

    nvarchar(255) './Tunnel-Server-Endpt',

        Acct_Tunnel_Connection

    nvarchar(255) './Acct-Tunnel-Connection',

        Tunnel_Pvt_Group_Id

    nvarchar(255) './Tunnel-Pvt-Group-Id',

        Tunnel_Assignment_Id

    nvarchar(255) './Tunnel-Assignment-Id',

        Tunnel_Preference

    int './Tunnel-Preference',

        MS_Acct_Auth_Type

    int './MS-Acct-Auth-Type',

        MS_Acct_EAP_Type

    int './MS-Acct-EAP-Type',

        MS_RAS_Version

    nvarchar(255) './MS-RAS-Version',

        MS_RAS_Vendor

    int './MS-RAS-Vendor',

        MS_CHAP_Error

    nvarchar(255) './MS-CHAP-Error',

        MS_CHAP_Domain

    nvarchar(255) './MS-CHAP-Domain',

    MS_MPPE_Encryption_Types

    int './MS-MPPE-Encryption-Types',

    MS_MPPE_Encryption_Policy

    int './MS-MPPE-Encryption-Policy',

        Proxy_Policy_Name

    nvarchar(255) './Proxy-Policy-Name',

        Provider_Type

    int './Provider-Type',

        Provider_Name

    nvarchar(255) './Provider-Name',

        Remote_Server_Address

    nvarchar(15) './Remote-Server-Address',

        MS_RAS_Client_Name

    nvarchar(255) './MS-RAS-Client-Name',

        MS_RAS_Client_Version

    nvarchar(255) './MS-RAS-Client-Version',

    /*

        NAP-specific information, available from NPS starting with Windows Server 2008.

    */

    Wednesday, February 12, 2014 10:05 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello, 

    I have  a  simular  problem,

    is it possible  to record in SQL Server, the  Attributes that are not recorded in IAS format log files?(http://technet.microsoft.com/en-us/library/dd197432%28v=ws.10%29.aspx)

    attribute  like  User-Password. 

    Thank you in advance. 

    Friday, March 7, 2014 9:48 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Sorry for replying to a thread thats more than a year old, but I have run into this issue as well.

    The 2012 R2 NPS SQL database has less fields than the 2008 R2 version.

    I have been able to get some fields back in the following way:

    Step 1. Use the MS SQL Server Management Studio and edit the dbo.accounting_data table.
    Add the fields that you need, in your case: Reason_Code (Int)

    Make sure you save the table

    Step2.
    Then modify  dbo.report_event  (Programmability -> Stored Procedures)
    Add the new field at the same position under

    Insert accounting_data
    SELECT

    eg, if you placed the field in the table after Packet_Type:

    Insert accounting_data
    SELECT
     Timestamp,
     Computer_Name,
     Packet_Type,
     Reason_Code,


    Step 3. Then go to the part under With(
    And insert the field and fieldtype at the same location, eg:

    WITH (
    Timestamp datetime './Timestamp',
    Computer_Name nvarchar(255) './Computer-Name',
    Packet_Type int './Packet-Type',
    Reason_Code int './Reason-Code',

    I always execute to check if I don't get errors.
    That should be all, I have noticed that not every field give me a satisfying result.
    And I try field for field, to avoid breaking the entire thing.

    Thursday, May 7, 2015 11:56 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Sorry for replying to a thread thats more than a year old, but I have run into this issue as well.

    The 2012 R2 NPS SQL database has less fields than the 2008 R2 version.

    I have been able to get some fields back in the following way:

    Step 1. Use the MS SQL Server Management Studio and edit the dbo.accounting_data table.
    Add the fields that you need, in your case: Reason_Code (Int)

    Make sure you save the table

    Step2.
    Then modify  dbo.report_event  (Programmability -> Stored Procedures)
    Add the new field at the same position under

    Insert accounting_data
    SELECT

    eg, if you placed the field in the table after Packet_Type:

    Insert accounting_data
    SELECT
     Timestamp,
     Computer_Name,
     Packet_Type,
     Reason_Code,


    Step 3. Then go to the part under With(
    And insert the field and fieldtype at the same location, eg:

    WITH (
    Timestamp datetime './Timestamp',
    Computer_Name nvarchar(255) './Computer-Name',
    Packet_Type int './Packet-Type',
    Reason_Code int './Reason-Code',

    I always execute to check if I don't get errors.
    That should be all, I have noticed that not every field give me a satisfying result.
    And I try field for field, to avoid breaking the entire thing.

    Works like a charm ;) I just have to use [] for some specific fields using - and not _

    Thanks for this useful sharing

    GIRAUD Alexandre - MVP Forefront France http://www.alexgiraud.net/blog Note : Si ma réponse vous a été utile, ou apporté une résolution; merci de voter ou de la marquer comme réponse.

    Friday, June 3, 2016 11:01 AM