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.
Access the HumanResources.EmployeePayHistory table and note that the audit file with the extension *.sqlaudit has been written to.
Create the AVDF_USER account in SQL Server.
Download the AVDF 20.4 Agent and activate the agent.
Install the AVDF 20.4 Agent on the SQL Server Windows machine.
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.
Execute the mssql_user_setup.sql script.
Create the Microsoft SQL Server target in AVDF.
Provide the SQL Server AVDF_USER account password.
Add the SQL Server Audit Trail.
Click on Save.
Execute SELECT statement on the HumanResources.EmployeePayHistory table – run this statement a few times.
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.
From the dashboard, we can also view all activity on the SQL Server target as well.