Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mxjoe35
Starting Member
2 Posts |
 Posted - 2011-09-02 : 09:20:13
|
| 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 CertificateCREATE 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 keyNote 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 the WITH PRIVATE KEY.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 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? |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
|
|
mxjoe35
Starting Member
2 Posts |
Posted - 2011-09-02 : 10:26:09
|
| Russell thanks for the reply. Not a bad suggestion, but I was trying to encrypt my key with my cert...which was encrypted by my DB Master key. If I can't get it working my way, I could do it this way. |
 |
|
|
|
|
|
CREATE CERTIFICATE WITH PRIVATE KEY