Microsoft SQL Server 2012 Security Cookbook

Preface 1

Chapter 1: Securing Your Server and Network 7

Introduction 8

Choosing an account for running SQL Server 8

Managing service SIDs 13

Using a managed service account 15

Using a virtual service account 19

Encrypting the session with SSL 20

Configuring a firewall for SQL Server access 24

Disabling SQL Server Browser 27

Stopping unused services 31

Using Kerberos for authentication 32

Using extended protection to prevent authentication relay attacks 37

Using transparent database encryption 39

Securing linked server access 41

Configuring endpoint security 44

Limiting functionalities – xp_cmdshell and OPENROWSET 46

Chapter 2: User Authentication, Authorization, and Security 51

Introduction 51

Choosing between Windows and SQL authentication 52

Creating logins 53

Protecting your server against brute-force attacks 62

Limiting administrative permissions of the SA account 66

Using fixed server roles 68

Giving granular server privileges 70

Creating and using user-defined server roles 74

Creating database users and mapping them to logins 76

Preventing logins and users to see metadata 81

Creating a contained database 84

Correcting user to login mapping errors on restored databases 90

Chapter 3: Protecting the Data 93

Introduction 93

Understanding permissions 94

Assigning column-level permissions 102

Creating and using database roles 104

Creating and using application roles 109

Using schemas for security 111

Managing object ownership 116

Protecting data through views and stored procedures 118

Configuring cross-database security 121

Managing execution-plan visibility 123

Using EXECUTE AS to change the user context 124

Chapter 4: Code and Data Encryption 129

Introduction 129

Using service and database master keys 131

Creating and using symmetric encryption keys 135

Creating and using asymmetric keys 139

Creating and using certificates 141

Encrypting data with symmetric keys 146

Encrypting data with asymmetric keys and certificates 150

Creating and storing hash values 151

Signing your data 153

Authenticating stored procedure by signature 156

Using module signatures to replace cross-database ownership chaining 161

Encrypting SQL code objects 163

Chapter 5: Fighting Attacks and Injection 167

Introduction 167

Defining Code Access Security for .NET modules 168

Protecting SQL Server against Denial of Service 172

Protecting SQL Server against SQL injection 176

Securing dynamic SQL from injections 183

Using a SQL firewall or Web Application Firewall 187

Chapter 6: Securing Tools and High Availability 193

Introduction 193

Choosing the right account for SQL Agent 194

Allowing users to create and run their own SQL Agent jobs 196

Creating SQL Agent proxies 198

Setting up transport security for Service Broker 201

Setting up dialog security for Service Broker 208

Securing replication 212

Securing SQL Server Database Mirroring and AlwaysOn 216

Chapter 7: Auditing 221

Introduction 221

Using the profiler to audit SQL Server access 222

Using DML trigger for auditing data modification 230

Using DDL triggers for auditing structure modification 234

Configuring SQL Server auditing 238

Auditing and tracing user-configurable events 244

Configuring and using Common Criteria Compliance 247

Using System Center Advisor to analyze your instances 251

Using the SQL Server Best Practice Analyzer 253

Using Policy Based Management 255

Chapter 8: Securing Business Intelligence 261

Introduction 261

Configuring Analysis Services access 262

Managing Analysis Services HTTP client authentication 265

Securing Analysis Services access to SQL Server 271

Using Role-Based Security in Analysis Services 276

Securing Reporting Services Server 281

Managing permissions in Reporting Services with roles 285

Defining access to data sources in reporting services 288

Managing Integration Services password encryption 292

Index 297