locked
NAP XP SP3 problems when SQL Logging RRS feed

  • Question

  • Hi,

    I have used the following DHCP NAP step-by-step guide to configure my POC environment:
    http://www.microsoft.com/downloads/details.aspx?FamilyID=ac38e5bb-18ce-40cb-8e59-188f7a198897&displaylang=en

    First thing I noticed is that the XP client reports incorrect compliant and non-compliant states.

    So I found some other threads here: http://social.technet.microsoft.com/Forums/en-US/itproxpsp/thread/f7abe0f2-0186-428c-9252-9d22b03dd496
    And I tried this setting on the client:
    [HKLM\SYSTEM\CurrentControlSet\Services\Dhcp\Parameters\DhcpGlobalForceBroadcastFlag\0] "0"=dword:00000001

    But the problem still persists....I can disable the Firewall, Windows Updates, have no AV product installed...and sometimes the NAP client reports non-compliance, then a few minutes later it says everything is compliant.

    THEN I found this little anomaly:
    If I do not log to SQL; NAP client seem to report work correctly.
    BUT the moment I configure NPS logging to SQL, the NAP client does not work - I can have everything disable on the client (firewall, av, etc) and NAPSTAT displays a very healthy system.

    For my test environment, I am running a single VM with Windows 2008 AD, NPS, DHCP, SQL 2005 SP2; and another single VM with XP SP3.

    Regards,
    Tom 
    • Edited by D Wind Thursday, August 20, 2009 5:38 PM
    Thursday, August 20, 2009 11:17 AM

Answers

  • Hi,

    Just an update for the forum.

    I eventually moved to the production environment; where things appear to be working:

    On the DC/DHCP server I installed and configured the NPS Proxy.

    Then installed WS08 R2 on another machine, in hyper-v, and installed SQL 2008 Express Advanced Edition.
    Then installed the NAP Reporting Beta.
    Then installed NPS, and used its wizard to create the SQL db, tables & stored procedures.

    Finally, things seem to be working as expected :-)
    So thank you all for your feedback and patience.

    I presume the issues I was having were related only to the virtual environment I was testing in.

    Kind regards,
    Tom
    Saturday, August 29, 2009 6:28 AM

All replies

  • Hi,

    I have removed SQL from the 2008 server; and am installing it on another VM inside Windows Server 2003 R2.
    Will let you know if that resolves the issue.

    Regards,
    Tom
    Thursday, August 20, 2009 1:04 PM
  • OK,

    I have installed and configured SQL 2005 on another VM, on Windows 2003 R2.

    The weird problem still persists.

    If I do not log to SQL, then NAP works as expected.

    The moment I enable SQL logging, to the ReportServer database - NAP does NOT work at all on the client...nothing happens...

    Looking forward to some suggestions,

    Regards
    Tom
    Thursday, August 20, 2009 5:12 PM
  • Hello Tom,

     

    Thanks for your post here.

     

    First of all, I'd like to know whether you have the KB 956463 installed on the server. If haven't, install it and check how it works.

     

    Windows Server 2008 Network Policy Server (NPS) recognizes all computers as domain controllers in the NAP reporting data

    http://support.microsoft.com/kb/956463

     

    Please help to collect the following information for further investigation:

     

    You can collect the MPS report (PFE version) on the server for the analyzing. The MPS Reporting Tool is utilized to gather detailed information regarding a systems current configuration including event logs. To collect the PFE log:

     

    a. Please download MPS Reporting Tool (MPSRPT_PFE.EXE) from the following link:

    (http://www.microsoft.com/downloads/details.aspx?FamilyID=00ad0eac-720f-4441-9ef6-ea9f657b5c2f&DisplayLang=en)

     

    Please note: The link may be truncated when you read the E-mail. Be sure to include all text between '(' and ')' when navigating to the download location.

     

    b. Right click MPSRPT_PFE.EXE and select Run as Administrator to run this tool, and you will see a Command Window start up.

     

    c. Please type Y with the message of <Include the MSINFO32 report? (defaults to Y in 15 seconds)[Y,N]?

     

    d. When the tool is done you will see an Explorer Window opening up the %systemroot%\MPSReports\Setup\Reports\cab folder and containing a <Computername>MPSReports.cab file. Then send the package to me at v-mileli@microsoft.com for further investigation.

     

    If you have any questions or concerns, please do not hesitate to let me know.

     

    Friday, August 21, 2009 10:30 AM
  • Miles,

    I have tried the hotfix - but it did not help.

    Subsequently I have emailed you the report .cab file.

    Hope you find something,

    Tom
    Friday, August 21, 2009 12:00 PM
  • Hi Tom,

    Have you confirmed that after enabling SQL logging that authentication is successful? Please review the event logs on NPS under Custom Views\Server Roles\Network Policy and Access Services. This should tell you what policies are matched by client access requests, and what kind of network access is being applied. If will also tell you if client access requests are not occuring or if they are being discarded for some reason.

    If a DHCP client is unable to authenticate, it would not be granted any access. In this state it may appear compliant because it has no policy requirements while disconnected from the network.

    If NPS is unable to log to your SQL database, perhaps due to a configuration problem with the SQL table, this can cause a problem with client authentication. NPS passes RADIUS attributes in XML to a stored procedure named Report_Event.  You must create this stored procedure along with creating the dB and creating the schema; tables, SPs, etc. If Report_Event does not complete with success, authentication will fail. This behavior is configurable in R2.

    To do NAP SQL logging, you must:

    1) Create a database (ex: NPSXML)
    2) Create a table (ex: NPS_Packets)
    3) Create a stored procedure (must be named Report_Event)

    Here is an example of the commands used to create such a database, table, and procedure:

     

    USE [master]

    CREATE DATABASE [NPSXML] ON PRIMARY

    (NAME = N'NPSXML', FILENAME = N'D:\NPSSQL\NPSXML.mdf')

    LOG ON

    (NAME = N'NPSXML_log', FILENAME = N'D:\NPSSQL\NPSXML_log.LDF')

     

    USE [NPSXML]

    CREATE TABLE [dbo].[NPS_Packets](

    [PacketTime] [datetime] NOT NULL DEFAULT (getutcdate()),

    [NPS_Attributes] [xml] NOT NULL

    ) ON [PRIMARY]

     

    CREATE PROCEDURE [dbo].[Report_Event]

    (@doc nvarchar(max))

    AS

    INSERT INTO NPS_Packets

    (PacketTime, NPS_Attributes)

    VALUES (GETUTCDATE(), @doc)

    -Greg

    Note: This post was edited to correct a typo in an endquote, to make the quote ASCII compliant.
    Saturday, August 22, 2009 5:18 AM
  • Greg,

    Thank you for that script...but when I execute it I get the following error message:

    Msg 153, Level 15, State 1, Line 4
    Invalid usage of the option NPSXML_log in the CREATE/ALTER DATABASE statement.
    Msg 105, Level 15, State 1, Line 4
    Unclosed quotation mark after the character string ')   
    USE [NPSXML]  
    CREATE TABLE [dbo].[NPS_Packets] ([PacketTime] [datetime] NOT NULL DEFAULT (getutcdate()),  
    [NPS_Attributes] [xml] NOT NULL) ON [PRIMARY]  
    CREATE PROCEDURE [dbo].[Report_Event]  
    (@doc nvarchar(max))  
    AS  
    INSERT INTO NPS_Packets (PacketTime, NPS_Attributes)  
    VALUES (GETUTCDATE(), @doc)
    '.

    Any ideas?

    Thanks,
    Tom
    Monday, August 24, 2009 8:01 AM
  • Hi Tom,

    It's not really a script, more of an example to use. I should probably change D:\NPSSQL\NPSXML.mdf to C:\NPSSQL\NPSXML.mdf because if you don't have a D: drive it will fail.

    I would try debugging a little by executing the commands one at a time. Maybe change D:\ to C:\.

    USE [master]
    CREATE DATABASE [NPSXML] ON PRIMARY (NAME = N'NPSXML', FILENAME = N'D:\NPSSQL\NPSXML.mdf’)
    LOG ON (NAME = N'NPSXML_log', FILENAME = N'D:\NPSSQL\NPSXML_log.LDF')
    USE [NPSXML]
    CREATE TABLE [dbo].[NPS_Packets]([PacketTime] [datetime] NOT NULL DEFAULT (getutcdate()),[NPS_Attributes] [xml] NOT NULL) ON [PRIMARY]
    CREATE PROCEDURE [dbo].[Report_Event] (@doc nvarchar(max)) AS INSERT INTO NPS_Packets (PacketTime, NPS_Attributes) VALUES (GETUTCDATE(), @doc)

    -Greg
    Monday, August 24, 2009 8:26 AM
  • Greg,

    I should have told you that I had already done that.

    If I copy & paste the above script...I get these errors:

    Msg 153, Level 15, State 1, Line 3
    Invalid usage of the option NPSXML_log in the CREATE/ALTER DATABASE statement.
    Msg 105, Level 15, State 1, Line 3
    Unclosed quotation mark after the character string ')
    USE [NPSXML]
    CREATE TABLE [dbo].[NPS_Packets]([PacketTime] [datetime] NOT NULL DEFAULT (getutcdate()),[NPS_Attributes] [xml] NOT NULL) ON [PRIMARY]
    CREATE PROCEDURE [dbo].[Report_Event] (@doc nvarchar(max)) AS INSERT INTO NPS_Packets (PacketTime, NPS_Attributes) VALUES (GETUTCDATE(), @doc)
    '.
    Monday, August 24, 2009 8:33 AM
  • Just noticed a typo...

    USE [master]
    CREATE DATABASE [NPSXML] ON PRIMARY (NAME = N'NPSXML', FILENAME = N'D:\NPSSQL\NPSXML.mdf’)

    That last single quote should be ' and not ’



    The new code is:

    USE [master]
    CREATE DATABASE [NPSXML] ON PRIMARY (NAME = N'NPSXML', FILENAME = N'C:\NPSSQL\NPSXML.mdf')
    LOG ON (NAME = N'NPSXML_log', FILENAME = N'C:\NPSSQL\NPSXML_log.LDF')
    USE [NPSXML]
    CREATE TABLE [dbo].[NPS_Packets]([PacketTime] [datetime] NOT NULL DEFAULT (getutcdate()),[NPS_Attributes] [xml] NOT NULL) ON [PRIMARY]
    CREATE PROCEDURE [dbo].[Report_Event] (@doc nvarchar(max)) AS INSERT INTO NPS_Packets (PacketTime, NPS_Attributes) VALUES (GETUTCDATE(), @doc)

    and the new error is:

    Msg 911, Level 16, State 1, Line 4
    Could not locate entry in sysdatabases for database 'NPSXML'. No entry found with that name. Make sure that the name is entered correctly.

    PS. I have manually created the C:\NPSSQL folder

    • Edited by D Wind Monday, August 24, 2009 8:41 AM
    Monday, August 24, 2009 8:37 AM
  • Good catch,

    This may help:

    http://msdn.microsoft.com/en-us/library/aa258742(SQL.80).aspx

    If you still have trouble I'll have to help debug further tomorrow. I don't have access to my NPS right now, and it's nearly 2AM here =)

    -Greg

    P.S. Make sure you change D:\ in both lines 2 and 3 (create and log on). Also make sure you a pointing to the right server.
    Monday, August 24, 2009 8:46 AM
  • Greg,

    I tried to execute the code individually...and got a 'Command completed successfully' after each line.

    Ran this first:
    USE [master]
    CREATE DATABASE [NPSXML] ON PRIMARY (NAME = N'NPSXML', FILENAME = N'C:\NPSSQL\NPSXML.mdf')
    Command(s) completed successfully

    Then this:
    CREATE TABLE [dbo].[NPS_Packets]([PacketTime] [datetime] NOT NULL DEFAULT (getutcdate()),[NPS_Attributes] [xml] NOT NULL) ON [PRIMARY]
    Command(s) completed successfully

    Then this:
    CREATE PROCEDURE [dbo].[Report_Event] (@doc nvarchar(max)) AS INSERT INTO NPS_Packets (PacketTime, NPS_Attributes) VALUES (GETUTCDATE(), @doc)
    Command(s) completed successfully

    I will test NPS logging now; and if that works, I will try figure out how to get the SQL Reports out of this lot.

    Thanks,
    Tom
    Monday, August 24, 2009 8:58 AM
  • Hi,

    So I have configured NPS for SQL logging, the config test was successfull....but the NAP client problem still persists.

    Here is the NPS Event Log entry:

    Network Policy Server discarded the request for a user.
    Contact the Network Policy Server administrator for more information.

    User:
     Security ID:   NULL SID
     Account Name:   -
     Account Domain:   -
     Fully Qualified Account Name: -

    Client Machine:
     Security ID:   TEST\SGCLIENT$
     Account Name:   SGCLIENT.test.com
     Fully Qualified Account Name: TEST\SGCLIENT$
     OS-Version:   5.1.2600 3.0 x86 Workstation
     Called Station Identifier:  192.168.1.0
     Calling Station Identifier:  0003FF6B5E80

    NAS:
     NAS IPv4 Address:  192.168.1.1
     NAS IPv6 Address:  -
     NAS Identifier:   BLVADS02
     NAS Port-Type:   Ethernet
     NAS Port:   -

    RADIUS Client:
     Client Friendly Name:  -
     Client IP Address:   -

    Authentication Details:
     Proxy Policy Name:  NAP DHCP
     Network Policy Name:  NAP DHCP Noncompliant
     Authentication Provider:  Windows
     Authentication Server:  BLVADS02.test.com
     Authentication Type:  Unauthenticated
     EAP Type:   -
     Account Session Identifier:  32393137363736303236
     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.


    IF I remove the SQL logging, then everything works as expected, with the following Event Logged:

    Network Policy Server granted access to a user but put it on probation because the host did not meet the defined health policy.

    Contact the Network Policy Server administrator for more information.

    User:
     Security ID:   NULL SID
     Account Name:   -
     Account Domain:   -
     Fully Qualified Account Name: -

    Client Machine:
     Security ID:   TEST\SGCLIENT$
     Account Name:   SGCLIENT.test.com
     Fully Qualified Account Name: TEST\SGCLIENT$
     OS-Version:   5.1.2600 3.0 x86 Workstation
     Called Station Identifier:  192.168.1.0
     Calling Station Identifier:  0003FF6B5E80

    NAS:
     NAS IPv4 Address:  192.168.1.1
     NAS IPv6 Address:  -
     NAS Identifier:   BLVADS02
     NAS Port-Type:   Ethernet
     NAS Port:   -

    RADIUS Client:
     Client Friendly Name:  -
     Client IP Address:   -

    Authentication Details:
     Proxy Policy Name:  NAP DHCP
     Network Policy Name:  NAP DHCP Noncompliant
     Authentication Provider:  Windows
     Authentication Server:  BLVADS02.test.com
     Authentication Type:  Unauthenticated
     EAP Type:   -
     Account Session Identifier:  31343830303839333835

    Quarantine Information:
     Result:    On Probation
     Extended-Result:   -
     Session Identifier:   {9B462BE4-B26E-45FE-B4FB-06DC30C00FB4} - 2009-08-24 10:27:00.068Z
     Help URL:   http://blvads02.test.com/nap.htm
     System Health Validator Result(s): 
    Windows Security Health Validator..
     NonCompliant
     No Data
     None
     (0x0 - )
     (0xc0ff0002 - A system health component is not installed...)
     (0x0 - )
     (0x0 - )
     (0x0 - )
     (0x0 - )
     Quarantine Grace Time:  2010-01-01 09:55:53.000


    I am going to review the whitepaper: Deploying SQL Server Logging with Windows Server 2003 Internet Authentication Service - maybe I will find some answers there

    Regards,
    Tom

    • Edited by D Wind Monday, August 24, 2009 10:54 AM
    Monday, August 24, 2009 10:18 AM
  • Hi Tom,

    If you have SQL on a different server than NPS, please change it for now so that NPS is logging locally. I think you mentioned you had SQL running on a 2008 R2 server and NPS was on a 2008 server. This is a supported scenario, but I've had trouble doing it and this (scenario 4) is high risk due to potential connectivity issues. It's always best to log locally.

    Here is a topic that discuss SQL logging for NAP at a high level: http://technet.microsoft.com/en-us/library/dd125332(WS.10).aspx
    There is a figure showing an example setup at: http://technet.microsoft.com/en-us/library/dd125365(WS.10).aspx

    One thing to keep in mind is that after you get the setup logging successfully - which I'm pretty sure will work easily when you try it on the local NPS - you still will need to write some SQL queries to pull the data. If you are new to SQL this will not be trivial, but again I can try to help. If you want to discuss over email, you can email me at: greglin at online.microsoft.com <-- remove "online" to actually email me.

    -Greg

    Monday, August 24, 2009 11:26 PM
  • Hi Tom,

    Can you enable tracing by running " netsh ras set tr * en". Perform one round of NAP evaluation. Check the file  c:\windows\tracing\iasacct.log. It should give more details on why the sql logging is failing.

    Thanks,
    Srinivasulu.
    Friday, August 28, 2009 12:32 AM
  • Hi,

    Just an update for the forum.

    I eventually moved to the production environment; where things appear to be working:

    On the DC/DHCP server I installed and configured the NPS Proxy.

    Then installed WS08 R2 on another machine, in hyper-v, and installed SQL 2008 Express Advanced Edition.
    Then installed the NAP Reporting Beta.
    Then installed NPS, and used its wizard to create the SQL db, tables & stored procedures.

    Finally, things seem to be working as expected :-)
    So thank you all for your feedback and patience.

    I presume the issues I was having were related only to the virtual environment I was testing in.

    Kind regards,
    Tom
    Saturday, August 29, 2009 6:28 AM
  • Hi All,

    I am facing the similar issue.

    I have my windows 2008 SP1 and all my XP endpoints are in VMs. As soon as I enable SQL Logging NAP enforcement (DHCP) seems to be not working and the client reports the machne is compliant.

    Any help on this much appreciated.

    Thanks in advance.
    Rao
    Friday, November 13, 2009 4:26 PM
  • Nageswara,

    It all has to do with incorrect or missing SQL tables & stored procedures.
    You either have to manually create these things - or get the NAP Reporting Beta Toolkit from MS Connect; as this will set up SQL logging correctly for you.

    regards,
    tz
    Saturday, November 21, 2009 6:29 AM
  • I searched NAP reporting beta toolkit on ms connect, but I can't find it, could you show me direct link? I just found out MAP beta 5.0 CTP when I tried to search NAP reporting beta toolkit.

    Thank you
    Liu-xiang Chen
    Tuesday, December 15, 2009 3:10 PM