Auditing Microsoft SQL Server

SQL Server is Microsoft’s corporate and enterprise database system. It is used extensively to provide the data storage and retrieval capabilities for office intranets, business applications and web services. This two-day course covers Microsoft SQL Server 2005 through 2008. It provides all the guidance an auditor needs to evaluate the security and control of SQL Server.

Using our detailed audit program, and your own SQL Server database login (if you take the ‘hands-on’ option), you will learn how the database is installed, what security configurations it offers and how it integrates into a Windows domain. You will see how users, groups and roles are created and managed, how database object security is handled, and what auditing and logging facilities are available to trace user activity. You will also be shown how to use T-SQL (Microsoft’s own version of Structured Query Language) to interrogate SQL Server’s internal system tables, using them to extract key audit data and automate the audit.

You will be able to take away your library of audit scripts to speed up your own SQL Server audits. A detailed audit program will be provided in print and electronic format.

Suggested duration: 2 days, but can be customised to your requirements.

Agenda

SQL Server Principles
The SQL Server Editions


Graphical Tools
Using the SQL Server Management Studio
Where to get on-line help - Working with SQL Server Books Online


SQL Server Configuration
System Objects – the master database
SQL Server client software and how it connects
SQL Server business continuity principles


Basic Querying
Introduction to the Query Analyser
Retrieving Data
System and user tables – where the information is kept
Using ODBC to extract SQL Server data to other applications
Stored Procedures and Triggers
Introduction to Transact-SQL (T-SQL)
System and User-defined Stored Procedures
Auditing stored procedures


SQL Server Security
Security Overview
Authentication
Understanding Server and Database Roles
Assigning permissions to tables, views and procedures
Application Roles
Database Ownership Chaining and its effect


Auditing SQL Server
Extracting audit data using SQL
Extracting audit data using the built-in stored procedures
Extracting audit data using SQL
Extracting audit data using the built-in stored procedures
Using triggers
The SQL Server built-in auditing facilities
The SQL Profiler and its information collection capabilities
Using SQL-DMO and SQL Server Management Objects to extract audit data
Auditing software for SQL Server
Data Transformation Services (DTS)
Introduction to DTS
Using the DTS Import/Export Wizard to collect audit data