CODEWITHSUNDEEP
sql-serverdatabase-administrationsql-server-2017
GeorgeM
GeorgeM

Reputation: 21

How to fix "The master key file does not exist or has invalid format." in SQL Server 2017

The environment I am using is a VM hosted on Azure.

I recently created a new instance of SQL Server on that VM called TESTINSTANCE (in addition to the existing default one). I used a separate service account.

Subsequently I need to restore a database backup to the new instance. In the source a master key and certificate had been created.

So before restoring, I think need to recreate that key in my destination instance (TESTINSTANCE).

When I trey the below however: 

-- Create a database master key on the destination instance of SQL Server.   
USE master;  
GO  

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'value_a';  
GO

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'value_a';   
go

RESTORE MASTER KEY   
    FROM FILE = 'C:\folder_\masterdb_masterkey'   
    DECRYPTION BY PASSWORD = 'value_b'   
    ENCRYPTION BY PASSWORD = 'value_a';  
GO

I am getting is the following error message:

The master key file does not exist or has invalid format.

I believe this may be caused by the permissions of the service account that has been created during my installation of TESTINSTANCE.

However when I check the Windows Privileges and Rights section of the page below, my understanding is that the TESTINSTANCE service account should get the same permissions as the default one.

https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions?view=sql-server-2017

So I'm not sure why it can't access that location in the C drive. To confirm, the file does exist in 'C:\folder_\masterdb_masterkey'

Upvotes: 1

Views: 3263

Answers (1)

GeorgeM
GeorgeM

Reputation: 21

The owner of the folder (C:\folder_) was a user that had been deactivated from the account. I updated the owner to my account by updating the owner to my user in the Advanced Security Settings as shown in the answer to this question.

Then I reran the query, and it worked.

https://dba.stackexchange.com/questions/112368/sql-server-data-folder-access-rights

Upvotes: 1

Related Questions