O'Reilly logo
live online training icon Live Online training

Data Encryption with SQL Server

Protecting the data with which you have been entrusted

Topic: Data
Tom Norman

Protecting data is a key challenge for every company. To make sure your company stays off the ever-growing list of data breaches, you’ll need to encrypt your data. But encryption is complex and hard to implement. How does encrypting data affect database performance? What data fields should you encrypt and why? And if you can’t encrypt all these fields, what else can you do to protect sensitive data?

Expert Tom Norman answers these questions and more. You’ll get a concise yet comprehensive overview of the major options to encrypt data within SQL Server—Transparent Data Encryption, cell-level encryption, and Always Encrypted—along with the appropriate situations where each method should be deployed and how each encryption method affects performance. You’ll also cover other data protection methods in SQL Server, from auditing to data classification. Join in to gain a deeper understanding of SQL Server encryption options and learn how to fully protect your data.

What you'll learn-and how you can apply it

By the end of this live online course, you’ll understand:

  • Current data privacy laws
  • How to implement Transparent Data Encryption
  • How encryption choices affect performance

And you’ll be able to:

  • Set up and work with Transparent Data Encryption
  • Implement encrypted fields within SQL Server
  • Set up Always Encrypted encryption
  • Audit sensitive data usage

This training course is for you because...

  • You’re a data professional entrusted with company data, and you use SQL Server.
  • You work with data that must be protected.
  • You understand the need to encrypt data.
  • You want to stay off the data breach list.


  • A computer with a Remote Desktop Protocol client installed (If you’re using Windows, this client is built-in; if you’re using a Mac, please preinstall a RDP client such as Microsoft Remote Desktop or FreeRDP.)
  • A free Microsoft account (You can sign up through Azure Lab Services.)
  • Basic familiarity with SQL Server

Recommended follow-up:

About your instructor

  • Tom Norman is a Database Architect at KPA with a strong fervor to protect data. He works daily to review and improve data protection methodologies while reviewing governing laws affecting finance, healthcare, and personal data. His areas of expertise include encryption, auditing, data identification and database object deployment. He is the current leader of the PASS Virtualization Group and Vice President of the TRIPASS user group.


The timeframes are only estimates and may vary according to how the class is progressing

Course overview (15 minutes)

  • Presentation: Introduction to Azure Data Studio; overview of data breaches; examination of government regulations
  • Q&A

Encryption method one: Transparent Data Encryption (TDE) (45 minutes)

  • Presentation: Overview of Transparent Data Encryption; suggested naming convention of TDE certificates; the importance of the TDE certificate; enabling and turning on on Transparent Data Encryption; restoring a Transparent Data Encryption Database on another server
  • Hands-on exercises: Create a master key; create a TDE certificate; back up your TDE certificate; turn on Transparent Data Encryption; restore a provided TDE database from another computer on your computer; rotate a TDE certificate
  • Group discussion: The effects of turning on Transparent Data Encryption; changing and rotating a TDE certificate on the computer; providing a TDE-enabled database to developers
  • Q&A

Break (5 minutes)

Encryption methods two and three: Cell-level encryption and Always Encrypted (45 minutes)

  • Presentation: Cell-level encryption overview and options; Always Encrypted overview and setup; creating a column encryption key; encrypting a column with Always Encryption; the limitations of Always Encrypted; advancements to Always Encryption in SQL Server 2019
  • Hands-on exercises: Create a column master key with Windows Certificate Store; back up a column master key with Windows Certificate Store; create a column master key with Azure Key Vault; export and grant security to a column master key; create a column encryption key; encrypt a column with Always Encryption; explore the limitations of Always Encrypted
  • Group discussion: Setting up security access for a column master key; implementing Always Encryption in the real world; What columns do I encrypt?
  • Q&A

Break (5 minutes)

Other data protection methods: Auditing (25 minutes)

  • Presentation: Overview of SQL Server Audit; creating an SQL Server audit; setting up an SQL Server audit with a security exception (and why you’d do so); reviewing your SQL Server audit data; providing a daily audit report
  • Hands-on exercises: Create an SQL Server audit on the “a” field; add an SQL Server audit with a security exception; review data collection with SQL Server Audit; create process to provide a daily audit report via email
  • Q&A

Other data protection methods: Data classification (20 minutes)

  • Presentation: Overview of SQL Server Data Classification; how to classify data in SQL Server Management Studio; adding data classification with table declaration scripts
  • Hands-on exercise: Classify data in SQL Server Management Studio; create and add data classification scripts
  • Q&A

Break (5 Minutes)

Source-controlling encryption, auditing, and data classification (15 minutes)

  • Presentation: Overview of source control needs; reviewing files to source control
  • Hands-on exercise: Review and create files to source control for encryption, auditing, and data classification
  • Q&A