Asked by:
NPS SQL missing reason-code in Server 2012 R2

Question
-
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
-
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 -
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 -
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 -
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 -
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 -
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 underInsert 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.- Proposed as answer by GIRAUD Alexandre - MVP Forefront - 3SR Friday, June 3, 2016 11:00 AM
Thursday, May 7, 2015 11:56 AM -
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 underInsert 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