Threat Report: Exposing Malware in Linux-Based Multi-Cloud Environments | Download Now

App Control: Disconnected Agents with Last Poll Date Shown in the Future (Violation of UNIQUE KEY)

App Control: Disconnected Agents with Last Poll Date Shown in the Future (Violation of UNIQUE KEY)

Environment

  • App Control Server: All Supported Versions
  • App Control Agent: All Supported Versions

Symptoms

  • App control Agent is not able to communicate with the App Control Console.
  • Dascli status outputs shows:
    Client Information
    Connection: disconnected (not permitted)
    or
    Client Information 
    Connection: connected (not permitted)
    
  • App Control Server logs shows messages like below
    [533517] <date> <hour> (8304 Register Thread 0) Statement returned error [2627]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Violation of UNIQUE KEY constraint 'UQ__hostmain__4E88F7AD5AEE82B9'. Cannot insert duplicate key in object 'dbo.hostmain'. The duplicate key value is (36affad1-0525-11ec-80c0-000c292e39fb-6daeef17).
    Statement: SET NOCOUNT ON;{CALL dbo.RegisterHost (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
    [533518] <date> <hour> (8304 Register Thread 0) Host <domainName>\<hostname> [-1] record created.
    [533519] <date> <hour> (8304 Register Thread 0) Error creating host record for computer <domainName>\<hostname> from <IP address>.
    [533520] <date> <hour> (8304 Register Thread 0) Error: Extra Result Set: 1, Statement: SET NOCOUNT ON;{CALL dbo.RegisterHost (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
    [533521] <date> <hour> (8304 Register Thread 0) Error: Extra SQL Message: #2, Message: [Microsoft][SQL Server Native Client 11.0][SQL Server]The 'RegisterHost' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead., Statement: SET NOCOUNT ON;{CALL dbo.RegisterHost (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
    [533522] <date> <hour> (8304 Register Thread 0) SQL Connection was disconnected. Server: localhost
    [533523] <date> <hour> (8304 Register Thread 0) Registration of <domainName>\<hostname> from <IP address> failed, returning error 401.

Cause

  • Duplicated cookie id for endpoint(s)

Resolution

*NOTEPlease make sure a full database backup is taken before executing the given steps*
  1. Get the duplicated cookie value(s) from the ServerLog.bt9 file, look for "The duplicate key value is"  and copy the value found inside the parenthesis, e.g:
    The duplicate key value is (36affad1-0525-11ec-80c0-000c292e39fb-6daeef17).
  2. Stop the App Control Server service
  3. Open SQL Mgmt Studio and run the bellow script (update line 3 with the cookie value(s) from Step 1):
    USE das    
            GO
            SELECT host_id INTO #tmpDeleteHosts FROM dbo.hostmain (nolock) WHERE cookie = 'xxxxxxxx-xxxx-xxxx-xxxxx-xxxxxxxxxxx-xxxxxxxx' --update the cookie here
            
            UPDATE dbo.config_list SET obsolete=1 
            WHERE host_id IN 
            (
                SELECT host_id FROM #tmpDeleteHosts
            )
    
            DELETE FROM dbo.host_config_prop
            WHERE host_id IN 
            (
                SELECT host_id FROM #tmpDeleteHosts
            )
    
            DELETE FROM dbo.hostmain
            OUTPUT deleted.host_id, deleted.template_host_id, deleted.deleted, deleted.cookie, deleted.host_group_id, deleted.host_group_is_automatic, deleted.hostname, deleted.display_hostname, deleted.bit9_version_kernel_major, deleted.bit9_version_kernel_minor, deleted.bit9_version_kernel_point, deleted.bit9_version_kernel_build, deleted.bit9_version_kernel_date, deleted.bit9_version_kernel_debug, deleted.ip_address, deleted.operating_system_id, deleted.register_date, deleted.delete_date, deleted.uninstall_date, deleted.users, deleted.mac_address, deleted.description, deleted.os_description, deleted.last_host_group_id, deleted.last_host_group_date, deleted.bit9_version_major, deleted.bit9_version_minor, deleted.bit9_version_point, deleted.bit9_version_build, deleted.bit9_version_debug, deleted.bit9_version_date, deleted.password_seed, deleted.cli_code, deleted.upgrade_state, deleted.host_flags, deleted.refresh_flags, deleted.debug_level, deleted.kernel_debug_level, deleted.debug_duration, deleted.debug_flags, deleted.cc_level, deleted.cc_flags, deleted.comment, deleted.virtual_platform, deleted.virtualized, GETUTCDATE(), deleted.cb_sensor_version, deleted.cb_sensor_flags, deleted.cb_sensor_id
            INTO dbo.hostmain_bak
            WHERE host_id IN 
            (
                SELECT host_id FROM #tmpDeleteHosts
            )
    
            DELETE FROM dbo.host_state
            WHERE host_id IN 
            (
                SELECT host_id FROM #tmpDeleteHosts
            )
            
            DELETE FROM dbo.host_metadata
            WHERE host_id IN 
            (
                SELECT host_id FROM #tmpDeleteHosts
            )
    
            DELETE FROM dbo.duplicate_hosts
            WHERE host_id IN 
            (
                SELECT host_id FROM #tmpDeleteHosts
            )
    
            DELETE FROM dbo.created_template_lookup
            WHERE template_host_id IN
            (
                SELECT host_id FROM #tmpDeleteHosts
            )
            DROP TABLE #tmpDeleteHosts
  4. The following error could be displayed when deleting the cookies:
    Violation of UNIQUE KEY constraint 'UQ__hostmain__4E88F7AD186E4FA6'. Cannot insert duplicate key in object 'dbo.hostmain_bak'. The duplicate key value is (xxxxxxxx-xxxx-xxxx-xxxxx-xxxxxxxxxxx-xxxxxxxx).
    The statement has been terminated.
  • If this happens please run the following query:
    use das;
    delete dbo.hostmain_bak where host_id in
    (
    select b.host_id from dbo.hostmain_bak as b
    inner join dbo.hostmain a
    on b.cookie = a.cookie
    )
  1. Please execute the query from Step 2 again for each duplicate cookie
  2. Start the App Control Server service

Additional Notes

If the 'DailyPruneTask' fails with an error in the Reporter log that contains a message like the below, then please...
Msg 2627, Level 14, State 1, Procedure PruneDeletedHosts, Line 25 [Batch Start Line 2]
Violation of UNIQUE KEY constraint 'UQ__hostmain__4E88F7AD186E4FA6'. Cannot insert duplicate key in object 'dbo.hostmain_bak'. The duplicate key value is (xxxxxxxx-xxxx-xxxx-xxxxx-xxxxxxxxxxx-xxxxxxxx).
The statement has been terminated.
  • Stop the App Control Server Service
  • Run the query:
    use das;
    delete dbo.hostmain_bak where host_id in
    (
    select b.host_id from dbo.hostmain_bak as b
    inner join dbo.hostmain a
    on b.cookie = a.cookie
    )
  • Confirm there are no more messages seen by running: 
    use das; exec dbo.PruneDeletedHosts
  • Start the App Control Server


 

Related Content


Labels (1)
Was this article helpful? Yes No
100% helpful (1/1)
Article Information
Author:
Creation Date:
‎02-01-2022
Views:
1297
Contributors