Support
 
Support Get Quote
 
 
 
 
Network devices

Critical Windows events: Event ID 7031 - Service crash

Read more
 
Previous articles
Next articles
Network devices

How to check your Cisco switch logs?

Read more
 
Microsoft SQL

Understanding SQL Server Audit better

Feb 10, 2022 6 min read
 
  • Microsoft SQL

Digitalization simplified access to the data organizations store for their users. However, protecting confidential data from hackers has become complicated. According to the FBI's Internet Crime Report 2021, complaints of cybercrimes have drastically increased by almost 300% in the last five years. The sudden surge in cybercrime is due to remote work and cloud adoption, which have allowed hackers to leverage misconfigurations and unsecured databases to steal data. In most enterprises, customers' confidential data is stored in the databases of Microsoft SQL Server, which holds a market share of 19.15% in the database management system category—approximately 180,106 companies.

IT security professionals need to monitor and audit all the activities in SQL Server regularly to ensure that the integrity and confidentiality of critical data are not compromised. Manually monitoring all these activities on a server is a hectic process. To make your job a little easier, Microsoft has provided SQL Server Audit features to help you audit logs efficiently. SQL Server Audit will also help you comply with the data protection policies of the GDPR, HIPAA, SOX, and the PCI DSS.

What is SQL Server auditing?

SQL Server auditing involves tracking and reviewing all activities happening on SQL servers to detect potential threats and vulnerabilities. Auditable actions in SQL Server are grouped as an audit action group. The server-level audit action groups, such as AUDIT_CHANGE_GROUP and APPLICATION_ROLE_CHANGE_PASSWORD_GROUP, are grouped into server audit specifications. The database-level audit action group events, such as DATABASE_OPERATION_GROUP and DATABASE_LOGON_GROUP, are grouped into database audit specifications. The result of the SQL Server Audit report is stored in event logs or files.

In simple terms, SQL Server Audit allows you to monitor and record every change to the server settings. Additionally, you can easily keep track of all server activities, like who modified what values, in a specific table in the database. You can identify unauthorized access to the network by analyzing the suspicious log events.

Auditing types

There are two types of SQL Server auditing:

  • Server-level auditing
  • Database-level auditing

SQL Server auditing features

In SQL Server auditing, define what to audit, make server configuration and schema changes, and audit data modifications. Here's a list of some of the SQL Server auditing features:

  • C2 auditing
  • Common Criteria compliance
  • Login auditing
  • SQL Server auditing
  • SQL Trace
  • Extended Events
  • Change data capture
  • DML, DDL, and login triggers

Choose any one or a combination of the above features, based on your organization's needs. Among these, C2 auditing and Common Criteria compliance are the most widely used international standards for SQL auditing. C2 auditing records information beyond SQL servers, like who triggered the event in which database, the server name, the event type, and the outcome of the event. Common Criteria compliance enables residual information protection, the ability to view login statistics, and the table-level DENY to take precedence over the column-level GRANT.

Login auditing monitors the SQL Server login activities and writes both failed and successful logins to an error log. SQL Trace is an event-driven monitoring tool that captures user activity. Change data capture records all the insert, update, and delete activities in the server table. DML, DDL, and login triggers are used for auditing and regulating database operations.

How to perform SQL Server auditing

Components of SQL Server auditing

  • SQL Server Audit object
  • Audit specifications
  • Target

An SQL Server Audit object is an object created in the audited SQL Server instance. Define the target for monitoring events at the server and database levels. You can define multiple audits for a single SQL Server instance, and each audit stores audit information in a separate target file. The destination type (binary file, security protocol, or application protocol) and path are defined. The audit does not specify what was recorded. No event type, object, or database is specified here. Now, let us create an SQL Server Audit event.

Creating an SQL Server audit

  • The first step is to create an audit and define the target. There are two methods of creating an SQL Server audit.
    1. Using SQL Server Management Studio (GUI features): In the Object Explorer panel, expand the Security folder, right-click Audits, and select New Audit.
    2. Using Transact-SQL commands
      CREATE SERVER AUDIT [ZohoAudit_DDL_Access] TO FILE
      (
      FILEPATH = N'C:\TestAudits\'
      ,MAXSIZE = 10 MB
      )
      WITH
      (
      QUEUE_DELAY = 1000
      ,ON_FAILURE = CONTINUE
      )
      ALTER SERVER AUDIT [ZohoAudit_DDL_Access]WITH (STATE = ON)
      GO
  • Choose either of the two audit specifications: server audit specification or database audit specification.
  • Enable the audit specification.
  • Read the audit events by using Windows Event Viewer and Log File Viewer.

To create an SQL Server Audit object

  • Open SQL Server Management Studio.
  • Click Create Audit to create an SQL Server audit, which will open the Create Audit dialog box.
  • Pr ovide a name for your audit in the Audit name field.
  • Choose the Audit destination (preferably File ).
  • Choose the File path for the audit.
  • Set the Maximum file size to 1000 (in MB, GB, TB, or Unlimited).
  • Click the check box for Reserve disk space.
image showing how to create an SQL Server audit

To view audit logs

  • Open SQL Server Management Studio.
  • In the Object Explorer panel, expand the Security folder.
  • Right-click the audit object in the Security folder to view the audit log report.

SQL Server Audit provides a built-in solution called Log File Viewer to make it easier for the user to customize and filter out log entries. By default, all SQL Server files are stored in SQLAUDIT format, which is hard to read. Despite that, you can use the LOG file format feature to save and export the audit report.

The best way to mitigate the risk of data leaks is to audit your SQL Server database proactively. Apart from monitoring, auditing, notifying about, and reporting on the critical changes made on the server, SQL Server Audit also gives a comprehensive view of SQL Server security insights. Thus, IT administrators can take appropriate action to enhance their enterprise-wide network data security through the powerful features provided by SQL Server Audit. We hope this article enlightens you about all the information needed to perform an SQL Server audit easily and safeguard your business-critical information.

You may also like

 

Interested in a
log management
solution?

Try EventLog Analyzer
Link copied, now you can start sharing
Copy

 

 

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