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

Practical DatabaseAuditing for MicrosoftSQL Server andAzure SQLTroubleshooting, RegulatoryCompliance, and GovernanceJosephine BushPractical Database Auditing for Microsoft SQL Server and Azure SQL:Troubleshooting, Regulatory Compliance, and Governance Josephine Bush Boulder, CO, USA ISBN-13 (pbk): 978-1-4842-8633-3 ISBN-13 (electronic): 978-1-4842-8634-0 https://doi.org/10.1007/978-1-4842-8634-0 Copyright 2022 by Josephine Bush This work is subject to copyright. All rights are reserved by the Publisher, whether the whole or part of the material is concerned, specifically the rights of translation, reprinting, reuse of illustrations, recitation, broadcasting, reproduction on microfilms or in any other physical way, and transmission or information storage and retrieval, electronic adaptation, computer software, or by similar or dissimilar methodology now known or hereafter developed. Trademarked names, logos, and images may appear in this book. Rather than use a trademark symbol with every occurrence of a trademarked name, logo, or image we use the names, logos, and images only in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the trademark. The use in this publication of trade names, trademarks, service marks, and similar terms, even if they are not identified as such, is not to be taken as an expression of opinion as to whether or not they are subject to proprietary rights. While the advice and information in this book are believed to be true and accurate at the date of publication, neither the authors nor the editors nor the publisher can accept any legal responsibility for any errors or omissions that may be made.

The publisher makes no warranty, express or implied, with respect to the material contained herein. Managing Director, Apress Media LLC: Welmoed Spahr Acquisitions Editor: Jonathan Gennick Development Editor: Laura Berendson Coordinating Editor: Jill Balzano Cover photo by Clark Van Der Beken on Unsplash Distributed to the book trade worldwide by Springer Science+Business Media LLC, 1 New York Plaza, Suite 4600, New York, NY 10004. Phone 1-800-SPRINGER, fax (201) 348-4505, e-mail orders-ny@springer-sbm. com, or visit www.springeronline.com. Apress Media, LLC is a California LLC and the sole member (owner) is Springer Science + Business Media Finance Inc (SSBM Finance Inc). SSBM Finance Inc is a Delaware corporation.

For information on translations, please e-mail booktranslations@springernature.com; for reprint, paperback, or audio rights, please e-mail bookpermissions@springernature.com. Apress titles may be purchased in bulk for academic, corporate, or promotional use. eBook versions and licenses are also available for most titles. For more information, reference our Print and eBook Bulk Sales web page at http://www.apress.com/bulk-sales. Any source code or other supplementary material referenced by the author in this book is available to readers on GitHub (https://github.com/Apress). apress.com/source-code. apress.com/source-code.

Printed on acid-free paper For my husband, Jim, who somehow managed to be nothing butsupportive about a book that would put him to sleep.Table of Contents About the Author xi About the Technical Reviewer xiii Acknowledgments xv Introduction xvii Part I: Getting Started with Auditing 1 Chapter 1: Why Auditing Is Important 3 Why Should You Audit? 3 Types of Audits 4 Types of Regulatory Compliance 5 What Is Database Auditing? 6 Database Problems Auditing Can Solve 6 Chapter 2: Types of Database Auditing 9 SQL Server Audit 9 Extended Events 10 Tracking SQL Server Configuration Changes 11 Change Data Capture 13 Change Tracking 14 C2 Audit and Common Criteria Compliance 15 Temporal Tables 16 Successful and Failed Login Auditing 17 Auditing Azure SQL Databases 18 Auditing Azure SQL Managed Instance 19 Amazon Web Services and Google Cloud Auditing Options 20 v Table of ConTenTs Part II: Implementing Auditing 21 Chapter 3: What Is SQL Server Audit? 23 SQL Server Audit Availability 23 SQL Server Audit Requirements 24 SQL Server Audit Use Cases 26 Audit Categories 26 Audit Action Groups 27 Server Audit Action Groups 27 Database Audit Action Groups 28 SQL Server Audit Examples 30 Multiple Audit Setups 34 Chapter 4: Implementing SQL Server Audit via the GUI 37 Setting Up the Audit 37 Setting Up the Server Audit Specification 44 Setting Up the Database Audit Specification 46 Adding Multiple Audits 51 Querying Audit Logs 52 Columns Available in SQL Server Audit 54 Filtering SQL Server Audits 55 Deleting Audits 57 Disabling Audits 60 Modifying Audits 62 Chapter 5: Implementing SQL Server Audit via SQL Scripts 65 Scripting Existing Specifications 65 Setting Up the Audit 66 Setting Up the Server Audit Specification 71 Setting Up the Database Audit Specification 72 Querying System Views 76 Adding Multiple Audits 79 vi Table of ConTenTs Columns Available in SQL Server Audit 79 Querying Audit Logs 81 Filtering SQL Server Audits 82 Deleting Audits 83 Disabling Audits 86 Modifying Audits 87 Chapter 6: What Is Extended Events? 89 Extended Events Default Sessions 89 Extended Event Components 90 Extended Events Templates 90 Extended Events Event Library 93 Extended Events Global Fields and Predicates 95 Extended Events Targets 97 Extended Events Advanced Settings 99 Extended Events Use Cases 101 Chapter 7: Implementing Extended Events via the GUI 103 Setting Up an Extended Event via the New Session Wizard Option 103 Setting Up an Extended Event via the New Session Option 119 Extended Event Files 125 Querying Extended Event Data 125 Modifying Extended Events 131 Stopping and Starting Extended Events 134 Deleting Extended Events 135 Chapter 8: Implementing Extended Events via SQL Scripts 137 Scripting Existing Extended Events 137 Setting Up an Extended Event 138 Querying System Tables and Views 141 Extended Event Files 146 Querying Extended Event Data 147 Modifying Extended Events 148 vii Table of ConTenTs Stopping and Starting Extended Events 150 Deleting Extended Events 150 Chapter 9: Tracking SQL Server Configuration Changes 151 Configuration Changes History in SSMS 151 Querying Configuration Changes in the SQL Server Logs 153 Using SQL Server Audit to Capture Configuration Changes 155 Chapter 10: Additional SQL Server Auditing and Tracking Methods 161 Common Criteria Compliance 161 Change Tracking 164 Change Data Capture 168 Temporal Tables 172 Creating a Temporal Table 173 Modifying Data in a Temporal Table 174 Querying a Temporal Table 175 Successful and Failed Logins 176 SQL Server Audit for Successful and Failed Login Auditing 177 Extended Events for Successful and Failed Login Auditing 178 DDL Triggers 180 Part III: Centralizing and Reporting on Auditing Data 181 Chapter 11: Centralizing Audit Data 183 Setting Up Audits on Multiple Servers 183 Creating a Centralized Audit Database and User 188 Creating a Linked Server 189 SQL Agent Jobs to Collect and Clean Up Audit Data 190 Chapter 12: Create Reports from Audit Data 197 HTML Reports with SQL Server Agent 197 HTML Reports with PowerShell 205 viii Table of ConTenTs Part IV: Cloud Auditing Options 211 Chapter 13: Auditing Azure SQL Databases 213 Auditing Azure SQL Database via the Portal 213 Enabling and Configuring Auditing 214 Viewing Audit Data 219 Modifying Azure SQL Database Auditing 224 Getting and Setting Your Auditing Policy 226 Auditing Azure SQL Database with Extended Events 229 Creating Storage Account and Container 230 Creating Database Credential 236 Creating Extended Event 237 Querying Extended Event 238 Centralizing and Reporting on Azure SQL Audit Data 241 Chapter 14: Auditing Azure SQL Managed Instance 245 Auditing Azure SQL Managed Instance with Diagnostic Settings 245 Enabling and Configuring Diagnostic Setting 246 Creating and Configuring SQL Server Audit 247 Querying Audit Data 248 Auditing Azure SQL Managed Instance with SQL Server Audit 251 Creating Storage Account and Container 252 Creating Database Credential 257 Creating SQL Server Audit 258 Querying SQL Server Audit Files 258 Auditing Azure SQL Managed Instance with Extended Events 261 Centralizing and Reporting on Azure SQL Managed Instance Audit Data 265 Chapter 15: Other Cloud Provider Auditing Options 269 AWS RDS SQL Server Audit 269 Creating an S3 Bucket 269 Creating an Option Group 272 Adding Auditing Option to New Option Group 275 ix Table of ConTenTs Adding the New Option Group to RDS Instance 279 Setting Up SQL Server Audit 281 Querying SQL Server Audit Data 283 AWS RDS Extended Events 286 Auditing Google Cloud SQL Databases 288 Part V: Appendix 291 Appendix A: Database Auditing Options Comparison 293 Auditing Options 293 Pros and Cons of Auditing Choices 295 SQL Server Audit vs Extended Events 296 Use Cases 298 Index 303 x