Log On/Register  

855.838.5028

Implementing In-depth Security & Auditing for Microsoft SQL Server 2012-2016 Databases

Duration: 3 Days
Course Price: $1,795

Implementing In-depth Security & Auditing for Microsoft SQL Server 2012-2016 Databases

 

The purpose of this 3-day hands-on “Fast-Track to Data Security” course is to teach Database Administrators and Security personnel how to implement an in-depth database security profile and how to audit Microsoft SQL Server 2012 and 2016. Using hands-on lab based instruction, students will go through the entire process of securing and encrypting data, hardening the environment and auditing the data warehouse. This will to ensure all database use can be reconciled and the server is properly configured to current security best practices. Note: This course does not cover database hacking and hacking methodologies. Database intrusion detection and hacking Methodologies are covered in a separate “Fast-Track to Data Security” course.

 

Audience

This hands-on, lab based course is intended for people responsible for maintaining, managing or securing Microsoft SQL Server databases in all phases of development, test and production. The course is for those with an intermediate level of experience using Microsoft SQL Server, Microsoft Analysis Services or Microsoft Integration Services.

 

At Course Completion

After completing this course of training students will be able to:

  • Review Common Data Breach and Infiltration Methodologies
  • Securing Microsoft SQL server and the OLTP Data warehouse
  • How to Secure Microsoft SSAS and the Cube OLAP Data warehouse
  • How to separate network and data security tasks
  • Best Practices and hands on labs for SQL, SSAS and SSIS Services Accounts
  • Best Practices and hands on labs for Implementing Principals
  • Manage Logins, Users, and Schemas
  • Understand and Implement Permissions, Permission Hierarchies and Securables
  • Understand and Implement Password and Password Change Policies
  • Implement Server-Level Roles, Database-Level Roles and Application Roles
  • How to Implement the use of Credentials
  • Designing and Implementing a Test to Development to Production solution
  • Design Solutions to Separate Internal and External Database Connections
  • Implement Different Authentication Modes
  • Set the Metadata Visibility Configurations
  • Understand and Implement different Surface Area Configurations
  • Understand the use of the xp_cmdshell Server Configuration Options
  • The use of the TRUSTWORTHY Database Property
  • The use of PowerShell and its Role in Database Management and Security
  • Understand and use DML Triggers, Views and Stored Procedure Security
  • Understand the Encryption Hierarchy
  • How to Choose an Encryption Algorithm
  • Implementing “Always Encrypted” in SQL 2016
  • Enable Encrypted Connections to the Database Engine
  • Implementing Transparent Data Encryption in standard SQL Databases
  • Transparent Data Encryption with Azure SQL Database
  • Understanding Asymmetric Keys used from the Azure Key Vault
  • How to work with SQL Server and Database Encryption Keys
  • How to work with SQL Server Certificates and Asymmetric Keys
  • Understand and use Service Master Keys
  • How to Create, Backup and Restore the Service and Database Master Keys
  • How to Encrypt a Column of Data and Test for Success
  • Secure Data Across the wire with IPSec Encryption and Test for Success
  • Implementing basic HTTPS
  • Using Wireshark, Scan Ports and Capture Data Packets for Audit Reconciliation
  • Set c2 audit mode Server Configuration Options
  • Set Common Criteria compliance Options
  • Understand FIPS 140-2-compliant mode
  • Configure and use SQL Server Audit Action Groups and Actions
  • Administer Servers and Security Using Policy-Based Management
  • Understand and implement Policy Conditions and Facets
  • Best Practices and hands on labs for Implementing Security for backup and restore operations in both Standard and Contained Databases
  • Implement, Backup and Restore a Contained Database
  • Understand and Implement SSAS Cube Security
  • Understand and Implement SSIS Package Security
  • Understand and Implement SQL Agent Security
  • Understanding SSAS, SSIS and SSRS Project Security
  • Use SQL Profiler for SQL and MDX Captures

Implementing In-depth Security & Auditing for Microsoft SQL Server 2012-2016 Databases

 

The purpose of this 3-day hands-on “Fast-Track to Data Security” course is to teach Database Administrators and Security personnel how to implement an in-depth database security profile and how to audit Microsoft SQL Server 2012 and 2016. Using hands-on lab based instruction, students will go through the entire process of securing and encrypting data, hardening the environment and auditing the data warehouse. This will to ensure all database use can be reconciled and the server is properly configured to current security best practices. Note: This course does not cover database hacking and hacking methodologies. Database intrusion detection and hacking Methodologies are covered in a separate “Fast-Track to Data Security” course.

 

Audience

This hands-on, lab based course is intended for people responsible for maintaining, managing or securing Microsoft SQL Server databases in all phases of development, test and production. The course is for those with an intermediate level of experience using Microsoft SQL Server, Microsoft Analysis Services or Microsoft Integration Services.

 

At Course Completion

After completing this course of training students will be able to:

  • Review Common Data Breach and Infiltration Methodologies
  • Securing Microsoft SQL server and the OLTP Data warehouse
  • How to Secure Microsoft SSAS and the Cube OLAP Data warehouse
  • How to separate network and data security tasks
  • Best Practices and hands on labs for SQL, SSAS and SSIS Services Accounts
  • Best Practices and hands on labs for Implementing Principals
  • Manage Logins, Users, and Schemas
  • Understand and Implement Permissions, Permission Hierarchies and Securables
  • Understand and Implement Password and Password Change Policies
  • Implement Server-Level Roles, Database-Level Roles and Application Roles
  • How to Implement the use of Credentials
  • Designing and Implementing a Test to Development to Production solution
  • Design Solutions to Separate Internal and External Database Connections
  • Implement Different Authentication Modes
  • Set the Metadata Visibility Configurations
  • Understand and Implement different Surface Area Configurations
  • Understand the use of the xp_cmdshell Server Configuration Options
  • The use of the TRUSTWORTHY Database Property
  • The use of PowerShell and its Role in Database Management and Security
  • Understand and use DML Triggers, Views and Stored Procedure Security
  • Understand the Encryption Hierarchy
  • How to Choose an Encryption Algorithm
  • Implementing “Always Encrypted” in SQL 2016
  • Enable Encrypted Connections to the Database Engine
  • Implementing Transparent Data Encryption in standard SQL Databases
  • Transparent Data Encryption with Azure SQL Database
  • Understanding Asymmetric Keys used from the Azure Key Vault
  • How to work with SQL Server and Database Encryption Keys
  • How to work with SQL Server Certificates and Asymmetric Keys
  • Understand and use Service Master Keys
  • How to Create, Backup and Restore the Service and Database Master Keys
  • How to Encrypt a Column of Data and Test for Success
  • Secure Data Across the wire with IPSec Encryption and Test for Success
  • Implementing basic HTTPS
  • Using Wireshark, Scan Ports and Capture Data Packets for Audit Reconciliation
  • Set c2 audit mode Server Configuration Options
  • Set Common Criteria compliance Options
  • Understand FIPS 140-2-compliant mode
  • Configure and use SQL Server Audit Action Groups and Actions
  • Administer Servers and Security Using Policy-Based Management
  • Understand and implement Policy Conditions and Facets
  • Best Practices and hands on labs for Implementing Security for backup and restore operations in both Standard and Contained Databases
  • Implement, Backup and Restore a Contained Database
  • Understand and Implement SSAS Cube Security
  • Understand and Implement SSIS Package Security
  • Understand and Implement SQL Agent Security
  • Understanding SSAS, SSIS and SSRS Project Security
  • Use SQL Profiler for SQL and MDX Captures

Module 1: Data Security Overview

In this module, students will cover the core concepts and principles of Data and database Security. They will learn the terminology associated with a data security infrastructure along with identifying the business requirements needed to secure their data. Students will also get an overview of industry security audit regulations and best practices relating to PCI, SOX and other security standards.

 

Lessons

  • Review Common Data Breach and Infiltration Methodologies
  • The Differences between Securing SQL Server and Securing the Data Warehouse
  • Security concerns for OLTP Verses OLAP Databases
  • Separating network and data security tasks
  • Physical Security
  • Stakeholders in data security

 

Labs for Lesson 1: What is Microsoft Business Intelligence

  • Identify Key Components of a SQL Server Data Infrastructure
  • Identify Key Components of a SQL Server Business Intelligence  Infrastructure

 

 

 

 

Module 2: Identity, Access Control and Secure operations

In this module, students will start by creating new SQL users, logins and schemas. They will then continue their hands-on labs to see how the SQL Server Identity and Access infrastructure works at a granular level to allow or restrict data access and use. Finally, they will implement a secure environment that will match the case study requirements for principles, passwords and permissions.

 

Lessons

  • Implementing Principals
  • Managing Logins, Users, and Schemas
  • Understanding Permissions
  • Understanding the Permissions Hierarchy
  • Understanding Permissions and Securables
  • Understand Password Policies
  • Using Strong Passwords
  • Server-Level Roles
  • Database-Level Roles
  • Application Roles
  • Credentials
  • Implementing a test to live solution

 

Labs for Lesson2: Identity, Access Control and Secure Operations

  • Test Permissions and Securables
  • Setting a Password and Password Change Policy
  • Create and Test Active Directory Logins
  • Create a Database User
  • Create and test a Database Schema
  • Joining Roles
  • Grant Permissions to a Principal
  • Database-Level Roles
  • Create an Application Role
  • Create a Credential

 

 

Module 3: Implementing a Secure SQL Server 2016 Deployment

In this module, students will configure services and options to ensure a secure environment. They will cover each items listed to see how changing, enabling and disabling options will affect the live environment. They will also write and test DML Triggers to see how security can be enhanced with their use.

 

Lessons

  • What Authentication Mode can be used
  • Understanding Metadata Visibility Configuration
  • Surface Area Configuration
  • xp_cmdshell Server Configuration Options
  • TRUSTWORTHY Database Property
  • Using SQL Server PowerShell
  • Managing Authentication in Database Engine
  • Understanding DML Triggers and Trigger Security

 

 

Labs for Lesson 3: Implementing a Secure SQL Server 2016 Deployment

  • Choosing an Authentication Mode
  • Set Metadata Visibility Configuration
  • Configure SQL Server Surface Area
  • Set xp_cmdshell Server Configuration Option
  • Setting the TRUSTWORTHY Database Property
  • Using SQL Server PowerShell
  • Install and test PowerShell Script
  • Import the SQLPS Module
  • Run Windows PowerShell from SQL Server Management Studio
  • SQL Server Identifiers in PowerShell
  • Manage Authentication in Database Engine
  • Implement and test an AFTER Trigger
  • INSTEAD OF Triggers
  • Implement and test an INSTEAD OF Trigger
  • Manage Trigger Security
  • Manage View and Stored Procedure Security and Encryption

 

 

Module 4: SQL Server Data and Wire Encryption

In this module, students will learn how to encrypt and secure their data both in the database and on the wire. Using hands-on labs, they will create and destroy keys, encrypt data and perform backup and restore operations on the same encrypted databases to see how the environment is affected when encryption is used.

 

Lessons

  • Encryption Hierarchy
  • Choosing an Encryption Algorithm
  • Transparent Data Encryption (TDE)
  • SQL Server and Database Encryption Keys
  • SQL Server Certificates and Asymmetric Keys
  • Service Master Key
  • Extensible Key Management (EKM)
  • Creating, Backing up and Restoring the Service Master Key
  • Creating, Backing up and Restoring a Database Master Key
  • Transparent Data Encryption Using EKM
  • Encrypt a Column of Data
  • Across the wire Encryption
  • Implementing basic HTTPS
  • Implementing IPSec Encryption
  • Implementing “Always Encrypted” in SQL 2016
  • Scanning Ports and Data Packet Captures

 

 

 

 

Labs for Lesson 4: SQL Server Data and Wire Encryption

  • Choose an Encryption Algorithm
  • Implement Transparent Data Encryption (TDE)
  • Moving a TDE Protected Database to Another SQL Server
  • Creating SQL Server and Database Encryption Keys
  • Creating SQL Server Certificates and Asymmetric Keys
  • Service Master Key
  • Extensible Key Management (EKM)
  • Back Up the Service Master Key
  • Restore the Service Master Key
  • Create a Database Master Key
  • Back Up a Database Master Key
  • Restore a Database Master Key
  • Enabling TDE Using EKM
  • Encrypting, viewing and decrypting a Column of Data
  • Implement basic HTTPS
  • Implement and testing IPSec Encryption
  • Scan Ports and Data Packet Captures

 

 

Module 5: Implementing SQL Server Auditing and Policy-based Security Management

In this module, students will learn how to Implement and test SQL Server Auditing and Policy-based Management. Students will cover all facets of auditing and policy management, from identifying what needs to be audited to what policy-based configurations meet the best practices for their secure environments.

 

Lessons

  • c2 audit mode Server Configuration Options
  • Common Criteria Compliance enabled Options
  • FIPS 140-2-compliant mode
  • SQL Server Audit Action Groups and Actions
  • Administer Servers and Security Using Policy-Based Management

 

Labs for Lesson5: Implementing SQL Server Auditing and Policy-based Security Management

  • Set and test c2 audit mode Server Configuration Options
  • Set and test Common Criteria compliance enabled Option
  • Set FIPS 140-2-compliant mode
  • Create a Server Audit and Server Audit Specification
  • Create a Server Audit and Database Audit Specification
  • View and understanding the SQL Server Audit Log
  • Writing SQL Server Audit Events to the Security Log
  • Create a Management Policy, Implement Policy Conditions and Facets
  • Create a New Policy-Based Management Condition
  • View or Modify the Properties of a Policy-Based Management Policy
  • View or Modify the Properties of a Policy-Based Management Condition
  • Delete a Policy-Based Management Policy
  • Delete a Policy-Based Management Condition
  • Evaluate a Policy-Based Management Policy from an Object
  • Evaluate a Policy-Based Management Policy on a Schedule
  • Import a pre-defined Policy

 

 

Module 6: Implementing Contained Databases

In this module, students will cover Contained Databases and their use in secure environments. Students will implement a Contained Database, backup and restore the database and see how security is impacted when using this configuration.

 

Lessons

  • Changes between Standard and Contained Database
  • Security Best Practices with Contained Databases
  • Security and Migrating to a Partially Contained Database
  • Implement a Contained Database

 

Labs for Lesson 6: Implementing Contained Databases

  • Implement a Contained Database
  • Backup a Standard and Contained Database
  • Restore a Standard and Contained Database
  • Identify Orphaned Users and remediation for Orphans in a Contained Database

 

 

Module 7: Implementing SSAS Cube and SSIS Package Security

In this module, students will implement and test SSAS Cube Security using Roles and custom MDX for Cell security. They will also cover SSIS Package Security and SQL Agent and Agent Job security when used to run jobs and process cubes. Additionally, students will discuss options and the use of custom service accounts for different security environments and securing SSAS, SSIS and SSRS BI Projects.

 

Lessons

  • SQL Server Analysis Services (SSAS) Cube and OLAP Data Warehouse Security
  • SSIS Package Security
  • SSIS Package Storage Options and their Security Risks
  • SQL Agent Security
  • Understanding SSAS, SSIS and SSRS Project Security
  • Using SQL Profiler for SQL and MDX Captures

 

Labs for Lesson 7: Implementing SSAS Cube and SSIS Package Security

  • Implement and Test SSAS Role Security
  • Implement and Test SSAS Cell Security
  • Implement and Test SQL Agent Security
  • Implement and Test SSIS Package Stored and Encrypted Credentials
  • Understanding SSAS, SSIS and SSRS Project Security
  • Using SQL Profiler for SQL and MDX Captures

 

Learn More
Please type the letters below so we know you are not a robot (upper or lower case):