SQL Server Security 201

This series will focus on creating a secure environment for enterprise data using best practices and tools built into SQL Server 2008 (and since the functionality hasn't changed dramatically from SQL Server 2005, almost every concept applies to that version as well.) Topics include an in-depth discussion of Windows and Mixed-Mode Authentication, Principals, Securables and Permissions, using Schemas, how to reduce SQL Server's attack surface, encryption of data and stored procedures, preventing SQL injection attacks and more.

Note: this is a 200-level topic, meaning that you should have some experience already with the SQL Server Management Studio and writing T-SQL scripts by hand. This is the entry point into all security topics on TrainingSpot.com (i.e., there will be no SQL Server Security 101 series.)

Total Time: 56 Minutes              Total Videos: 6

  • Lesson 1

    • Video 1: Series Introduction
      (Running Time: 2:11)

      This first video is simply an introduction to the SQL Server that will provide an overview of the scope and topics that will be discussed in the series. Additionally, it describes the necessary skills that the viewer should have before attempting to watch this 200-level series.


    • Video 2: Security Concepts
      (Running Time: 9:17)

      In this video we begin to talk about the different ways that SQL Server can be assaulted. Microsoft describes security threats using the acronym STRIDE ... which stands for:

      Spoofing Identity

      Tampering

      Repudiation

      Information Disclosure

      Denial of Service

      Elevation of Privilege

      ... each of these concepts are discussed. We also begin to compile a list of security best practices, seeding the initial list with six ideas which we'll build on in subsequent videos.


    • Video 3: Understanding Authentication Modes
      (Running Time: 9:53)

      In this video we discuss the two authentication modes that are supported by SQL Server: namely, Windows Authentication Mode and Mixed-Mode. As we discuss in the video, Windows Authentication Mode requires an Active Directory domain controller as a means of controlling access to the SQL Server environment, and is therefore the most secure mode. Mixed-Mode also includes SQL Server security, and is the least secure because it manages a separate set of logins from the domain and can more easily be attacked by a hacker. We discuss the pro's and cons of each authentication mode and add five new items to our list of security best practices near the end of the video.


    • Video 4: Principals and Securables
      (Running Time: 17:50)

      In this video we talk about Principals, Securables, Privileges (or rather, Permissions), Roles and Schemas to map out the taxonomy of all database objects in SQL Server and how SQL Server determines who should be allowed to perform which actions on a given database object (securable). We then talk about using Roles and Schemas to simplify the process of giving each user access to the resources he needs to do his job. Finally, we discuss additional best practices relating to Roles and Schemas


  • Lesson 2 - Using Windows Authentication Mode

    • Video 1: Adding Users and Groups via Active Directory
      (Running Time: 9:54)

      In this video we demonstrate how to manage users and groups in Active Directory in Windows 2008 Server. This will help us to demonstrate how to allow users and groups access to SQL Server in Windows Authentication Mode in subsequent videos.


    • Video 2: Creating Server and Database Logins
      (Running Time: 7:51)

      Continuing from the previous video, here we demonstrate creating Server logins using Active Directory users and continue to demonstrate how to create Database users and review the error messages we receive when we don't have proper permissions as we attempt to perform several common server and database operations.