Restore database from a shared folder

1
votes

I did a backup of a database on Prod. I need to restore it to cert. I am Running below query on Cert to access the backup residing on prod

Query:

USE [master]
RESTORE DATABASE DB
FROM  DISK = N'\\SQL2005Prod\Backups\DB\DB.bak' WITH  FILE = 1,  
GO

Error:

Msg 3201, Level 16, State 2, Line 3
Cannot open backup device '\\SQL2005Prod\Backups\DB\DB.bak'. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.

Additional notes: I am a sys admin on the server. SQL Server Agent and I have full access to the folder Backups. SQL Server Agent account and SQL server Service account are under the same domain name. They have full control under folder permissions.

1
sql-serverbackuprestore
Is there any problem using the Sql Server management studio Restore wizard???. BTW your problem is with your path. Try some physical path like C:\, D:\ if you have permissions - user240141
@Amit that is not true at all. - Aaron Bertrand
@Amit your comment makes it sound like a permission error is the end of the world, and that's all there is to it. Permission errors can be resolved (you know, by granting the right permissions to the right accounts). - Aaron Bertrand
You talk about SQL Server Agent and you. What about the SQL Server service account? What account is it using? If it's a local account, how exactly have you granted it permissions to the remote share? (Hint: a local account is not considered to be inside of "Everyone" in the domain or on the remote machine.) - Aaron Bertrand
There is a disconnect in the Query SQL above and the Error message below it. The SQL references \\SQL2005Prod\ but the Error message references \\SQL2005\ as the server names. - user121489

1 Answers

3
votes

SQL Server accesses external resources under:

  • impersonation, if the original login is an NT login
  • service account, if the original login is a SQL login w/o a mapped credential
  • credential, if the original login is a SQL login that has a mapped credential

So the user which actually accesses the share will depend on how you logged in to SQL at the moment you issued the RESTORE statement. Note also that if NT login is used then normal constrained delegation rules for impersonation are in place (aka. 'kerberos double hop', if your original login occured on a different LSA than the one hosting the SQL Server service and the target resource resides on a host different from the SQL Server service host then you'll be authenticated by the target resource's LSA as 'anonymous' unless you explicitly configure delegation for that resource).

PS. SQL Agent adds 'proxies' to the mixture, but those are nothing else but credentials (case 3 in my list). See Create a SQL Server Agent Proxy.