SQL Server Always Encrypted Protecting Your Data with Column Encryption

  • Eric Blinn

Your browser needs to be JavaScript capable to view this video

Try reloading this page, or reviewing your browser settings

You're watching a preview of subscription content. Log in to check access

Master SQL Server’s Always Encrypted feature to provide column-level encryption of sensitive data such that only authorized application users can see that data. This video explains how the Always Encrypted feature works and shows how to implement column encryption using best practices. System and database administrators who might otherwise have access to view any data in the database are not able to view data protected through Always Encrypted’s feature set.

This video begins with an introduction to column encryption as implemented through the Always Encrypted feature, and the use cases for applying that encryption. Then follows an explanation of how exactly the technology works and is implemented in SQL Server. The video then walks you through implementing Always Encrypted column encryption on a new database and again on an existing database. Finally, the video covers how to configure an application to access the encrypted data in a manner that is transparent and convenient for end users of your database applications.

What You Will Learn

  • Recognize use cases for the Always Encrypted feature

  • Understand how the feature works technically

  • Generate an encrypted column for a new table

  • Insert data into an encrypted database column

  • Encrypt existing data within a table

  • Identify the relevant encryption certificates

  • Modify an application to access encrypted data

Who This Book Is For

Database administrators, application developers, and system architects who need to encrypt and protect data in a SQL Server instance. Especially helpful when it must be ensured that database administrators and system administrators do not in any way have access to encrypted data.

This video explains of how exactly the technology works and is implemented in SQL Server. The video then walks you through implementing Always Encrypted column encryption on a new database and again on an existing database. Finally, the video covers how to configure an application to access the encrypted data in a manner that is transparent and convenient for end users of your database applications.

About The Author

Eric Blinn

Eric Blinn has over a decade’s experience as a SQL Server DBA in the legal, software, transportation, and insurance industries. Currently he is the Sr. Data Architect for Squire Patton Boggs, a leading provider of legal services with 47 offices in 20 countries. He is also the vice president of the Ohio North SQL Server Users’ Group. Eric has been a presenter at PASS Summit, SQL Saturdays, and the in.sight transportation conference.

 

About this video

Author(s)
Eric Blinn
DOI
https://doi.org/10.1007/978-1-4842-5565-0
Online ISBN
978-1-4842-5565-0
Total duration
58 min
Publisher
Apress
Copyright information
© Eric Blinn 2019

Related content

Video Transcript

[MUSIC PLAYING]

Hello and welcome. In this video, we’ll cover SQL Server column encryption using Always Encrypted. You will cover Always Encrypted completely, starting with a technical understanding of how it works through creating a new or modifying an existing application to use the feature, along with some ongoing maintenance. My name is Eric Blinn, and I’ve spent the last 15 years as a SQL Server professional in a number of industries from transportation to insurance to software and legal services.

As of this recording, I’m also the vice president of the Ohio north SQL Server user group based in Cleveland, Ohio. For my day job, I’m the senior data architect for Squire Patton Boggs, an international law firm with offices in 20 countries. Always Encrypted was introduced in SQL Server 2016 and is available in both standard and enterprise editions.

It’s most commonly compared to TDE, or transparent data encryption. While both technologies encrypt data, the similarities largely end of there, where RDE encrypts the entire database, Always Encrypted only encrypts specific columns. TDE does its cryptographic operations on the server side only when data is read from or written to disk.

Because of this, the data is encrypted at rest only. It is not encrypted in the buffer pool or in transit to the client. Always Encrypted does its cryptographic operations on the client side, meaning the data is encrypted in transit to or from the server. It remains encrypted in memory, and again, at rest on disk.

The other large difference is that Always Encrypted can be used to deny column access to otherwise privileged users. The DBA or any other server insider does not need access to the cryptographic keys to manage the server or the database. They will be able to perform all required tasks, including server maintenance and even to query these encrypted columns but only view the cyber text, not the encrypted clear text.

The first segment of this video will explain the encryption hierarchy used by Always Encrypted. Next, we’ll look at the two types of encryption offered by the feature and talk about how to choose the right one. In the next segment, we’ll talk about how the applications in SQL Server interact to make this feature work.

Then, we’ll start building a new database that uses the feature. We’ll move on to modifying an existing database to use it. We’ll move next to the client side and start inserting new data into an encrypted column and making sure our applications are ready for the change. Finally, we’ll look at some of the maintenance involved with Always Encrypted, specifically key rotation, before we do a final wrap up of the video. Let’s get started.