1. Home
  2. Knowledge Base
  3. Security
  4. Auditing SQL Server 2019 with AVDF 20.4

Auditing SQL Server 2019 with AVDF 20.4

In this note we will see how we can add the MS SQL Server 2019 Adventure Works database to be a target in AVDF 20 Release 4.

Enable auditing for the HumanResources.EmployeePayHistory table in SQL Server Adventure Works database.

Graphical user interface, text, application

Description automatically generated
Graphical user interface

Description automatically generated with medium confidence
Graphical user interface, application

Description automatically generated
Graphical user interface, text, application, Word

Description automatically generated
Graphical user interface, table

Description automatically generated with medium confidence

Access the HumanResources.EmployeePayHistory table and note that the audit file with the extension *.sqlaudit has been written to.

Graphical user interface, text, table

Description automatically generated
Graphical user interface, text, application, email

Description automatically generated

Create the AVDF_USER account in SQL Server.

Graphical user interface, text, application

Description automatically generated

Download the AVDF 20.4 Agent and activate the agent.

Graphical user interface, application

Description automatically generated
Graphical user interface, text, application

Description automatically generated
Graphical user interface, application

Description automatically generated

Install the AVDF 20.4 Agent on the SQL Server Windows machine.

Graphical user interface, text, application, email

Description automatically generated
Text

Description automatically generated
Text

Description automatically generated
Text

Description automatically generated

Note: we can also configure the agent to run as a service in Windows. In this case since we have not, we need to keep this window open.

Agent is now running.

Graphical user interface, application

Description automatically generated

Execute the mssql_user_setup.sql script.

Create the Microsoft SQL Server target in AVDF.

Provide the SQL Server AVDF_USER account password.

A screenshot of a computer

Description automatically generated with medium confidence
Graphical user interface, application

Description automatically generated

Add the SQL Server Audit Trail.

Graphical user interface, application

Description automatically generated
Graphical user interface, text, application, email

Description automatically generated
Graphical user interface, text, application

Description automatically generated
Graphical user interface, text, application, email

Description automatically generated

Click on Save.

Graphical user interface, text, application

Description automatically generated

Execute SELECT statement on the HumanResources.EmployeePayHistory table – run this statement a few times.

Table

Description automatically generated

Connect as AVDF_AUD user and execute the Data Access report.

Note that we can view the activity on the audited SQL Server table in AVDF now.

Graphical user interface, text, application

Description automatically generated
Chart

Description automatically generated with low confidence
A picture containing text

Description automatically generated

From the dashboard, we can also view all activity on the SQL Server target as well.

Chart, waterfall chart

Description automatically generated
A picture containing graphical user interface

Description automatically generated
Updated on June 29, 2021

Was this article helpful?

Related Articles

Leave a Comment