Microsoft SQL Server 2008: Audit & Security Course

Course Overview

Walking around any end user or technical department, auditors always find developments under way using the Microsoft SQL Server environment. But while we continue to audit central IT systems, this platform infrequently appears as a target of evaluation in audit plans. Even though continuity of service to the organisation within departments could well depend on this product. Here is an opportunity to find out how SQL Server works, what are its security weaknesses and how they should be mitigated. This is a highly functional environment where communication across a Microsoft network is very easy to achieve. Too easy perhaps? As with all highly functional products the downside is insecurity. Learn hands-on what this means.

You will learn what the components of Microsoft SQL Server are and understand what the system administrators should be doing. Find out how to get information out of SQL Server and how to interpret it. Experiment with a live SQL Server network in a lab environment. Learn how to write auditors job packs in T-SQL, for regularly extracting information.

Covers all version up to and including SQL Server 2008.

Booking a Microsoft training course with CourseMonster couldn't be easier. Simply click on the date and location that suits you best, confirm your booking and we'll send you all the information that you need.

Related Courses

• Microsoft SQL Server 2008 New Features: Hands-On
• Microsoft SQL Server 2008 Comprehensive Introduction: Hands-On
• Microsoft SQL Server 2008: Database Administration
• Microsoft SQL Server 2008 Training Course UK: Comprehensive DBA & Programming Upgrade
• Microsoft SQL Server 2008 Analysis & Integration Services (SSAS & SSIS) - (Business Intelligence, BI, Data Warehouse, Mining)
• Microsoft SQL Server 2008 Reporting Services (SSRS) - (Business Intelligence, BI)
• Microsoft SQL Server 2008: Business Intelligence with SQL Server 2008 - (Microsoft, BI, SSRS, SSAS, SSIS, Data Warehouse, Mining)
• Microsoft SQL Server 2008: Maintaining a Microsoft SQL Server 2008 Database
• Microsoft SQL Server 2008 Reporting Services: Hands-On
• Microsoft SQL Server 2008: Implementing and Maintaining Microsoft SQL Server 2008 Reporting Services

Prerequisites

• Delegates should possess a basic understanding of database technology. Some prior knowledge of SQL will be useful.

Course Outline

INTRODUCTION TO AUDIT & SECURITY

AUDIT & SECURITY

• Checklist-Based Auditing
• Risk-Based Auditing
• Audit Plan
• CHECK LISTS
• DISA Database STIG
• NIST

THE BIG PICTURE

• Access Control
• Intrusion Prevention
• Intrusion Detection
• Secure Data Storage
• Secure Data Access

INTRODUCTION TO SQL SERVER

SECURITY CONSIDERATIONS

AUDIT CONSIDERATIONS

SQL SERVER BASIC ARCHITECTURE

• The Physical Database
• The master Database
• The msdb Database
• The model database
• The tempdb database
• The Instance
• Licensing
• SQL Server Services
• The File System Locations
• The Registry Keys
• Restricting Access to a SQL Server Instance
• Restricting Access to Databases
• Client Connectivity
• Network Protocols
• Encrypting Connections to SQL Server
• Instance & Database Metadata
• The Logical Database Architecture
• Database Objects
• Ownership
• Schemas
• Character Sets & Sort Orders

THE MANAGEMENT TOOLS

• Management Studio
• Configuration Manager
• Surface Area Configuration Manager
• osql Utility
• bcp Utility
• sqlcmd Utility
• Powershell

SQL SERVER LOGS

SQL SERVER SECURITY COMPLIANCE

SQL SERVER CONFIGURATION

• Ad Hoc Distributed Queries
• Agent XPs
• clr enabled
• Database Mail XPs
• Replication XPs
• SMO and DMO XPs
• SQL Mail XPs
• xp_cmdshell

DATABASE STORAGE

• Data Files
• Primary Data File
• Secondary Data File
• Transaction Log File
• Filegroups
• Storage Fault Tolerance

BACKUP DEVICES

BACKUP IMAGES

IMPORTING AND EXPORTING DATA

• SQL Server Data Transformation Services (DTS)
• Import/Export Wizard
• DTS Packages
• SQL Server Integration Service (SSIS)
• Back Ups and Restores
• Backing Up A Database or Transaction Log
• To Back Up A Database Or A Transaction Log
• SQL Server 2000 Backup
• SQL Server 2005 Backup
• The BACKUP Statement
• Restoring a database or Applying a Transaction log
• Security Consideration for Backup & Restore

USER SECURITY

LOGIN ACCOUNTS

• SQL Server authentication
• Windows authentication
• Change The Authentication Mode
• Default Login Accounts
• Creating Login Accounts

DATABASE USER ACCOUNTS

• Default Database User Accounts
• Creating Database Users
• The guest Account

ADMINISTRATIVE PRIVILEGES

• Server Roles
• User Defined Database Roles
• Application Roles

SQL SERVER PERMISSIONS

• Principals
• Securables
• Permissions
• Server Scope Permissions
• Database, Schema & Object Scope Permissions
• Statement Level Permissions
• Object Level Permissions

ASSIGNING PRIVILEGES & PERMISSIONS

• Statement Level Permissions
• Object Level Permissions
• GRANT, REVOKE & DENY Statements

IMPERSONATION

• User and Login Security Tokens
• Understanding Impersonation
• The AUTHENTICATE Permission
• The TRUSTWORTHY Property
• EXECUTE AS vs SETUSER

SQL SERVER ENCRYPTION

ENCRYPTION HIERARCHY

• The Service Master Key
• The Database Master Key
• Asymmetric Keys
• Certificates
• Symmetric Keys
• Transparent Database Encryption

ENCRYPTION & DECRYPTION FUNCTIONS

HIGH AVAILABILITY

• Replication
• Log Shipping
• Database Mirroring
• Clusters

APPLICATION DEVELOPMENT

• Sql Injection
• Validate User Input
• Module Signing
• Module Signatures

SELECTING THE TRUST MECHANISM

• Database Owner Approach
• Signature Approach

SQL SERVER AUDITING

• Login Audit
• SQL Profiler Traces
• SQL Server Profiler Audit Example
• SQL Trace
• Triggers
• DML Triggers
• DDL Event Triggers
• The EVENTDATA() Function
• Event Groups & Events
• Logon Triggers
• Notification Service

SQL SERVER SYSTEM VIEWS & STORED PROCEDURES

• System Views
• Built-In Stored Procedure Examples

SQL SERVER 2008 AUDIT

• The Server Audit object
• The Server Audit Specification
• The Database Audit Specification

POLICY BASED MANAGEMENT

• Policy Components
• Facet Properties
• Create & Managing Policies

THREATS & VULNERABILITIES

• Process Threats And Vulnerabilities
• Platform Threats And Vulnerabilities
• Authentication Threats And Vulnerabilities
• Programming Threats And Vulnerabilities
• Data Access Threats And Vulnerabilities