SQL Server 2005 create certificate with private key / encryption

Ok, here is my problem. I am doing data encryption in SQL Server 2005 using a DB Master Key, Certificate and Symmetric Key. I need to be able to restore a certificate with a private key. But when I run the CREATE CERTIFICATE with PRIVATE KEY, the certificate gets pulled into the DB but the private key does not show up. Below are the steps I follow for testing.

Create the Database Master Key.

 CREATE MASTER KEY ENCRYPTION BY PASSWORD = '12345'

Create the Certificate

CREATE CERTIFICATE MyCert 
WITH SUBJECT = 'My First Certificate', 
EXPIRY_DATE = '1/1/2199';

Create a symmetric key that is encrypted with MyCert.

CREATE SYMMETRIC KEY MySymmetricKey 
WITH ALGORITHM = AES_256 
ENCRYPTION BY CERTIFICATE MyCert

Call below select statements to show the keys and certs are there. They are. Master DB Key, Symmetric Key and Certificate are all there.

SELECT * FROM sys.symmetric_keys 

SELECT * FROM sys.certificates 

Create a backup of the database certificate and key

Note I have tried putting them in the same folder and that did not work either.

BACKUP CERTIFICATE MyCert TO FILE = 'C:\SQLDatabase\MyCert\MyCert.cert'
WITH PRIVATE KEY ( FILE = 'C:\SQLDatabase\MyKey\MySymmetricKey.key' ,
ENCRYPTION BY PASSWORD = '12345' )

Drop the key and cert and verify they are gone.

DROP SYMMETRIC KEY MySymmetricKey
DROP CERTIFICATE MyCert;

There is no RESTORE for certificates only create by file. I call create certificate with th开发者_高级运维e WITH PRIVATE KEY.

When I run this the certificate shows up but the key does not come with it.

I have verified they are in the folders and SQL has access to the folders.

I have also tried the ALTER CERTIFICATE WITH PRIVATE KEY and still nothing.

What am I missing?

CREATE CERTIFICATE PayGoDBCert 
FROM FILE = 'C:\SQLDatabase\MyCert\MyCert.cert'
WITH PRIVATE KEY (FILE = 'C:\SQLDatabase\MyKey\MySymmetricKey.key' , 
DECRYPTION BY PASSWORD = '12345')

SELECT * FROM sys.symmetric_keys 
SELECT * FROM sys.certificates 

---

In your example you drop the symmetric keys but don't recreate it again.

Recreate the symmetric key again then restore the certificate.

Just a note:The password used to protect the backed up certificate is not the same password that is used to encrypt the private key of the certificate.

let us know if that solves your issue