February 10, 2015 at 5:44 am
Hi All,
I have an issue to restore an encrypted backup via GUI.
I can restore an encrypted backup on another instance using t-sql command, but cannot do the same using the GUI of sql server 2014.
The message error is: No backupset selected to be restored.
I have restored the master key from the instance where the encrypted backup was created on the instance where I want to restore the encrypted backup, then restored the certificate. I managed to open the master key. All passed well...
I have the following versions:
On the instance where the encrypted backup is taken:
Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
Feb 20 2014 20:04:26
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
On the instance where I'm trying to restore the bakcup via GUI:
Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
Feb 20 2014 20:04:26
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )
Thanks,
Igor Micev,My blog: www.igormicev.com
February 10, 2015 at 6:15 am
I've had the same issue in the past and I can tell for sure it has nothing to do with the encryption: it's a random bug in the restore GUI in SSMS.
Anyway, a T-SQL script is preferrable in most cases, so not a big deal IMHO.
-- Gianluca Sartori
February 10, 2015 at 6:36 am
Thanks. At least I now know others have had the same issue. It should be reported to Microsoft to fix it.
But if you read this article you'll see that Perry manages to use the GUI after restored DMK and certificate - http://www.sqlservercentral.com/articles/Encryption/109028/
Igor Micev,My blog: www.igormicev.com
February 10, 2015 at 9:24 am
Igor Micev (2/10/2015)
Hi All,I have an issue to restore an encrypted backup via GUI.
I can restore an encrypted backup on another instance using t-sql command, but cannot do the same using the GUI of sql server 2014.
The message error is: No backupset selected to be restored.
I have restored the master key from the instance where the encrypted backup was created on the instance where I want to restore the encrypted backup, then restored the certificate. I managed to open the master key. All passed well...
I have the following versions:
On the instance where the encrypted backup is taken:
Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
Feb 20 2014 20:04:26
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
On the instance where I'm trying to restore the bakcup via GUI:
Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
Feb 20 2014 20:04:26
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )
Thanks,
Igor, you do not need to restore the DMK from the source instance, in fact its best you don't so they're unique. You do however, need to create a new DMK on the target instance if one doesn't already exist.
The only object required is a backup of the certificate and its private key. This must be restored on the target server before attempting to restore the encrypted database backup.
As shown in my article and to help further, can you perform the following without error on the backup file on the target server
RESTORE HEADERONLY FROM DISK = '?:\blah\blah\blah.bak'
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 11, 2015 at 12:47 am
Perry Whittle (2/10/2015)
Igor Micev (2/10/2015)
Hi All,I have an issue to restore an encrypted backup via GUI.
I can restore an encrypted backup on another instance using t-sql command, but cannot do the same using the GUI of sql server 2014.
The message error is: No backupset selected to be restored.
I have restored the master key from the instance where the encrypted backup was created on the instance where I want to restore the encrypted backup, then restored the certificate. I managed to open the master key. All passed well...
I have the following versions:
On the instance where the encrypted backup is taken:
Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
Feb 20 2014 20:04:26
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
On the instance where I'm trying to restore the bakcup via GUI:
Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
Feb 20 2014 20:04:26
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )
Thanks,
Igor, you do not need to restore the DMK from the source instance, in fact its best you don't so they're unique. You do however, need to create a new DMK on the target instance if one doesn't already exist.
The only object required is a backup of the certificate and its private key. This must be restored on the target server before attempting to restore the encrypted database backup.
As shown in my article and to help further, can you perform the following without error on the backup file on the target server
RESTORE HEADERONLY FROM DISK = '?:\blah\blah\blah.bak'
Thanks Perry, it works. I shouldn't have restored the DMK but create it as you proposed.
Igor Micev,My blog: www.igormicev.com
February 11, 2015 at 4:07 am
Igor Micev (2/11/2015)
Thanks Perry, it works. I shouldn't have restored the DMK but create it as you proposed.
😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 12, 2015 at 8:50 am
Igor Micev (2/10/2015)
I have restored the master key from the instance where the encrypted backup was created on the instance where I want to restore the encrypted backup
Just out of interest what was the exact command you used to restore the master key?
spaghettidba (2/10/2015)
it's a random bug in the restore GUI in SSMS.
Can see no evidence of this, got a link at all detailing this?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 12, 2015 at 9:22 am
Perry Whittle (2/12/2015)
Igor Micev (2/10/2015)
I have restored the master key from the instance where the encrypted backup was created on the instance where I want to restore the encrypted backupJust out of interest what was the exact command you used to restore the master key?
spaghettidba (2/10/2015)
it's a random bug in the restore GUI in SSMS.Can see no evidence of this, got a link at all detailing this?
RESTORE MASTER KEY
FROM FILE = 'E:\SQLServerBackup\SQL2014_DMK.key'
DECRYPTION BY PASSWORD = 'password'
ENCRYPTION BY PASSWORD = 'password';
OPEN MASTER KEY
DECRYPTION BY PASSWORD = 'password'
Igor Micev,My blog: www.igormicev.com
February 13, 2015 at 3:22 am
Hmm, I thought so. This is not exactly a bug, although BOL should explain a little better what happens when you restore a DMK :angry:
When you restore the DMK from a backup you have to actually open the key before you can use it, it's not automatically protected by the SMK. If you'd checked sys.databases using the following query you would have see the database name "master" and a value of zero. The key is not encrypted by the SMK
select db_name(database_id), is_master_key_encrypted_by_server
from sys.databases
where database_id = DB_ID('master')
This means the DMK has to be opened each and every time before using it, now since your restore GUI session didn't provide the following command
OPEN MASTER DECRYPTION BY PASSWORD = 'blahblah'
The restore session failed. To correct this, you should have used the following before attempting the restore
--Open the DMK
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'blahblahblah'
--now set encryption by the SMK
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
--Recheck sys.databases
SELECT DB_NAME(database_id),
is_master_key_encrypted_by_server
FROM sys.databases
WHERE database_id = DB_ID('master')
Rechecking sys.databases would have shown the value "master" followed by the value one.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 13, 2015 at 5:19 am
Perry Whittle (2/13/2015)
Hmm, I thought so. This is not exactly a bug, although BOL should explain a little better what happens when you restore a DMK :angry:When you restore the DMK from a backup you have to actually open the key before you can use it, it's not automatically protected by the SMK. If you'd checked sys.databases using the following query you would have see the database name "master" and a value of zero. The key is not encrypted by the SMK
select db_name(database_id), is_master_key_encrypted_by_serverfrom sys.databases
where database_id = DB_ID('master')
This means the DMK has to be opened each and every time before using it, now since your restore GUI session didn't provide the following command
OPEN MASTER DECRYPTION BY PASSWORD = 'blahblah'The restore session failed. To correct this, you should have used the following before attempting the restore
--Open the DMKOPEN MASTER KEY DECRYPTION BY PASSWORD = 'blahblahblah'
--now set encryption by the SMK
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
--Recheck sys.databases
SELECT DB_NAME(database_id),
is_master_key_encrypted_by_server
FROM sys.databases
WHERE database_id = DB_ID('master')
Rechecking sys.databases would have shown the value "master" followed by the value one.
That's what I missed to check, ... and because security is not my best friend, but we're progressing our friendship :). Thanks.
Igor Micev,My blog: www.igormicev.com
February 13, 2015 at 9:08 am
no problem, you're welcome
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply