Securing Data on Microsoft SQL Server 2012

Monitoring User Activity

Auditing Basics

Access Controls

CSI SQL Server

Using SQL Server Tools

Overview of SQL Tools

SQL Server Configuration Manager (SSCM)

SQL Server Management Studio (SSMS)

After completing this module, students will be able to:

Understand the basic concepts of security and privacy on SQL Server.

Know the difference between security and privacy.

Understand the need for data auditing.

Recognize the correct tools for achieving all security tasks.

This module describes the security steps during and after installing SQL Server 2012. Lessons presented in this module will detail the security and service accounts during the installation. You will learn about the SQL Server Configuration Manager and working with Windows Firewall. You will also understand the password issues and policies for consumers of SQL resources.

Security Steps during Installation

Service Accounts

Types of Authentication

Administrator Account

Security Steps after Installation

Managing Services Using SSCM

Managing Server Network Protocols using SSCM

Managing Client Network Protocols using SSCM

Working with Windows Firewall

Connecting to a SQL Server Instance

SQL Server Resources Consumers

Types of SQL Server Consumers

Password Issues

Password Policy

Configuring TCP Port

Opening TCP port 1433 on Windows Firewall

Configuring Password Policy through SSMS

Configuring Password Policy through T-SQL Code

Testing the Password Policy

After completing this module, students will be able to:

Understand what are the service accounts.

Determine types of authentication.

Understand the risk of administrator accounts.

This module describes how to the authentication and authorization process in SQL Server works. Lessons presented in this module will detail how to authenticate and authorize users to access and use SQL data. You will also learn about server-side and database security.

Authentication

Process of Authentication

Windows Authentication

SQL Server Authentication

Database Authentication

Authorization

Process of Authorization

Mapping Login to User

Default Database Users

Accessing SQL Server Resources

Server-Side Security

Database Security

Schema Separation

Creating a New Windows Login

Creating a New Contained Database

Creating New Database Users

Applying Server-Side Policy

Applying Database Access Policy

After completing this module, students will be able to:

Understand how to use the authentication process.

Understand and use the authorization process.

Access SQL Server resources.

This module describes how to protect your data in SQL Server 2012. Lessons presented in this module will detail how cryptography works and the crypto features in SQL Server 2012. You will learn about security keys and how they various methods of data encryption used, such as TDE, Symmetric and Asymmetric encryption. You will also learn the difference between hashing and encryption.

Understanding Cryptography

What is Cryptography?

Types of Cryptography

SQL Server Cryptographic Features

Keys

What is a Key?

Service Master Key

Database Master Key

Database Encryption Keys

Transparent Data Encryption (TDE)

What is Transparent Data Encryption?

Transparent Data Encryption Architecture

Implementing Transparent Data Encryption

Symmetric Encryption

Symmetric Encryption Model

Creating Symmetric Keys

Implementing Symmetric Encryption

Asymmetric Encryption

Asymmetric Encryption Model

Creating Asymmetric Keys

Implementing Asymmetric Encryption

Hashing

What is Hashing?

Hashing vs. Encryption

Implementing SQL Server Hash Functions

Creating a New Database Master Key

Implementing Transparent Data Encryption

Implementing Symmetric Encryption

Implementing Asymmetric Encryption

Implementing Hash Functions

After completing this module, students will be able to:

Understand what is cryptography.

Know what cryptographic elements are.

Defining cryptography in SQL Server.

Recognize and use correct cryptographic SQL Server mechanisms.

This module describes the auditing process and how to use the various methods. Lessons presented in this module will detail the classic auditing methods, such as using triggers. You will learn how to configure SQL Server auditing and how to access the audit logs. You will also learn about database forensics and collecting digital evidence.

Classic Audit Methods

DDL Triggers

DML Triggers

SQL Server Profiler

SQL Server Auditing

SQL Server Audit Components

Configuring SQL Server Audit

Accessing SQL Server Audit Logs

Database Forensics

Digital Evidence

Methods for Collecting Data

Securing Digital Evidence

Implementing DML Trigger

Implementing Server-Side Auditing

Configuring Server-Side Auditing

Implementing Server-Side Auditing

Tampering Evidence

After completing this module, students will be able to:

Implement classic auditing on SQL Server.

Implement a new Audit feature in SQL Server 2012.

Understand the basics about database forensics.

This module describes the security threats and the countermeasures used to protect your data and database server. Lessons presented in this module will detail the weak points inside and outside of SQL Server 2012. You will also learn how to identify specific threats, such as data transfer sniffing and SQL code injection and how to prevent them with the appropriate countermeasures.

Threats from Authorized Users

User’s Role in Date Security and Privacy

Examples of Threats

Countermeasures

Physically Stealing Data

Weak Points Inside SQL Server

Weak Points Outside of SQL Server

Countermeasures

Data Transfer Sniffing

Client/Server Communications

Why is a Firewall not Enough?

Countermeasures

SQL Injection

What is SQL Injections?

Demonstration of SQL Injection

Countermeasures

Implementing Explicit DENY

Implementing Transparent Data Encryption

How to Protect SQL Injection on a User Table

After completing this module, students will be able to:

Identify threats from authorized users.

Understand the risk from physically stealing data.

Prevent data transfer sniffing.

Avoid SQL injection.