Peter A. Carter
Securing SQL ServerDBAs Defending the Database2nd ed.
Peter A. Carter
London, UK
ISBN 978-1-4842-4160-8e-ISBN 978-1-4842-4161-5
https://doi.org/10.1007/978-1-4842-4161-5
Library of Congress Control Number: 2018963673
© Peter A. Carter 2018
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.
Distributed to the book trade worldwide by Springer Science+Business Media New York, 233 Spring Street, 6th Floor, New York, NY 10013. Phone 1-800-SPRINGER, fax (201) 348-4505, e-mail [email protected], 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.

This book is for Danielle Carter–my favorite Disney villain

Introduction

With repeated, high-profile data security breaches hitting the headlines, security is moving increasingly to the forefront of the minds of data professionals.

SQL Server provides a broad and deep set of security features that allow you to reduce the attack surface of your SQL Server instance, with defense-in-depth and principles of least privilege strategies.

The attack surface of SQL Server refers to the set of features and windows services, which attackers can (and will) attempt to exploit to either steal data or reduce the availability of data and services.

Defense-in-depth is a strategy used across the IT industry, where multiple layers of security are put in place. The idea is that if one layer of security is breached, then another layer will stop the attacker in their tracks.

To fully protect data against attack, SQL Server DBAs, developers, and architects alike must all understand how and when to implement each of the security features that SQL Server offers. This book attempts to address these topics.

The first section of this book begins by looking at how to holistically model threats before deep-diving into each of SQL Server’s main areas of security, providing examples of how to implement each technology.

The second section of this book demonstrates some of the common threats that DBAs may face and how to guard against them. There is always an ethical question around revealing how attackers may try to penetrate your systems, but without knowledge and understanding of vulnerabilities that may be exploited, all too many DBAs do not implement the security measures that could easily avoid attacks from being successful. Every attack type discussed in this book is followed by a demonstration of how to use out-of-the-box SQL Server technologies to proactively stop the attacks occurring.

Many of the code examples in this book use the WideWorldImporters database. This database can be downloaded from github.com/Microsoft/sql-server-samples/releases/download/wide-world-importers-v1.0/WideWorldImporters-Full.bak

Some chapters also refer to CarterSecureSafe. This is a fictional company and product, which is purely designed to illustrate points made within this book.

Acknowledgments

I would like to thank Mark Burnett (xato.net) for allowing me to use his weak password list in this book.

I would also like to thank Ian Stirk, for a really good technical review, which has had a positive impact on the quality of this book.

Table of Contents

Part I: Database Security
Chapter 1:​ Threat Analysis and Compliance 3
Threat Modeling 4
Understanding Threat Modeling 4
Identifying Assets 6
Creating an Architecture Overview 6
Creating a Security Profile 9
Identifying Threats 11
Rating Threats 13
Creating Countermeasures 17
Compliance Considerations 18
Introducing SOX for SQL Server 19
Introducing GDPR For SQL Server 20
Summary 20
Chapter 2:​ SQL Server Security Model 23
Security Principal Hierarchy 24
Instance Level Security 26
Logins 27
Server Roles 35
Credentials 39
Database-Level Security 39
Users 39
Database Roles 47
Summary 49
Chapter 3:​ SQL Server Audit 51
Understanding SQL Server Audit 51
SQL Server Audit Actions and Action Groups 52
Implementing SQL Server Audit 64
Creating a Server Audit 64
Create a Server Audit Specification 70
Create a Database Audit Specification 71
Creating Custom Audit Events 74
Creating the Server Audit and Database Audit Specification 74
Raising the Event 75
Summary 76
Chapter 4:​ Data-Level Security 77
Schemas 77
Ownership Chaining 80
Impersonation 83
Row-Level Security 85
Security Predicates 86
Security Policies 87
Implementing RLS 87
Dynamic Data Masking 91
Summary 96
Chapter 5:​ Encryption in SQL Server 97
Generic Encryption Concepts 97
Defense in Depth 97
Symmetric Keys 98
Asymmetric Keys 98
Certificates 98
Self-Signed Certificates 98
Windows Data Protection API 98
SQL Server Encryption Concepts 99
Master Keys 99
EKM and Key Stores 102
SQL Server Encryption Hierarchy 102
Encrypting Data 103
Encrypting Data With a Password or Passphrase 103
Encrypting Data with Keys and Certificates 109
Transparent Data Encryption 114
Considerations for TDE With Other Technologies 115
Implementing TDE 116
Administering TDE 118
Always Encrypted 119
Implementing Always Encrypted 121
Summary 130
Chapter 6:​ Security Metadata 133
Security Principal Metadata 133
Finding a User’s Effective Permissions 134
Securable Metadata 137
Code Signing 138
Permissions Against a Specific Table 141
Audit Metadata 142
Encryption Metadata 145
Always Encrypted Metadata 145
TDE Metadata 148
Credentials Metadata 152
Securing Metadata 154
Risks of Metadata Visibility 156
Summary 157
Chapter 7:​ Implementing Service Accounts for Security 159
Service Account Types 159
Virtual Accounts 160
Managed Service Accounts 161
SQL Server Services 162
How Service Accounts Can Become Compromised 171
Designing a Pragmatic Service Account Strategy 172
Summary 174
Chapter 8:​ Protecting Credentials 175
Protecting the sa Account 175
DBA Steps to Mitigate the Risks 176
Protecting User Accounts 188
Auditing Passwords Susceptible to Word List Attacks 189
Protecting Windows Accounts 191
Summary 192
Chapter 9:​ Reducing the Attack Surface 193
Network Configuration 193
Understanding Ports and Protocols 193
Firewall Requirements for SQL Server 199
Miscellaneous Considerations 205
Ensuring that Unsafe Features Remain Disabled 206
Configuring the Surface Area Manually 207
Managing Features With Policy-Based Management 208
Summary 216
Part II: Threats and Countermeasures
Chapter 10:​ SQL Injection 221
Preparing the Environment 221
Performing SQL Injection Attacks 230
Spoofing a User Identity 230
Using SQL Injection to Leak Information 232
Destructive Attacks 236
Attacking the Network 238
Preventing SQL Injection Attacks 240
Using an Abstraction Layer 241
Access via Stored Procedures Only 242
Summary 244
Chapter 11:​ Hijacking an Instance 247
Hijacking an Instance 247
Protecting Against Hijacking 252
Understanding Logon Triggers 253
Using Logon Triggers to Prevent Instance Hijacking 254
Understanding Server Agent 260
Putting it all Together 273
Summary 274
Chapter 12:​ Database Backup Theft 275
Overview of Backups 275
Recovery Modes 275
Backup Types 279
Backup Media 280
Securing Backup Media 284
Physical Security 284
Encrypting Backups 285
Attempting to Steal an Encrypted Backup 295
Administrative Considerations for Encrypted Backups 302
Key Management 302
Backup Size 303
Summary 306
Chapter 13:​ Code Injection 307
Understanding Code Injection 307
Understanding EXECUTE AS 308
Using EXECUTE AS to Perform a Code Injection Attack 310
Protecting Against Code Injection 312
DevOps 313
Using Policy-Based Management to Protect Against Code Injection 313
Code Signing 321
Summary 324
Chapter 14:​ Whole Value Substitution Attacks 325
Understanding Whole Value Substitution Attacks 325
Salary Manipulation Example 326
Credit Card Fraud Example 329
Protecting Against Whole Value Substitution Attacks 332
Performance Considerations 335
Summary 339
Index 341

About the Author and About the Technical Reviewer

About the Author

Peter A. Carter
../images/395795_2_En_BookFrontmatter_Figb_HTML.jpg

is an SQL Server expert with over 15 years’ experience in database development, administration, and platform engineering. He is currently a consultant based in London. Peter has written several books across a variety of SQL Server topics, including security, high availability, automation, administration, and working with complex data types.

 

About the Technical Reviewer

Ian Stirk
../images/395795_2_En_BookFrontmatter_Figc_HTML.jpg

is a freelance SQL Server consultant based in London. In addition to his day job, he is an author, creator of software utilities, and technical reviewer who regularly writes book reviews for www.i-programmer.info .

He covers every aspect of SQL Server and has a specialist interest in performance and scalability. If you require help with your SQL Server systems, feel free to contact him at [email protected] or www.linkedin.com/in/ian-stirk-bb9a31 .

Ian would like to thank Peter Carter, Jonathan Gennick, and Jill Balzano for making this book experience easier for him.

None of us stands alone, and with this in mind, Ian would like to thank these special people: Kemi Amos, Malcolm Smith, John Lewis, Alan Crosby, Penny Newman, Tony Pugh, Stephen Cockburn, Jennifer Warner, John Woods, Tina Vick, Catherine Valentin, Stephen Johnson, Martin Fallon, Sizakele Phumzile Mtshali, Mark Hardman, Mark Northern, Ruhina Kabani, Peter Coombes, Lucy Mwangi, Silvia Alvarado, and Keila Fialho.

Ian’s fee for his work on this book has been donated to the GiveWell charities ( www.givewell.org/charities/top-charities/ ).

 
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset