question

jhowe avatar image
jhowe asked

cannot attach encrypted database

Hi people, I've got the following problem. We had our instance of SQL setup for SQL 2012 CTP3 on our dev environment. Now that SQL 2012 is out, we uninstalled the previous instance. Now that I have sql installed i'm trying to reattach some of the databases. I have the following items : Certificate, Password and Private key. When I try and run the following script i am getting the error : USE MASTER GO CREATE CERTIFICATE CERTIFICATE FROM FILE = 'C:\Users\me\Desktop\TDE\Certificate.cer' WITH PRIVATE KEY (FILE = 'C:\Users\me\Desktop\TDE\Private_Key.pvk', DECRYPTION BY PASSWORD = 'password'); The certificate, asymmetric key, or private key file is not valid or does not exist; or you do not have permissions for it. Can someone please help me with what i'm missing? Thanks... Update : I have been messing around with permissions etc. I have now managed to create my certificate from the files, but i still cannot attach the database. I am getting Cannot find server certificate with thumbprint '0xD7AABE7C1B2A50A85BEC01AC5F204B77339492AC'. I'm giving up... i think somehow we've got a certificate mix up... thanks for your help anyway, off to create a new dev environment and backup certificates!
sql-server-2008sql-server-2008-r2tde
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I'm wondering whether sql can't see the files for some stupid reason, what service does sql use to access files is it NETWORK SERVICE? just so i can check permissions etc...
0 Likes 0 ·
I was just thinking the same thing that it appears to me that it might be a security thing. The CREATE CERTIFICATE code looks correct to me. Also, do you want to create the cert in Master?
0 Likes 0 ·
WilliamD avatar image
WilliamD answered
It sounds like you don't have permission to access the folder where the certificate is. Make sure the SQL Server service account has access rights to the folder and try again.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM avatar image
JohnM answered
Not sure if this will fix it, but you are missing the "\\" in the path for the certificate & key. That might have been just a formatting thing when you posted the question. USE MASTER GO CREATE CERTIFICATE CERTIFICATE FROM FILE = 'C:\UsersmeDesktopTDECertificate.cer' WITH PRIVATE KEY (FILE = 'C:\UsersmeDesktopTDEPrivate_Key.pvk', DECRYPTION BY PASSWORD = 'password'); Hope this helps!
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

na... Shawn edited my post and removed them for some reason. I have been using backslashes...
0 Likes 0 ·
I figured as much, but I still thought that I'd point it out. ;-) You can completely disregard my answer then. =)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.