Forum Discussion

ComputerHabit's avatar
ComputerHabit
Brass Contributor
Apr 18, 2024

NPS ODBC Logging on Server 2022 isn't working

I've setup new NPS servers.  When I setup accounting with an SQL DB connection the tables are not generated correctly.  I have an old install and the tables have much more info.  

I have tried to change to ODBC and IAS log formats, but I can't seem to get it to work.  The tables are not generated unless I run the wizard.  The wizard always sets things back to DTS compliant.

I have searched for days now on a fix. 

What is the problem with NPS?  Does anyone know how to fix this?

HELP

  • ComputerHabit's avatar
    ComputerHabit
    Brass Contributor

    Just for reference:

    NPS Table created:

    /****** Script for SelectTopNRows command from SSMS  ******/
    SELECT TOP (1000) [id]
          ,[timestamp]
          ,[Computer_Name]
          ,[Packet_Type]
          ,[User_Name]
          ,[Client_IP_Address]
          ,[Fully_Qualified_Machine_Name]
          ,[NP_Policy_Name]
          ,[MS_Quarantine_State]
          ,[MS_Extended_Quarantine_State]
          ,[System_Health_Result]
          ,[System_Health_ResultEx]
          ,[MS_Network_Access_Server_Type]
          ,[Called_Station_Id]
          ,[MS_Quarantine_Grace_Time]
          ,[MS_Quarantine_User_Class]
          ,[Client_IPv6_Address]
          ,[Not_Quarantine_Capable]
          ,[AFW_Zone]
          ,[AFW_Protection_Level]
          ,[Quarantine_Update_Non_Compliant]
          ,[MS_Machine_Name]
          ,[OS_Version]
          ,[MS_Quarantine_Session_Id]
      FROM [NPSODBC].[dbo].[accounting_data]



    NPS Table on my other NPS setup:

    /****** Script for SelectTopNRows command from SSMS  ******/
    SELECT TOP 1000 [id]
          ,[timestamp]
          ,[Computer_Name]
          ,[Packet_Type]
          ,[User_Name]
          ,[F_Q_User_Name]
          ,[Called_Station_Id]
          ,[Calling_Station_Id]
          ,[Callback_Number]
          ,[Framed_IP_Address]
          ,[NAS_Identifier]
          ,[NAS_IP_Address]
          ,[NAS_Port]
          ,[Client_Vendor]
          ,[Client_IP_Address]
          ,[Client_Friendly_Name]
          ,[Event_Timestamp]
          ,[Port_Limit]
          ,[NAS_Port_Type]
          ,[Connect_Info]
          ,[Framed_Protocol]
          ,[Service_Type]
          ,[Authentication_Type]
          ,[NP_Policy_Name]
          ,[Reason_Code]
          ,[Class]
          ,[Session_Timeout]
          ,[Idle_Timeout]
          ,[Termination_Action]
          ,[EAP_Friendly_Name]
          ,[Acct_Status_Type]
          ,[Acct_Delay_Time]
          ,[Acct_Input_Octets]
          ,[Acct_Output_Octets]
          ,[Acct_Session_Id]
          ,[Acct_Authentic]
          ,[Acct_Session_Time]
          ,[Acct_Input_Packets]
          ,[Acct_Output_Packets]
          ,[Acct_Terminate_Cause]
          ,[Acct_Multi_Session_Id]
          ,[Acct_Link_Count]
          ,[Acct_Interim_Interval]
          ,[Tunnel_Type]
          ,[Tunnel_Medium_Type]
          ,[Tunnel_Client_Endpoint]
          ,[Tunnel_Server_Endpoint]
          ,[Acct_Tunnel_Connection]
          ,[Tunnel_Pvt_Group_Id]
          ,[Tunnel_Assignment_Id]
          ,[Tunnel_Preference]
          ,[MS_Acct_Auth_Type]
          ,[MS_Acct_EAP_Type]
          ,[MS_RAS_Version]
          ,[MS_RAS_Vendor]
          ,[MS_CHAP_Error]
          ,[MS_CHAP_Domain]
          ,[MS_MPPE_Encryption_Types]
          ,[MS_MPPE_Encryption_Policy]
          ,[Proxy_Policy_Name]
          ,[Provider_Type]
          ,[Provider_Name]
          ,[Remote_Server_Address]
          ,[MS_RAS_Client_Name]
          ,[MS_RAS_Client_Version]
          ,[MS_Quarantine_State]
      FROM [NPSODBC].[dbo].[accounting_data]

     

Resources