Microsoft SQL Server Audit & Security Course

Topics covered on the 3 day Microsoft SQL Server Audit & Security course

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