Managing SQL Server Encryption Certificates Getting Started with SQL Server 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

Learn best practices in certificate management for SQL Server. Certificates are small snippets of text that are foundational to encrypting and decrypting data. Watch this video to learn how to safely store certificates and prevent them from falling into the wrong hands and compromising the very data you are using them to protect. Also learn how to ensure that you still have access to needed certificates following a catastrophe or other data loss event, because data loss from lost certificates is a very real risk that must be managed and mitigated.

This video begins with a short introduction to certificates and some of the different types of encryption they enable you as a SQL Server DBA to implement – such as transparent data encryption (TDE), backup encryption, and column encryption. Then you’ll learn practices for generating and managing certificates specifically for each of the different certificate types. Next, you’ll learn best practices for backing up and securing your encryption certificates so that you have them when you need them, because losing them can mean losing your data altogether. You’ll learn about certificate expiration, and the proper methods by which to backup, store, restore, and protect your certificates so that any encrypted data is never lost.

What You Will Learn

  • Ensure access to certificates following a catastrophe or other data loss event

  • Learn about the types of encryption SQL Server offers

  • Understand how certificates enable encryption

  • Generate correct certificates for your chosen encryption methods

  • Find where each certificate type is stored, how to back it up, and how to restore it

  • Identify best practices with regards to generating and managing certificates

Who This Video Is For

For database administrators and system administrators who handle or plans to handle certificates for encrypting data on SQL Server instances.

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. He has been a presenter at PASS Summit, SQL Saturdays, and the in.sight transportation conference.


About this video

Eric Blinn
Online ISBN
Total duration
38 min
Copyright information
© Eric Blinn 2019

Video Transcript


Hello and welcome. My name is Eric Blinn. And we’re here to learn about SQL server encryption keys and certificates. A little bit about myself, I’ve been working in the SQL server space for a little over 15 years now. I’ve co-authored a few white papers and written some blog posts mostly on my website But I’ve done a few guest articles on other sites. You may have seen me at the Ohio North SQL Server User Group in Cleveland, where I currently serve as the vise president, or at one of many SQL server community events like SQL Saturday or PASS Summit, where I try to present as time allows. Currently, I’m the senior data architect at Squire Patton Boggs, an international law firm based out of Cleveland, Ohio.

What we’re here to learn– SQL Server has many options to encrypt the data it stores along with the metadata it keeps, mostly things like passwords or other credentials. It does so using an encryption hierarchy where each layer of encryption is protected by the layer above it. These layers rely on keys and certificates to perform the encryption activities. What we’re here to learn is the different layers of that hierarchy and how to protect the keys and/or certificates at each level.

What this video isn’t– this video is designed to be a very practical look at encryption keys and certificates used by SQL server. It is not a primer on encryption itself nor a look at encryption activities within SQL server. It is designed to be a prerequisite course taken before undertaking encryption activities within SQL server to reduce the risk of data loss or data breaches due to lost or mismanaged keys or certificates.

At the risk of turning this into a lesson on encryption itself, we do need to understand a few things about it to move forward, mainly these two words that we’re putting in this glossary of terms. These words are key and certificate. You’ll often hear them used interchangeably. But there is a difference. The key is the source object used to actually encrypt or decrypt data. There are different kinds of keys, but that distinction is out of scope for this video. A certificate accompanies a key and verifies its authenticity. It is these objects, encryption keys and certificates, that we’ll focus on generating and protecting throughout the course of this video.

These items we want to protect include master keys– this would be a service master key or a database master key– SQL server certificates– these we create inside the database to protect items within it– the SSRS encryption key– this is a very important and often overlooked key– and column encryption keys– this is for the Always Encrypted service. These are actually outside of the SQL Server encryption hierarchy. But they’re still very important. So we’re going to cover them. And lastly, we’ll talk about some of the best ways we can manage these keys and certificates to make sure that they are properly protected and cared for.