Restoring Server Certificate to new instance

Question

Restoring Server Certificate to new instance

winston Smith

SSCoach

Points: 19498
More actions
December 29, 2011 at 2:11 pm

#248285

I have two instances, InstanceA running under accountA, and InstanceB running under accountB.
Both instances are on the same machine.
I have a cert on InstanceA. It and its Private key are backed up:
use master;
go
BACKUP CERTIFICATE MyServerCert TO FILE = 'C:\SQLServerFiles\myServerCert.cer'
WITH PRIVATE KEY ( FILE = 'C:\SQLServerFiles\privateKey.pvk' ,
ENCRYPTION BY PASSWORD = 'Password1' );
GO
I now try to restore this to InstanceB. I create a database master key and then run code to restore the cert:
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password1';
go
--this runs successfully
use master;
go
CREATE CERTIFICATE MyServerCert
FROM FILE = 'C:\SQLServerFiles\myserverCert.cer'
WITH PRIVATE KEY
(
FILE = 'C:\SQLServerFiles\privatekey.pvk'
,DECRYPTION BY PASSWORD = 'Password1'
);
GO
I now get the error:
Msg 15208, Level 16, State 1, Line 1
The certificate, asymmetric key, or private key file does not exist or has invalid format.
If i change the service account of InstanceB to accountA, i dont get any issues. Can anyone explain why this is?
Update: I granted accountB admin rights on the server ( its only a local dev machine) but this still did not help, although it rules out any question of if the account has access to the directory that contains the cert and private key
To fix, I clicked advanced and changed the file owner to mw\globalitsqladmins (my group).( I could haveselected myself as owner instead). This is where I typedglobalitsqladmins:
Then clicked ‘enableinheritance’ on next screen (which applies all the permissions the cert folderhas to the file(s):

Then was able to create the cert.

winston Smith SSCoach Points: 19498 More actions · Answer 1

Success!

After adding the accountB to the administrators group, i still could not restore the cert.

I had an idea that perhaps sql service was still operating under the credentials of the account BEFORE i added it to the admin group.

I restarted the sql service and then i could restore the cert.

Next question: If i change the permissions of the sql service account, is there any way to ensure those new permissions take effect, without restarting the sql service?

Perry Whittle SSC Guru Points: 233866 More actions · Answer 2

So instance A and B were running under separate user accounts, correct?

Are you moving a TDE protected database from one instance to another?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

winston Smith SSCoach Points: 19498 More actions · Answer 3

Yea,I was taking a backup of an encrypted db on one instance and restoring it to a second instance.

And yes, each instance SQL srvc is running under a different account.

Perry Whittle SSC Guru Points: 233866 More actions · Answer 4

As I pointed out on your last post you made on this topic, you only need the backup of the database and the backup of the certificate. Books online details the steps required to restore a TDE protected database to a new instance.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Perry Whittle SSC Guru Points: 233866 More actions · Answer 5

See my reply to your last post on this subject

http://www.sqlservercentral.com/Forums/Topic1205594-1526-2.aspx

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

winston Smith SSCoach Points: 19498 More actions · Answer 6

That procedure relies on a detatch andreattach of the db, so it's not really a backup and restore of a tde database. It's moving of a tde database.

I was trying to do this via backup and restore.

Perry Whittle SSC Guru Points: 233866 More actions · Answer 7

winston Smith (1/8/2012)
That procedure relies on a detatch andreattach of the db, so it's not really a backup and restore of a tde database. It's moving of a tde database.
I was trying to do this via backup and restore.

Is there a difference, are you sure?????

An attach or a restore both run a create database statement, there is no difference 😉

Now to the problem itself

winston Smith (12/29/2011)
I now get the error:
Msg 15208, Level 16, State 1, Line 1
The certificate, asymmetric key, or private key file does not exist or has invalid format.

it's 99.9% likely that accountb does not have permissions to read the cert backup files in C:\SQLServerFiles, the following is an indication of this

winston Smith (12/29/2011)
If i change the service account of InstanceB to accountA, i dont get any issues. Can anyone explain why this is?

This means nothing

winston Smith (12/29/2011)
Update: I granted accountB admin rights on the server ( its only a local dev machine) but this still did not help, although it rules out any question of if the account has access to the directory that contains the cert and private key

Actually go to the NTFS permissions for the folder in question and check the permissions, grant accountb read access it'll work 😉

I can re produce your exact error message at will by adjusting permissions 😎

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

freeman99z Grasshopper Points: 13 More actions · Answer 8

Its a file permssions issue. Select proerpties on the cert and pvk files.

yash.viva93 Grasshopper Points: 13 More actions · Answer 9

yash.viva93

Grasshopper

Points: 13

January 28, 2020 at 1:32 pm

#3720901

Thanks, Winston Smith.

It works for me.