x

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!
more ▼

asked May 14, 2012 at 12:51 PM in Default

jhowe gravatar image

jhowe
1.1k 50 57 61

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...
May 14, 2012 at 02:31 PM jhowe

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?
May 14, 2012 at 02:36 PM JohnM
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first
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.
more ▼

answered May 14, 2012 at 02:39 PM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

(comments are locked)
10|1200 characters needed characters left

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!
more ▼

answered May 14, 2012 at 02:20 PM

JohnM gravatar image

JohnM
6.8k 1 3 7

na... Shawn edited my post and removed them for some reason. I have been using backslashes...
May 14, 2012 at 02:27 PM jhowe
I figured as much, but I still thought that I'd point it out. ;-) You can completely disregard my answer then. =)
May 14, 2012 at 02:32 PM JohnM
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1839
x586
x9

asked: May 14, 2012 at 12:51 PM

Seen: 1536 times

Last Updated: May 14, 2012 at 04:16 PM