I'm just starting to experiment with encrypting backups, I think I now understand it and have got the process working, but something strange is happening with restoring.
On Server A (SQL 2014 Standard) I have created a new master key and certificate and successfully produced an encrypted bak file and have also exported the key and cert files.
On Server B (SQL 2014 Developer) I have successfully restored the master key and certificate from Server A.
I know these are working fine as I can restore the encrypted bak file successfully to both servers using the syntax:
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'xxx'; RESTORE DATABASE testDb FROM DISK = 'C:\testDb.bak' WITH STATS = 5; CLOSE MASTER KEY;
But it's not the same story when using the Restore Database tool in SSMS.
It works fine when restoring to Server A.
But when using the UI to restore to Server B, it just gives a basic message of 'No backupset selected to be restored'.
Why would this be?
UPDATE: I've just noticed when using the commands to restore the backup, it will only work on Server B if I open the master key first otherwise it gives the message
Please create a master key in the database or open the master key.
On Server A I do not need to do this, it's as though the key is always open on Server A