7

Our DBA is out and I'm trying to back up our (remote) dev database to a UNC path. I'm running the back up operation while logged into Management Studio as a particular Windows domain account (e.g. CORP\myUser). I've checked that this domain account has FULL CONTROL rights the UNC path \\nameOfMachine\backup\.

When I attempt to execute the following as CORP\myUser

BACKUP DATABASE [DEV] TO  DISK = N'\\nameOfMachine\backup\dev.bak' WITH COPY_ONLY, INIT;

I receive the error

Cannot open backup device '\\nameOfMachine\backup\dev.bak'. 
Operating system error 5(Access is denied.).

Which seems to indicate that whatever account SQL Server is using to run the backup command does not have rights to that UNC path.

I do not have a way to RDP to the dev database server and find out which account SQL Server is using to execute the backup command above. Is there a way to find this out from Management Studio using e.g. a dynamic management view?

5
  • 1
    Try this to find SQL Server service account: select servicename, startup_type_desc, service_account from sys.dm_server_services;
    – donger
    Aug 2, 2016 at 17:54
  • 1
    run this to figure out what you have first: SELECT * FROM fn_my_permissions(NULL, 'SERVER');
    – donger
    Aug 2, 2016 at 18:00
  • Would you try Arthur's suggestion backing up to local drive then copy to network share? Instead of trying to figure out the service account with all these issues.
    – donger
    Aug 2, 2016 at 18:14
  • 1
    Your account can't but the SQL Server Service account should be able to backup to local drives. After that you will still need to find an account with access to copy the files out.
    – donger
    Aug 2, 2016 at 18:22
  • @donger this is why I was backing up to a UNC share that my account does have access to.
    – T. Webster
    Aug 2, 2016 at 18:24

3 Answers 3

6

If you run a BACKUP query under a certain Windows/SQL Server account, it is actually the SQL Server Agent account which "does" the BACKUP and needs full control permissions to the UNC path. So you must be able to allow that account (or Everyone) have full control permissions to that path. To find out which account SQL Server Agent runs as, query sys.dm_server_services. It requires that you (or someone else) have VIEW SERVER STATE permission on the server.

5

BACKUP will access the UNC under an impersonation context. This is a 'double-hop' situation and requires Kerberos constrained delegation to be configured.

This is already answered at https://stackoverflow.com/questions/18749224/restore-database-from-a-shared-folder

4

Do you have the option of creating a folder on your local computer and create a share on that and grant full authority to everyone and then back up to that share? - you didn't indicate how big the backup might be

2
  • You could do this. Alternatively, if you have admin permissions on the UNC share, then you could temporarily allow "Everyone" read/write access to the share until the backup is copied.
    – Gary
    Aug 2, 2016 at 19:23
  • @Scott, I credited your answer +1 because it is one decent way I could have easily solved the problem, but I posted the actual solution I found.
    – T. Webster
    Aug 2, 2016 at 19:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.