locked
NPS SQL Logging not working RRS feed

  • Question

  • I am running NPS on a Windows 2008 enterprise server in a VM. I also installed SQLExpress 2008 w/adv tools on the same server. My goal is to have NPS log to SQL to generate reports. I ran the Accounting wizard to create the database, so I have the required stored procedure in SQL and I am using Windows Authentication in SQL. I can authenticate to my Cisco devices, wireless clients and VPN users. I have no problem when I use the local logging. When I setup NPS accounting, the data link connects successfully. After setting up SQL, I get the ReasonCode 80 in the NPS event log. I don;t know what I am missing. Any ideas?

    Saturday, March 13, 2010 4:41 PM

Answers

  • Hmmm...

    To summarize, you've configured NPS to use SQL logging via the Accounting wizard which also created your DB in SQL Express, initialized the DB, and created the necessary stored procedures. Yet still, when you make a RADIUS authentication with NPS, an Event Log error appears with reason code 80, signifying a problem with Accounting.

    Here are some other things to look at:

    1. Are we sure it is SQL Accounting and not File Log Accounting causing the error? The event is a generic event for any Acounting failure I believe. Do you have File Log Accounting enabled as well? Can you disable it and only use SQL Logging to test?
    2. If I recall correctly, SQL Express doesn't enable TCP connectivity out of the box; only named pipes. I gathered that you have SQL Express installed on the same box as NPS. Can you try enabling TCP connectivity for SQL? Especially if you have SQL Express installed on another machine (you'll need firewall rules as well).

    Also, you might try enabling NPS tracing to determine if there are any clues to the problem there.

    Run the command "netsh ras set tr * en" in an elevated command prompt and the traces should appear as "%windir%\tracing\*.log". The logs are written as UTF8 but notepad will try to open them as UNICODE. You'll need to set the encoding manually or use a different editor to view them.


    This TechNet forum post is provided "AS IS" with no warranties, and confers no rights. This entry reflects my own personal views and does not necessarily reflect the view of my employer.
    • Marked as answer by Miles Zhang Monday, March 29, 2010 2:53 AM
    Friday, March 26, 2010 8:21 PM

All replies

  • In the Data Link Properties dialog, are you using "Use Windows NT Integrated security" or specifying a username/password?

    I suspect that when you are using the Data Link Properties dialog, you are logged in with an account which has access to the SQL server and it is connecting. When the NPS service tries to connect to the SQL server it is running under the Local System account which can't access SQL.

    Check that you are providing the SQL server credentials to the Data Link Properties dialog.

    This TechNet forum post is provided "AS IS" with no warranties, and confers no rights. This entry reflects my own personal views and does not necessarily reflect the view of my employer.
    • Marked as answer by Miles Zhang Monday, March 22, 2010 2:43 AM
    • Unmarked as answer by mbreaux Tuesday, March 23, 2010 2:11 AM
    Monday, March 15, 2010 10:24 PM
  • I am using Windows NT Integrated security. You are correct. The account for NPS is the local service account. I will setup SQL credentials and test those.
    Tuesday, March 16, 2010 8:24 PM
  • Ok, so I created a local SQL account and used it in the Data Link Propoerties. I was able to a make a successful connection. I tested again, and got the same message from the NPS logs.
    The SQL logs show I am able to authenticate successfully.  Do I need to change how the NPS service acct logs in or the acct is uses? Is there anything I can check to get more specific info on why this is happening? Thanks!
    -----------------------------------

    Network Policy Server discarded the accounting request for a user.

    Contact the Network Policy Server administrator for more information.

    User:

    Security ID: NULL SID

    Account Name: mbreaux

    Account Domain: -

    Fully Qualified Account Name: -

    Client Machine:

    Security ID: NULL SID

    Account Name: -

    Fully Qualified Account Name: -

    OS-Version: -

    Called Station Identifier: -

    Calling Station Identifier: <ip address>

    NAS:

    NAS IPv4 Address: <ip address>

    NAS IPv6 Address: -

    NAS Identifier: -

    NAS Port-Type: Virtual

    NAS Port: 2

    RADIUS Client:

    Client Friendly Name: <Radius client name>

    Client IP Address: <ip address>

    Proxy Policy Name: Use Windows authentication for all users

    Network Policy Name: -

    Authentication Provider: -

    Authentication Server: <Radius server name>

    Authentication Type: -

    EAP Type: -

    Account Session Identifier: 3030303030303846

    Reason Code: 80

    Reason: The authentication or accounting record could not be written to the log file location. Ensure that the log file location is accessible, has available space, can be written to, and that the directory or SQL server name is valid.

    Tuesday, March 16, 2010 8:44 PM
  • Anyone got ideas on how to fix this?
    Tuesday, March 23, 2010 2:10 AM
  • Things to check on the Data Link Properties page:

    • Do you have the name of the SQL server (1.) as "<whatever you machine name is>\SQLEXPRESS" since you are using SQL Express?
    • Did you check the "Allow saving password" checkbox (2.) so NPS can use the credentials you entered?
    • When you ran the Accounting wizard, did you enter a database name (3.) on the Data Link Properties page?
    • Is this database name configured when you view the Data Link Property page (3.) from the NPS SQL Server Logging Properties page?

    Other things you can check:

    • Do you have SQL Server Management Studio installed with SQL Server 2008 Express?
    • Can you use this account you created with SQL Server Management Studio to connect to your database?
    • Does SQL have any Events or logs you can check?

    This TechNet forum post is provided "AS IS" with no warranties, and confers no rights. This entry reflects my own personal views and does not necessarily reflect the view of my employer.
    • Proposed as answer by Manish1989 Monday, June 29, 2015 8:53 AM
    Tuesday, March 23, 2010 7:44 PM
  • Yes, to all of your questions. The SQL logs show "Logon succeeded for user "RADIUS". Connection made using SQL Server authentication. [CLIENT: <local machine>]".

    Is there something in SQL I need to look at?

     

     

    Wednesday, March 24, 2010 5:10 PM
  • Does your SQL account have permission to write to the database?


    This TechNet forum post is provided "AS IS" with no warranties, and confers no rights. This entry reflects my own personal views and does not necessarily reflect the view of my employer.
    Wednesday, March 24, 2010 6:30 PM
  • I believe so. The SQL account I am using has sysadmin permissions to the SQL instance and the db_owner permission to the db. This account should be able to write to the db.
    Thursday, March 25, 2010 9:12 PM
  • Hmmm...

    To summarize, you've configured NPS to use SQL logging via the Accounting wizard which also created your DB in SQL Express, initialized the DB, and created the necessary stored procedures. Yet still, when you make a RADIUS authentication with NPS, an Event Log error appears with reason code 80, signifying a problem with Accounting.

    Here are some other things to look at:

    1. Are we sure it is SQL Accounting and not File Log Accounting causing the error? The event is a generic event for any Acounting failure I believe. Do you have File Log Accounting enabled as well? Can you disable it and only use SQL Logging to test?
    2. If I recall correctly, SQL Express doesn't enable TCP connectivity out of the box; only named pipes. I gathered that you have SQL Express installed on the same box as NPS. Can you try enabling TCP connectivity for SQL? Especially if you have SQL Express installed on another machine (you'll need firewall rules as well).

    Also, you might try enabling NPS tracing to determine if there are any clues to the problem there.

    Run the command "netsh ras set tr * en" in an elevated command prompt and the traces should appear as "%windir%\tracing\*.log". The logs are written as UTF8 but notepad will try to open them as UNICODE. You'll need to set the encoding manually or use a different editor to view them.


    This TechNet forum post is provided "AS IS" with no warranties, and confers no rights. This entry reflects my own personal views and does not necessarily reflect the view of my employer.
    • Marked as answer by Miles Zhang Monday, March 29, 2010 2:53 AM
    Friday, March 26, 2010 8:21 PM
  • The "allow saving password" option helped.

    Shiva

    Monday, June 29, 2015 8:53 AM
  • I had the same issue.

    Radius server had event and could not write logs to the SQL database (not express).

    The setup of logging was successful using SQL user account and connection looked good.

    I have checked the box to discard the connection if logging fails (on the accounting setup page) - just like I am sure you have ticked too.

    Resolution:

    I initially had db_dataReaderdb_dataWriter and Public permissions set for the SQL user to the database.  Then SQL instance permission was set to sysadmin and Public.

    the correct permissions required to resolve this was db_owner and Public on the database. and keep the sysadmin AND Public permissions for the SQL instance.

    if you have these already set and you are sure they should work but they are not working , then it would be worth double checking by changing them and change them back to what I have specified above because it is what worked for me. after getting the same exactly the same problem you have had.

    Thanks Matt for the helpful hints here that helped me fix other things that were wrong.


    Friday, July 24, 2020 4:09 PM