Cannot use Certificate because its Private Key is not Present

2019-03-21 · by Hannah Vernon · in data security

Introduction

Transparent Database Encryption, or TDE as it’s colloquially known, is a great solution for data security “at rest”. That is, data is encrypted on-disk, preventing someone with a copy of the database files from viewing data stored in the database. With a non-TDE encrypted database, if you have a copy of the database files, you can simply attach them to any other SQL Server¹. If you store personally identifiable information, or PII, in your database, or you’re storing financial data and need to comply with PCI-DSS, it’s imperative that you encrypt that data.

TDE is almost as hard to setup as this never ending waterfall.
Almost.

Let’s setup TDE!

TDE makes the process fairly simple, although there are some “gotchas”. Be aware of the encryption requirements imposed by SQL Server. For instance, you must trust SQL Server to handle the private keys. In other words, if you attempt to encrypt a database using a password-protected certificate, SQL Server will not allow you to proceed, as I show in this post.

The script below looks like it should work:

USE master;

--drop the TDETest database if it exists, so we can run a clean test
IF DB_ID(N'TDETest') IS NOT NULL
BEGIN
    ALTER DATABASE TDETest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE TDETest;
END

--create the TDETestCertificate, with a password
IF EXISTS (SELECT 1 FROM sys.certificates c WHERE c.name = N'TDETestCert')
BEGIN
    DROP CERTIFICATE TDETestCert;
END
CREATE CERTIFICATE TDETestCert 
AUTHORIZATION dbo
ENCRYPTION BY PASSWORD = N'^MaffiaDhakaWingerBari3'
WITH SUBJECT = N'TDE Encryption Certifcate for TDETest Database'
    , START_DATE = '2019-03-20T00:00:00'
    , EXPIRY_DATE = '2020-03-20T00:00:00';
GO

--create the TDETest database
CREATE DATABASE TDETest
COLLATE SQL_Latin1_General_CP1_CI_AS;
ALTER DATABASE TDETest SET RECOVERY FULL;
GO

--attempt to create a database encryption key
USE TDETest;
CREATE DATABASE ENCRYPTION KEY 
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDETestCert;

USE master;

--drop the TDETest database if it exists, so we can run a clean test

IF DB_ID(N'TDETest') IS NOT NULL

BEGIN

ALTER DATABASE TDETest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

DROP DATABASE TDETest;

END

--create the TDETestCertificate, with a password

IF EXISTS (SELECT 1 FROM sys.certificates c WHERE c.name = N'TDETestCert')

BEGIN

DROP CERTIFICATE TDETestCert;

END

CREATE CERTIFICATE TDETestCert

AUTHORIZATION dbo

ENCRYPTION BY PASSWORD = N'^MaffiaDhakaWingerBari3'

WITH SUBJECT = N'TDE Encryption Certifcate for TDETest Database'

, START_DATE = '2019-03-20T00:00:00'

, EXPIRY_DATE = '2020-03-20T00:00:00';

--create the TDETest database

CREATE DATABASE TDETest

COLLATE SQL_Latin1_General_CP1_CI_AS;

ALTER DATABASE TDETest SET RECOVERY FULL;

--attempt to create a database encryption key

USE TDETest;

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER CERTIFICATE TDETestCert;

Msg 33101, Level 16, State 1, Line 31
Cannot use certificate 'TDETestCert', because its private key is not present or it is not protected by the database master key. SQL Server requires the ability to automatically access the private key of the certificate used for this operation.

Trying to encrypt the certificate with a password, in line 17 with the ENCRYPTION BY PASSWORD = N'^MaffiaDhakaWingerBari3' clause, prevents SQL Server being able to use the certificate for database encryption. Removing that single line will allow the CREATE DATABASE ENCRYPTION KEY statement to complete successfully:

USE master;

--drop the TDETest database if it exists, so we can run a clean test
IF DB_ID(N'TDETest') IS NOT NULL
BEGIN
    ALTER DATABASE TDETest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE TDETest;
END

--create the TDETestCertificate, with a password
IF EXISTS (SELECT 1 FROM sys.certificates c WHERE c.name = N'TDETestCert')
BEGIN
    DROP CERTIFICATE TDETestCert;
END
CREATE CERTIFICATE TDETestCert 
AUTHORIZATION dbo
--ENCRYPTION BY PASSWORD = N'^MaffiaDhakaWingerBari3'
WITH SUBJECT = N'TDE Encryption Certifcate for TDETest Database'
    , START_DATE = '2019-03-20T00:00:00'
    , EXPIRY_DATE = '2020-03-20T00:00:00';
GO

--create the TDETest database
CREATE DATABASE TDETest
COLLATE SQL_Latin1_General_CP1_CI_AS;
ALTER DATABASE TDETest SET RECOVERY FULL;
GO

--attempt to create a database encryption key
USE TDETest;
CREATE DATABASE ENCRYPTION KEY 
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDETestCert;

USE master;

--drop the TDETest database if it exists, so we can run a clean test

IF DB_ID(N'TDETest') IS NOT NULL

BEGIN

ALTER DATABASE TDETest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

DROP DATABASE TDETest;

END

--create the TDETestCertificate, with a password

IF EXISTS (SELECT 1 FROM sys.certificates c WHERE c.name = N'TDETestCert')

BEGIN

DROP CERTIFICATE TDETestCert;

END

CREATE CERTIFICATE TDETestCert

AUTHORIZATION dbo

--ENCRYPTION BY PASSWORD = N'^MaffiaDhakaWingerBari3'

WITH SUBJECT = N'TDE Encryption Certifcate for TDETest Database'

, START_DATE = '2019-03-20T00:00:00'

, EXPIRY_DATE = '2020-03-20T00:00:00';

--create the TDETest database

CREATE DATABASE TDETest

COLLATE SQL_Latin1_General_CP1_CI_AS;

ALTER DATABASE TDETest SET RECOVERY FULL;

--attempt to create a database encryption key

USE TDETest;

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER CERTIFICATE TDETestCert;

However, the ENCRYPTION BY PASSWORD = ... clause protects the certificate’s private key, and without that clause SQL Server can’t create the certificate unless we first create a database master key, in the master database. The master key is used to encrypt the certificate’s private key. With the required statements, the script now looks like:

USE master;
IF NOT EXISTS (
    SELECT 1
    FROM sys.key_encryptions ke
    WHERE ke.key_id = 101 --master key
    )
BEGIN
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'[FilletCastorBlameBlain5';
END
GO

--drop the TDETest database if it exists, so we can run a clean test
IF DB_ID(N'TDETest') IS NOT NULL
BEGIN
    ALTER DATABASE TDETest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE TDETest;
END

--create the TDETestCertificate, with a password
IF EXISTS (SELECT 1 FROM sys.certificates c WHERE c.name = N'TDETestCert')
BEGIN
    DROP CERTIFICATE TDETestCert;
END
CREATE CERTIFICATE TDETestCert 
AUTHORIZATION dbo
--ENCRYPTION BY PASSWORD = N'^MaffiaDhakaWingerBari3'
WITH SUBJECT = N'TDE Encryption Certifcate for TDETest Database'
    , START_DATE = '2019-03-20T00:00:00'
    , EXPIRY_DATE = '2020-03-20T00:00:00';
GO

--create the TDETest database
CREATE DATABASE TDETest
COLLATE SQL_Latin1_General_CP1_CI_AS;
ALTER DATABASE TDETest SET RECOVERY FULL;
GO

--attempt to create a database encryption key
USE TDETest;
CREATE DATABASE ENCRYPTION KEY 
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDETestCert;

USE master;

IF NOT EXISTS (

SELECT 1

FROM sys.key_encryptions ke

WHERE ke.key_id = 101 --master key

)

BEGIN

CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'[FilletCastorBlameBlain5';

END

--drop the TDETest database if it exists, so we can run a clean test

IF DB_ID(N'TDETest') IS NOT NULL

BEGIN

ALTER DATABASE TDETest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

DROP DATABASE TDETest;

END

--create the TDETestCertificate, with a password

IF EXISTS (SELECT 1 FROM sys.certificates c WHERE c.name = N'TDETestCert')

BEGIN

DROP CERTIFICATE TDETestCert;

END

CREATE CERTIFICATE TDETestCert

AUTHORIZATION dbo

--ENCRYPTION BY PASSWORD = N'^MaffiaDhakaWingerBari3'

WITH SUBJECT = N'TDE Encryption Certifcate for TDETest Database'

, START_DATE = '2019-03-20T00:00:00'

, EXPIRY_DATE = '2020-03-20T00:00:00';

--create the TDETest database

CREATE DATABASE TDETest

COLLATE SQL_Latin1_General_CP1_CI_AS;

ALTER DATABASE TDETest SET RECOVERY FULL;

--attempt to create a database encryption key

USE TDETest;

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER CERTIFICATE TDETestCert;

Running the code above now results in a warning by SQL Server:

Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.

Backup the certificate using this code:

BACKUP CERTIFICATE TDETestCert 
TO FILE = N'D:\SQLServer\Backups\TDETestCert.cert'
WITH PRIVATE KEY (
    FILE = N'D:\SQLServer\Backups\TDETestCert.key'
    , ENCRYPTION BY PASSWORD = N'=BebopAcetalCareReims1'
    );

BACKUP CERTIFICATE TDETestCert

TO FILE = N'D:\SQLServer\Backups\TDETestCert.cert'

WITH PRIVATE KEY (

FILE = N'D:\SQLServer\Backups\TDETestCert.key'

, ENCRYPTION BY PASSWORD = N'=BebopAcetalCareReims1'

);

The WITH PRIVATE KEY clause specifies that SQL Server will create a file containing a copy of the certificate’s private key. The ENCRYPTION BY PASSWORD = N'=BebopAcetalCareReims1' clause encrypts the TDETestCert.key file with that password. If you lose that password, you’ll never be able to restore the certificate from the .cert file. Store both the .cert and the .key file in a secure location. SQL Server will protect the exported .cert and .key files by limiting the Access Control List (or ACL) on each file. Only the following Windows principals will have access to the file; OWNER RIGHTS, Administrators, and the service account used by SQL Server.

You also need to backup the master key; like this:

BACKUP MASTER KEY TO FILE = N'D:\SQLServer\Backups\Master_Key.key'
ENCRYPTION BY PASSWORD = N'#BlotchOwenLonganOrly8';

1 2	BACKUP MASTER KEY TO FILE = N'D:\SQLServer\Backups\Master_Key.key' ENCRYPTION BY PASSWORD = N'#BlotchOwenLonganOrly8';

In the example above, I’m using #BlotchOwenLonganOrly8 as the password to encrypt the backup file. If you lose this password you will never be able to restore the master key. It is imperative that you record the password in a secure location. I’d recommend using a password management tool such as the free and open-source KeePass.

Please Note!

Oh, and please, don’t use the passwords presented in this blog post. They are just examples.

One thing to consider, the above code doesn’t actually encrypt the database. At this point, we’ve only created the encryption infrastructure required. To encrypt the database, run this code:

ALTER DATABASE TDETest SET ENCRYPTION ON;

1	ALTER DATABASE TDETest SET ENCRYPTION ON;

That code causes SQL Server to begin encryption of all data and log files, including full-text indexes, in the background. Use this query to see the process of the background encryption process:

SELECT d.name
    , dek.create_date
    , dek.percent_complete
FROM sys.dm_database_encryption_keys dek
    INNER JOIN sys.databases d ON dek.database_id = d.database_id
ORDER BY d.name;

SELECT d.name

, dek.create_date

, dek.percent_complete

FROM sys.dm_database_encryption_keys dek

INNER JOIN sys.databases d ON dek.database_id = d.database_id

ORDER BY d.name;

Let me know if you have an questions or comments about this. I hope this post helps clarify what causes the “cannot use certificate” error.

This post is part of our series on data security.

Footnotes

¹ – any SQL Server that is the same or higher version.

3 Responses

Filesystem security for TDE Keys and Certificates - SQL Server Science

2019-03-22 at 8:32 AM ·

[…] This post is part of our series on Data Security. You may also be interested in our prior post on what happens when you try to use a password to encrypt a database. […]

Loading...
Mark

2023-03-28 at 8:37 PM · Reply →

Thanks for this post, but you don’t explain how we can password protect the certificate private key as you eliminated the ENCRYPTION BY PASSWORD statement. Or is this no longer necessary as the certs private key is now protected by the DMK?

Loading...
1. Hannah Vernon
  
  2023-03-28 at 8:48 PM · Reply →
  
  Yes, password protecting the certificate would result in SQL Server not being able to open the database at startup, and is not supported.
  
  Loading...

Cannot use Certificate because its Private Key is not Present

Introduction

Let’s setup TDE!

Please Note!

Footnotes

Related

3 Responses

Leave a ReplyCancel reply

Search

Categories

Pages

Meta