SQL: Forgetting to backup the TDE certificate is a CLM

Years ago, I was a trainer for the Microsoft SQL Server Master’s program at their headquarters in Redmond. In one of the labs, I asked the students to set up database mirroring on a TDE-encrypted database. It was fascinating to watch them try to set it up.

It’s important to realize that the students in the room were all Premier Field Engineers and senior consultants from Microsoft and from some large partner companies. The students were all very experienced with the product. Yet it often took many of them quite a while to work out how to set it up.

The main problem wasn’t the database mirroring, it was that they struggled to restore an encrypted database on another server. Many understood that they needed to backup and restore the server certificate, but they forgot to include the private key.

A serious warning

The key message here is that if they struggled, you might have issues as well, unless you know what you’re doing. TDE is an excellent feature in SQL Server but it’s not a feature that you just dabble with. If you get it wrong, remember that it is designed to keep the wrong people out. And if you don’t have the correct keys, etc., that also means you.

I remember attending a SharePoint session one day, and the person delivering the session mentioned that it was a good idea to encrypt your databases, and he showed how easily it could be done. But there wasn’t the slightest mention of what comes next.

The problem is that if you make a mistake, it might be a long time before you find out i.e. when you need to restore the database on another server.

A CLM

I have a friend that used to call this sort of mistakes, a CLM. (Career Limiting Move). You don’t want to make one. You need to practice all of this before you start to use it for production work.

The server certificate and private key must be backed up, and the backups must not be lost.

There are two components to the certificate backup. The certificate itself holds the public key, but the private key must also be backed up.

BACKUP CERTIFICATE SecureDBCert   
TO FILE = N'C:\Temp\SecureDBCert.cer'  
WITH PRIVATE KEY   
(  
    FILE = N'C:\Temp\SecureDBCert.pvk',  
    ENCRYPTION BY PASSWORD = '0234@#$@asdgAAA'  
);  
GO

While not required, I suggest that .cer is used as the filetype for the certificate and .pvk is used for the private key.

The file that holds the private key can also be encrypted by a password. If you do use a password (as shown in the example above), make sure that you also retain that password, so that the private key file can be opened later.

On the right-hand side of my blog posts, you’ll see a link to a free eBook on TDE. Make sure you download it, and read it.

2025-08-10