title: SQL Server 2005 create certificate with private key / encryption
body: <p>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.</p>
  
  <p>Create the Database Master Key. </p>
  
  <pre><code> CREATE MASTER KEY ENCRYPTION BY PASSWORD = '12345'
  </code></pre>
  
  <p>Create the Certificate</p>
  
  <pre><code>CREATE CERTIFICATE MyCert 
  WITH SUBJECT = 'My First Certificate', 
  EXPIRY_DATE = '1/1/2199';
  </code></pre>
  
  <p>Create a symmetric key that is encrypted with MyCert.</p>
  
  <pre><code>CREATE SYMMETRIC KEY MySymmetricKey 
  WITH ALGORITHM = AES_256 
  ENCRYPTION BY CERTIFICATE MyCert
  </code></pre>
  
  <p>Call below select statements to show the keys and certs are there.
  They are. Master DB Key, Symmetric Key and Certificate are all there.</p>
  
  <pre><code>SELECT * FROM sys.symmetric_keys 
  
  SELECT * FROM sys.certificates 
  </code></pre>
  
  <p>Create a backup of the database certificate and key</p>
  
  <p><strong>Note</strong> I have tried putting them in the same folder and that did not work either.</p>
  
  <pre><code>BACKUP CERTIFICATE MyCert TO FILE = 'C:\SQLDatabase\MyCert\MyCert.cert'
  WITH PRIVATE KEY ( FILE = 'C:\SQLDatabase\MyKey\MySymmetricKey.key' ,
  ENCRYPTION BY PASSWORD = '12345' )
  </code></pre>
  
  <p>Drop the key and cert and verify they are gone.</p>
  
  <pre><code>DROP SYMMETRIC KEY MySymmetricKey
  DROP CERTIFICATE MyCert;
  </code></pre>
  
  <p>There is no RESTORE for certificates only create by file. I call
  create certificate with the WITH PRIVATE KEY.</p>
  
  <p>When I run this the certificate shows up but the key does not come with it.</p>
  
  <p>I have verified they are in the folders and SQL has access to the folders.</p>
  
  <p>I have also tried the ALTER CERTIFICATE WITH PRIVATE KEY and still nothing.</p>
  
  <p>What am I missing?</p>
  
  <pre><code>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 
  </code></pre>