Unable to create SQL encryption key

Question

I am trying to encrypt a backup for our new financial database. This is a completely new database, so absolutely nothing has been created before. I work in Microsoft SQL Server 2014.

I tried to monitor the MSDN, but this is not useful, the pages simply link to each other and end in a self-referential circle. Painful!

So here is what I have done so far.

• Recorded as sa
• sp_configure 'show advanced', 1
• sp_configure 'EKM provider enabled', 1

• Created a certificate

  CREATE CERTIFICATE fooBackup 
     ENCRYPTION BY PASSWORD = '#########'
     WITH SUBJECT = '### Server Backup Key', 
     EXPIRY_DATE = '20201031';
  GO
  

• Checked that it exists with SELECT * FROM sys.certificates(it exists!)

• Tried to create an encryption key

  CREATE DATABASE ENCRYPTION KEY
      WITH ALGORITHM = AES_256
      ENCRYPTION BY SERVER CERTIFICATE fooBackup;
  GO
  

And here I am stuck. I get the following error:

Msg 15151, Level 16, State 1, Line 10

Cannot find the certificate "fooBackup" because it does not exist or you do not have permission.

ALTER CONTROL , http://bit.ly/1OcbYyt, , , SA. , . ... .

-, SQL, !

Answer 1

, , , .

:

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '5tr0ngP@ssW0rd'
GO

OPEN MASTER KEY DECRYPTION BY PASSWORD = '5tr0ngP@ssW0rd'

CREATE CERTIFICATE MyDBcert 
  WITH SUBJECT = 'The Cert For MyDB';
GO

USE MyDB;
GO

CREATE DATABASE ENCRYPTION KEY
  WITH ALGORITHM = AES_256 
  ENCRYPTION BY SERVER CERTIFICATE MyDBcert;
GO

.