Tips & Troubleshooting — SQL Server Auditor

Command-Line Arguments

DBLenseSQLAuditor.exe [-s server] [-u user] [-p password] [-e]

Service Management

The DBLenseAuditCollector Windows service runs continuously in the background. Here are common management tasks:

Check Service Status

sc query DBLenseAuditCollector

Start / Stop / Restart

sc start DBLenseAuditCollector
sc stop DBLenseAuditCollector

# Or using PowerShell:
Restart-Service DBLenseAuditCollector

View Service Logs

The service writes logs to the Windows Event Log under the Application source. Use Event Viewer or PowerShell:

Get-EventLog -LogName Application -Source "DBLenseAuditCollector" -Newest 20

Update Central DB Connection

Edit appsettings.json in the service install directory (default: C:\Program Files\DBLense\AuditCollector\appsettings.json):

{
  "CentralDb": {
    "ConnectionString": "Server=YOUR_SERVER;Database=DBLenseAuditDB;Integrated Security=true;TrustServerCertificate=true;",
    "DatabaseName": "DBLenseAuditDB"
  }
}

Restart the service after changing the configuration.

Uninstall Service

sc stop DBLenseAuditCollector
sc delete DBLenseAuditCollector

Troubleshooting

System Requirements

Central Database

Collector Service

UI Dashboard

Database Schema

The central database contains the following tables (created automatically by the setup wizard):

Frequently Asked Questions

Q: What exactly happens when I delete (remove) an instance from the Dashboard tab?

Deleting a registered instance triggers a coordinated cleanup across three layers. Here is exactly what happens in each component:

1. UI Dashboard

• A confirmation dialog appears: “Remove instance ‘SERVER_NAME’?”
• If you click Yes, the instance is immediately removed from the grid and deselected.

2. Central Database

Only the instance registration row is deleted from dbo.AuditRegisteredInstances. This removes the connection string, all audit configuration (tracked events, filters, session settings, retention policy), and runtime status for that instance.

Important: The following data is preserved and is NOT deleted:

This means you keep your full audit trail for compliance and forensic purposes even after removing the instance from active monitoring.

3. Collector Service

On the next collection cycle (typically within seconds), the collector service detects that the instance is no longer registered and performs the following cleanup:

• Connects to the target SQL Server instance
• If Persist XE Sessions was OFF (the default): the XEvent session (DBLenseAudit) is stopped and dropped (DROP EVENT SESSION) — completely removed from the monitored instance
• If Persist XE Sessions was ON: the XEvent session is stopped but left defined on the server (can be restarted manually if needed)
• If Dual Session mode was enabled, both sessions (primary and _B) are stopped/dropped
• The instance is removed from the collector’s in-memory tracking — no further collection cycles run for that server

4. On the Deleted SQL Server Instance

Tip: If you want to fully clean up the target instance after deletion, manually run the following on the monitored SQL Server:

-- Remove the security DDL trigger (if installed)
IF EXISTS (SELECT 1 FROM sys.server_triggers WHERE name = 'dbl_tr_security_audit')
    DROP TRIGGER [dbl_tr_security_audit] ON ALL SERVER;

-- Remove the security log table (if installed)
IF OBJECT_ID('master.dbo.DBLenseAuditSecurityLog') IS NOT NULL
    DROP TABLE master.dbo.DBLenseAuditSecurityLog;

-- Remove the Server Audit (if Compliance Pack was enabled)
-- See scripts/install-server-audit-onprem.sql UNINSTALL section

Q: Can I re-register an instance after deleting it?

Yes. Click Register Instance on the Dashboard tab and enter the same server connection details. A new registration is created with default configuration. The collector service will create a fresh XEvent session on the next cycle. Previously collected audit events for that server are still in the central database and will appear in search results alongside new events.

Q: How do I permanently delete historical audit events for a removed instance?

Historical events are retained by design for compliance. If you need to remove them (e.g., decommissioned server), run the following against the central database:

-- Delete all events for a specific server (use with caution)
DELETE FROM dbo.AuditEvents WHERE ServerName = 'YOUR_SERVER_NAME';

Warning: This operation is irreversible. If Ledger Tables are enabled, DELETE is blocked on the append-only AuditEventsLedger table by design — this is the tamper-proof guarantee.