Practical Database Auditing for Microsoft SQL Server and Azure SQL: Troubleshooting, Regulatory Compliance, and Governance

✍ Scribed by Josephine Bush

• Publisher: Apress
• Year: 2022
• Tongue: English
• Leaves: 312
• Category: Library

✦ Synopsis

Know how to track changes and key events in your SQL Server databases in support of application troubleshooting, regulatory compliance, and governance. This book shows how to use key features in SQL Server ,such as SQL Server Audit and Extended Events, to track schema changes, permission changes, and changes to your data. You’ll even learn how to track queries run against specific tables in a database.
Not all changes and events can be captured and tracked using SQL Server Audit and Extended Events, and the book goes beyond those features to also show what can be captured using common criteria compliance, change data capture, temporal tables, or querying the SQL Server log. You will learn how to audit just what you need to audit, and how to audit pretty much anything that happens on a SQL Server instance. This book will also help you set up cloud auditing with an emphasis on Azure SQL Database, Azure SQL Managed Instance, and AWS RDS SQL Server.

You don’t need expensive, third-party auditing tools to make auditing work for you, and to demonstrate and provide value back to your business. This book will help you set up an auditing solution that works for you and your needs. It shows how to collect the audit data that you need, centralize that data for easy reporting, and generate audit reports using built-in SQL Server functionality for use by your own team, developers, and organization’s auditors.


What You Will Learn

• Understand why auditing is important for troubleshooting, compliance, and governance
  
• Track changes and key events using SQL Server Audit and Extended Events
• Track SQL Server configuration changes for governance and troubleshooting
• Utilize change data capture and temporal tables to track data changes in SQL Server tables
• Centralize auditing data from all yourdatabases for easy querying and reporting
• Configure auditing on Azure SQL, Azure SQL Managed Instance, and AWS RDS SQL Server

Who This Book Is For
Database administrators who need to know what’s changing on their database servers, and those who are making the changes; database-savvy DevOps engineers and developers who are charged with troubleshooting processes and applications; developers and administrators who are responsible for generating reports in support of regulatory compliance reporting and auditing

✦ Table of Contents

Table of Contents
About the Author
About the Technical Reviewer
Acknowledgments
Introduction
Part I: Getting Started with Auditing
Chapter 1: Why Auditing Is Important
Why Should You Audit?
Types of Audits
Types of Regulatory Compliance
What Is Database Auditing?
Database Problems Auditing Can Solve
Chapter 2: Types of Database Auditing
SQL Server Audit
Extended Events
Tracking SQL Server Configuration Changes
Change Data Capture
Change Tracking
C2 Audit and Common Criteria Compliance
Temporal Tables
Successful and Failed Login Auditing
Auditing Azure SQL Databases
Auditing Azure SQL Managed Instance
Amazon Web Services and Google Cloud Auditing Options
Part II: Implementing Auditing
Chapter 3: What Is SQL Server Audit?
SQL Server Audit Availability
SQL Server Audit Requirements
SQL Server Audit Use Cases
Audit Categories
Audit Action Groups
Server Audit Action Groups
Database Audit Action Groups
SQL Server Audit Examples
Multiple Audit Setups
Chapter 4: Implementing SQL Server Audit via the GUI
Setting Up the Audit
Setting Up the Server Audit Specification
Setting Up the Database Audit Specification
Adding Multiple Audits
Querying Audit Logs
Columns Available in SQL Server Audit
Filtering SQL Server Audits
Deleting Audits
Disabling Audits
Modifying Audits
Chapter 5: Implementing SQL Server Audit via SQL Scripts
Scripting Existing Specifications
Setting Up the Audit
Setting Up the Server Audit Specification
Setting Up the Database Audit Specification
Querying System Views
Adding Multiple Audits
Columns Available in SQL Server Audit
Querying Audit Logs
Filtering SQL Server Audits
Deleting Audits
Disabling Audits
Modifying Audits
Chapter 6: What Is Extended Events?
Extended Events Default Sessions
Extended Event Components
Extended Events Templates
Extended Events Event Library
Extended Events Global Fields and Predicates
Extended Events Targets
Extended Events Advanced Settings
Extended Events Use Cases
Chapter 7: Implementing Extended Events via the GUI
Setting Up an Extended Event via the New Session Wizard Option
Setting Up an Extended Event via the New Session Option
Extended Event Files
Querying Extended Event Data
Modifying Extended Events
Stopping and Starting Extended Events
Deleting Extended Events
Chapter 8: Implementing Extended Events via SQL Scripts
Scripting Existing Extended Events
Setting Up an Extended Event
Querying System Tables and Views
Extended Event Files
Querying Extended Event Data
Modifying Extended Events
Stopping and Starting Extended Events
Deleting Extended Events
Chapter 9: Tracking SQL Server Configuration Changes
Configuration Changes History in SSMS
Querying Configuration Changes in the SQL Server Logs
Using SQL Server Audit to Capture Configuration Changes
Chapter 10: Additional SQL Server Auditing and Tracking Methods
Common Criteria Compliance
Change Tracking
Change Data Capture
Temporal Tables
Creating a Temporal Table
Modifying Data in a Temporal Table
Querying a Temporal Table
Successful and Failed Logins
SQL Server Audit for Successful and Failed Login Auditing
Extended Events for Successful and Failed Login Auditing
DDL Triggers
Part III: Centralizing and Reporting on Auditing Data
Chapter 11: Centralizing Audit Data
Setting Up Audits on Multiple Servers
Creating a Centralized Audit Database and User
Creating a Linked Server
SQL Agent Jobs to Collect and Clean Up Audit Data
Chapter 12: Create Reports from Audit Data
HTML Reports with SQL Server Agent
HTML Reports with PowerShell
Part IV: Cloud Auditing Options
Chapter 13: Auditing Azure SQL Databases
Auditing Azure SQL Database via the Portal
Enabling and Configuring Auditing
Viewing Audit Data
Modifying Azure SQL Database Auditing
Getting and Setting Your Auditing Policy
Auditing Azure SQL Database with Extended Events
Creating Storage Account and Container
Creating Database Credential
Creating Extended Event
Querying Extended Event
Centralizing and Reporting on Azure SQL Audit Data
Chapter 14: Auditing Azure SQL Managed Instance
Auditing Azure SQL Managed Instance with Diagnostic Settings
Enabling and Configuring Diagnostic Setting
Creating and Configuring SQL Server Audit
Querying Audit Data
Auditing Azure SQL Managed Instance with SQL Server Audit
Creating Storage Account and Container
Creating Database Credential
Creating SQL Server Audit
Querying SQL Server Audit Files
Auditing Azure SQL Managed Instance with Extended Events
Centralizing and Reporting on Azure SQL Managed Instance Audit Data
Chapter 15: Other Cloud Provider Auditing Options
AWS RDS SQL Server Audit
Creating an S3 Bucket
Creating an Option Group
Adding Auditing Option to New Option Group
Adding the New Option Group to RDS Instance
Setting Up SQL Server Audit
Querying SQL Server Audit Data
AWS RDS Extended Events
Auditing Google Cloud SQL Databases
Part V: Appendix
Appendix A: Database Auditing Options Comparison
Auditing Options
Pros and Cons of Auditing Choices
SQL Server Audit vs. Extended Events
Use Cases
Index