unable to create the Database Encryption Key (DEK). Getting Error : = Msg 15151, Level 16, State 1, Line 1 Cannot find the certificate 'cert_name', because it does not exist or you do not have permission.

Question

unable to create the Database Encryption Key (DEK). Getting Error : = Msg 15151, Level 16, State 1, Line 1 Cannot find the certificate 'cert_name', because it does not exist or you do not have permission.

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
May 22, 2015 at 7:45 am

#398494

Hello Experts,
Getting error while applying the Database Encryption Key (DEK).
--1. Step 1: I creatd the databse master key (worked fine)
USE [DB]
BEGIN TRANSACTION;
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys where name = '##MS_DatabaseMasterKey##')
CREATE MASTER KEY ENCRYPTION BY PASSWORD='some_password here'
COMMIT;
go
--2 : Step 2 : Created certificate (worked fine)
USE [db]
CREATE CERTIFICATE user_cert
WITH SUBJECT = 'DB Encryption Certificate for [db] Database.';
--3. Took the backup of the certificate (worked fine)
--4. --Create the Database Encryption Key (DEK)
USE [db]
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE user_cert;
GO
Step 4 fails with the error msg as:
Msg 15151, Level 16, State 1, Line 1
Cannot find the certificate 'user_cert', because it does not exist or you do not have permission.
Can you suggest what may be the error and how to fix this?
Note: I did the same for anothre 8 database successfully in the same way in the same SQL Instance. Worked absolutely fine
Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Maddave SSCertifiable Points: 6828 More actions · Answer 1

You're creating the master key and certificate on the Database. You need to create it on the server, so run the first two commands on the master database. Then create the database encryption key on the database:

From BOL:https://msdn.microsoft.com/en-gb/library/bb677241.aspx?f=255&MSPPError=-2147217396

" The certificate or asymmetric key that is used to encrypt the database encryption key must be located in the master system database."

So you should do:

USE [master]

BEGIN TRANSACTION;

IF NOT EXISTS (SELECT * FROM sys.symmetric_keys where name = '##MS_DatabaseMasterKey##')

CREATE MASTER KEY ENCRYPTION BY PASSWORD='some_password here'

COMMIT;

go

--2 : Step 2 : Created certificate (worked fine)

USE Master

CREATE CERTIFICATE server_cert -- changed name to reflect the location better.

WITH SUBJECT = 'DB Encryption Certificate for [db] Database.';

--3. Took the backup of the certificate (worked fine)

--4. --Create the Database Encryption Key (DEK)

USE [db]

GO

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_256

ENCRYPTION BY server CERTIFICATE server_cert;

GO

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 2

One more help/suggestion.

I found that some1 accidentally did nt mention d expiry date against each certificate so by default it took 1 yr of expiry.

Is there anyway I can alter the date to perpetuity?

I dont want to drop & recreate then again..

Thanks.

Perry Whittle SSC Guru Points: 233846 More actions · Answer 3

SQL-DBA-01 (5/22/2015)
One more help/suggestion.
I found that some1 accidentally did nt mention d expiry date against each certificate so by default it took 1 yr of expiry.
Is there anyway I can alter the date to perpetuity?
I dont want to drop & recreate then again..

Create a new cert with the appropriate start and end date, then once done alter the encryption key using

ALTER DATABASE ENCRYPTION KEY

ENCRYPTION BY SERVER CERTIFICATE new_cert_Name

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 4

Thnx..

I m now recreating certs with proper start n expiry dates.

I m.planning to automate this now

Thanks.

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 5

Hi Experts,

I have written a custom automated script to implement TDE. Attached the one./

I have few concerns on the results part:=

1. After the task is over when I look at the column is_master_key_encrypted_by_server, I see the data is 0. I shiould be 1 instead, when I do it manually following each steps.

2. After I execute the data command, the encryption process says "in progress", stage is 2. IF I look at the errorlog files, it says that it is processing, but after an hour also the operation do not get complete on a database of 100 MB space.

So when I run the command manually, it works and encryption goes complete.

--Turn on encryption in database

declare @cmd1 nvarchar(4000);

select @cmd1 = 'USE master' + ';' + CHAR(13)+ 'ALTER DATABASE ' + quotename(@db_nm) + ' SET ENCRYPTION ON';

Print @cmd1

exec(@cmd1)

WAITFOR TIME '00:00:10';

Please revierw the attached script and suiggest what's wrong here, would be a great help.

Thanks.