Support
 
Support Get Quote
 
 
 
 
Database platforms

Understanding SQL Server Audit better

Read more
 
Previous article
Next article
Network devices

Critical Windows events: Event ID 6008 - Unexpected system shutdown

Read more
 
Microsoft SQL

How to use SQL Server tracing and profiler for auditing

Feb 10, 2022 3 min read
 
  • Microsoft SQL

What is SQL Trace?

SQL Trace is a feature in SQL Server to track and audit events occurring in your SQL instance. The events are recorded in a trace file which is by default stored in \MSSQL\LOG directory.

How to enable SQL Trace for auditing?

While creating a trace, you can specify the events you want to record by making changes to the Trace Properties dialogue box. Under the General tab of the dialogue box, you can specify information such as the trace name, the storage location of the trace file, trace stop time, etc.

  • In the Events Selection tab, you can select or unselect the events you want to capture in your trace.
  • Under the same tab, create filters by clicking on Column Filters.
  • The Edit Filter dialogue box appears.
  • You can enter the filter specifications using the provided comparison operators.
  • Save the changes.

Now, the traces will be collected by incorporating the filters in the specified storage location.

Viewing Trace files.

The trace files can be read using fn_trace_gettable function that displays the recorded events in a table format. The records can be aggregated and searched using SQL queries. The trace files are voluminous and manually writing queries to extract fields of interest for generating an audit trail can be a tedious process.

You can also use SQL Profiler to view trace files.

What is SQL Profiler?

SQL Profiler provides a Graphical User Interface (GUI) to access your trace files by storing, retrieving and auditing stored records. SQL Profiler's main functions are to monitor SQL Server activities, provide insights on the overall health and identify elements that affect the performance of your SQL instance.

SQL Profiler provides visibility into your trace files by allowing you to save trace records and compare it with successive records to troubleshoot any operational issue (viz. a slowly executing query) and fine tune them to enhance performance.

Why should you not rely on SQL Trace and SQL Profiler for auditing?

You can store records only up to a specified data limit (say 20 MB) in a single trace file. Trace files can't be retained for long time frames as they are rolled over. There is also a constraint on the maximum number of trace files that can be retained (say 5 files). Once the maximum is reached, the oldest trace file will be wiped off from the memory. This isn't favorable for auditing as you are expected to retain records over prolonged time periods. SQL Profiler can be used only when traces are configured for different activities individually. Analyzing the trace files using SQL Profiler manually is a time-consuming process.

Note:

Microsoft has deprecated SQL Trace and SQL Profiler along with Microsoft.SqlServer.Management.Trace namespace and the SQL Server Trace and Replay objects. The Trace and Profiler features can possibly be removed in the upcoming versions of SQL Server.

The best alternative to audit your SQL Server.

SQL Servers generate voluminous logs and they should be monitored closely to ensure security and compliance. Instead of relying on SQL Trace and SQL Profiler for auditing, you can use a log management solution that can effectively collect, parse and analyze logs from your SQL Server.

EventLog Analyzer is a holistic log management solution that collects SQL Server logs and analyzes them efficiently. It can produce intuitive and comprehensive reports for DDL, DML, Server account and security attack activities. Some of the audit reports produced by EventLog Analyzer include Column Modified, Delete Operations, Security Changes, Permissions Information, Last Login Time, Logins Information, Unauthorized copies of sensitive data, Account Lockouts, Storage media exposure, SQL Injection, and Denial of Service. Alerts can be configured for anomalous activities and you can be notified in real-time through email and SMS to mitigate threats and prevent attacks. Check out other reports provided by EventLog Analyzer.

You may also like

 

Interested in a
log management
solution?

Try EventLog Analyzer

 

 

Manage logs, comply with IT regulations, and mitigate security threats.

Seamlessly collect, monitor, and analyze
logs with EventLog Analyzer

Your request for a demo has been submitted successfully

Our support technicians will get back to you at the earliest.

  •  
  •  
By clicking 'Submit', you agree to processing of personal data according to the Privacy Policy.

  Zoho Corporation Pvt. Ltd. All rights reserved.

Link copied, now you can start sharing
Copy