Retain a complete SQL Server audit trail to quickly investigate database security incidents

Many organizations choose SQL Server to store sensitive data that they need to ensure is both protected and available. To stay on top of database and user activity and control server permissions, you need to enable SQL Server auditing and monitor what’s going on. But you also need to retain your audit trail in SQL Server by logging events to SQL Server audit tables designed to store audit data, so you can analyze events, investigate aberrant activity and respond to auditors’ requests.

Maintaining your audit trail in SQL server with native tools

A record of SQL Server audit logs is kept in SQL Server audit tables. Using SQL Server Management Studio, you can go through the logs in those tables and figure out what changes happened across your SQL Server — but you need to be fluent in Transact-SQL. Another option is SQL Server Profiler. This Microsoft log file viewer can’t show data stored in audit tables, but it can deliver insight about what’s going on across your SQL Server in real time. Whichever tool you choose, keep in mind that the amount of audit data you can store on your SQL Server database and your ability to find particular information quickly depends on the disk space capacity of your SQL Server. As your log grows with time, the performance of your SQL Server can suffer, which makes maintaining SQL Server event logs an ongoing challenge.

Retaining SQL Server audit and event logs with Netwrix Auditor

Using SQL Server Management Studio you can stay on top of SQL Server event logs. For example, you can create triggers, manage database objects (DBOs), make your own schemas and table names, and define database audit specification objects. Aside from this, you can also use native tools to write SQL Server audit access events to the Windows Security log. However, none of these tools deliver any reports on audit events that can be available quickly, so many organizations find they need to invest in a separate reporting tool.

Netwrix Auditor for SQL Server enables you to easily archive your SQL Server audit trail and browse through multiple SQL Server audit tables by delivering cost-effective two-tiered storage (SQL database + file-based). The storage keeps your consolidated SQL Server audit logs for more than 10 years and ensures quick and easy access to the data throughout the whole retention period. The first tier, a SQL repository, is used for reporting purposes, and the second tier is used to back up the archived events. There’s no need to purchase another solution to facilitate reporting, because Netwrix Auditor’s storage provides both access and long-term capabilities at no additional cost.

Audit Archive from Netwrix Auditor — Location and retention settings: where to write audit data and how long to keep audit data