SQL Server Security and Auditing

Audience

This course is for computer auditors and security specialists wishing to see how to close weaknesses on a SQL Server, and how to check this has been done. Audit planners and managers may also wish to attend.

Prerequisites

It is recommended that before attending this course students possess the following:

• An understanding of working in a Microsoft Windows operating system environment.
• An understanding of Relational Database Management Systems.
• Some programming experience in the SQL language would be helpful but not essential.
• Previous experience of auditing database systems would be helpful but not essential.

Duration

2 days. Hands on.

Course Objectives

This course is intended for computer auditors and security specialists who need to understand the mechanisms employed to secure a SQL Server installation, and how to successfully audit user access and activity following best practices and guidelines. The coverage of SQL Server security is quite in-depth, and the remaining topics focus on the major tools and techniques utilised for auditing. Albeit that the content and delivery is based on SQL Server 2008 R2, the material covered can be applied to previous releases if required, with the exception of some of the newer features such as Transparent Data Encryption. Besides excellent coverage of the theory and concepts, time is also devoted to hands-on activities to put into practice the topics introduced in each module. Upon successful completion of this course, students will be able to:

• Understand the fundamental structure and architecture of SQL Server.
• Work confidently in SQL Server Management Studio (SSMS).
• Understand the purpose of the system databases.
• Understand SQL Server database architecture and objects.
• Configure SQL Server security and audit related features.
• Manage access to a SQL Server.
• Understand and work with server principals and securables.
• Understand and work with database principals and securables.
• Implement permissions on securables.
• Understand encryption options in SQL Server.
• Secure code modules with signatures.
• Implement Transparent Data Encryption (TDE).
• Understand SQL Server Agent Security.
• Utilise DML Triggers for Auditing.
• Utilise SQL Server Profiler for Auditing.
• Use dedicated SQL Server auditing tools.
• Implement Policy Based Management.
• Retrieve security and audit related metadata.
• Implement best practices for auditing and compliance.

Course Content

An Introduction to SQL Server
Introduction to SQL Server Management Studio (SSMS)
System Databases
User Databases
Database Objects
Database and Log File Architecture
Filegroups
Configuring a Database
Configuring a SQL Server System
Feature Support Comparison in SQL Server

SQL Server Security
Overview of SQL Server Security
Service Account Security
Configuring Network Protocols and Endpoints
Configuring the SQL Server Surface Area
Server Level Principals and Securables
Database Level Principals and Securables
Authorisation through Permissions
Impersonation
Enhancing Security with Keys and Certificates
Signing Code Modules with Signatures
Encrypting Data
Transparent Data Encryption (TDE)

SQL Server Agent Security
SQL Server Agent Service Account Security
Managing SQL Server Agent Security

Auditing Techniques
Using DML Triggers for Auditing
Using DDL Triggers for Auditing
Using SQL Server Profiler for Auditing

Dedicated SQL Server Auditing Tools
SQL Server Audit Specifications
C2 Auditing

Policy Based Management
Evaluating and Enforcing Compliance with Policies
The Central Management Server

Security and Auditing Best Practices and Guidelines
Useful Scripts to Retrieve Security and Related Metadata
Useful Guidelines to Implementing Best Practices and Compliance
Other Considerations