NPS ODBC Logging on Server 2022 isn't working

Brass Contributor

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

1 Reply

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]