PacktLib: Microsoft SQL Server 2012 Security Cookbook

Microsoft SQL Server 2012 Security Cookbook

Credits

About the Author

About the Reviewers

www.PacktPub.com

Preface

Securing Your Server and Network

Introduction

Choosing an account for running SQL Server

Managing service SIDs

Using a managed service account

Using a virtual service account

Encrypting the session with SSL

Configuring a firewall for SQL Server access

Disabling SQL Server Browser

Stopping unused services

Using Kerberos for authentication

Using extended protection to prevent authentication relay attacks

Using transparent database encryption

Securing linked server access

Configuring endpoint security

Limiting functionalities – xp_cmdshell and OPENROWSET

User Authentication, Authorization, and Security

Introduction

Choosing between Windows and SQL authentication

Creating logins

Protecting your server against brute-force attacks

Limiting administrative permissions of the SA account

Using fixed server roles

Giving granular server privileges

Creating and using user-defined server roles

Creating database users and mapping them to logins

Preventing logins and users to see metadata

Creating a contained database

Correcting user to login mapping errors on restored databases

Protecting the Data

Introduction

Understanding permissions

Assigning column-level permissions

Creating and using database roles

Creating and using application roles

Using schemas for security

Managing object ownership

Protecting data through views and stored procedures

Configuring cross-database security

Managing execution-plan visibility

Using EXECUTE AS to change the user context

Code and Data Encryption

Introduction

Using service and database master keys

Creating and using symmetric encryption keys

Creating and using asymmetric keys

Creating and using certificates

Encrypting data with symmetric keys

Encrypting data with asymmetric keys and certificates

Creating and storing hash values

Signing your data

Authenticating stored procedure by signature

Using module signatures to replace cross-database ownership chaining

Encrypting SQL code objects

Fighting Attacks and Injection

Introduction

Defining Code Access Security for .NET modules

Protecting SQL Server against Denial of Service

Protecting SQL Server against SQL injection

Securing dynamic SQL from injections

Using a SQL firewall or Web Application Firewall

Securing Tools and High Availability

Introduction

Choosing the right account for SQL Agent

Allowing users to create and run their own SQL Agent jobs

Creating SQL Agent proxies

Setting up transport security for Service Broker

Setting up dialog security for Service Broker

Securing replication

Securing SQL Server Database Mirroring and AlwaysOn

Auditing

Introduction

Using the profiler to audit SQL Server access

Using DML trigger for auditing data modification

Using DDL triggers for auditing structure modification

Configuring SQL Server auditing

Auditing and tracing user-configurable events

Configuring and using Common Criteria Compliance

Using System Center Advisor to analyze your instances

Using the SQL Server Best Practice Analyzer

Using Policy Based Management

Securing Business Intelligence

Introduction

Configuring Analysis Services access

Managing Analysis Services HTTP client authentication

Securing Analysis Services access to SQL Server

Using Role-Based Security in Analysis Services

Securing Reporting Services Server

Managing permissions in Reporting Services with roles

Defining access to data sources in reporting services

Managing Integration Services password encryption