Sql-server – No permissions to the certificate file when trying to restore a DB from a third party

permissionsrestoresql server

I am trying to restore a Db in SQL server 16. It is a developer version installed on windows 10 laptop. We have a backup file from a SaaS instance. We have moved to a new instance of the SaaS and this is our archive from the first instance. This is strictly for archiving purposes. That said, the only was to read this of course is with SQL Server.

Script:

use master ;
 -- drop master key encryption by password ='somepassword';

-- create   master key encryption by password ='somepassword';

create certificate BULLHORN14852_45764 from file 
    = 'C:\Program Files\Microsoft SQL Server\MSSQL13.LEVVEL\MSSQLBULLHORN14852_45764.cer'
with private key 
  (file='C:\Program Files\Microsoft SQL Server\MSSQL13.LEVVEL\MSSQLBULLHORN14852_45764.pvk',
decryption by password = 'somepassword')

I was able to create the master key encryption no problem. When I run the create certificate command I get the famous

"The certificate, asymmetric key, or private key file is not valid or does not exist; or you do not have permissions for it."

I am connected as the local administrator on windows. I have also granted permissions on the folders and the files.

NETWORK SERVICE account does have permission to the folder and the cert, pvk files.

When I look at the services and what the Logon as is I see the following.

SQL Server (LEVVEL) - logon as NT Service\MSSQL$LEVVEL

SQL Server (MSSQLSERVER) - logon as NT Service\MSSQLSERVER


SQL Server Agent (LEVVEL) - logon as NT Service\SQLAgent$LEVVEL

SQL Server Agent (MSSQLSERVER) - logon as NT Service\SQLSERVERAGENT

The granted permissions on the folder (and files ) are: NETWORK SERVICE and MSSQL$LEVEL. I can't find any user names starting with SQL or MSS or NT in order to add other users to the permissions.

I am not sure what I am missing.

I've been reading that these permissions above are not enough. I haven't administered a SQL DB in 15 years so I need some guidance. Any help would be greatly appreciated!

Rob.

Best Answer

It seems you have two SQL Server instances installed. LEVVEL and a default instance. Which one are we talking about? I.e., which one are you connected to?

Anyhow, both services uses a virtual service account, so privileges assigned to NETWORK SERVICE is irrelevant.

For the default instance, you should assign privileges to NT Service\MSSQLSERVER.

The the LEVVEL, you should assign privileges to NT Service\MSSQL$LEVVEL.

When you assign privileges, you need to type the above account name.

Related Question