Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Hey guys, I have sql05 and my maintenance plan which backs up a database to an external storage SAN, has been failing every night.
Here is my error:

Executing the query "BACKUP DATABASE [master] TO  DISK = N'\\\\192.168.x.x\\vmbackup\\server\\dbbackup\\master_backup_201004222300.bak' WITH NOFORMAT, NOINIT,  NAME = N'master_backup_20100422230002', SKIP, REWIND, NOUNLOAD,  STATS = 10
" failed with the following error: "Cannot open backup device '\\\\192.168.x.x\\vmbackup\\server\\dbbackup\\master_backup_201004222300.bak'. Operating system error 5(Access is denied.).
BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I googled this error and tried adding permissions to the backup device for network service as recommended at experts exchange, no dice.

Does anyone have any ideas?

share|improve this question
add comment

2 Answers

What service account is SQL Server running under (you can check this in SQL Server Configuration Management or in Services.MSC)? That account will need permissions on the share and the folder where the file is being written to. A couple of provisos:

  • If the SQL Server is running under Network Service, this will map to Domain\Computer$ where Domain is the domain the SQL Server is in and Computer is the name of the server SQL Server is installed upon.
  • If the SQL Server is running under System (Local System), it can't do this because the System account does not map outside the computer as of Windows Server 2003.
  • If the SQL Server is running under Computer\User (not a domain account, but an account local to the server), then it faces the same problem as running under System.
share|improve this answer
    
Thanks for the reply. Checked the sqlserver config menu. All services except SQL server integration service are running under local system. The SQL server integration service is running under NT authority /Network service. Looking back in my logs, this backup was working at one time. I checked the user id on successful backups and unsuccessful backups, the id is the same: S-1-5-18. This user also has permissions on the share drive where i am sending the backups. Any ideas? –  user41313 Apr 23 '10 at 14:52
    
Also noticed event ID changed for backup job. Was 18264 now is 18204. –  user41313 Apr 23 '10 at 15:00
    
Has the service account been changed recently? Try changing it to a domain user account created specifically for the SQL Server to run under (use SQL Server Configuration Management to make the change once you have the account), give that account the proper rights to the share/folder, and that should fix the issue. On a related note, because System has even more rights on the local server than even a member of the Administrators group (it has certain implicit rights), it's not recommended to use this account as the service account for SQL Server. –  K. Brian Kelley Apr 23 '10 at 15:08
    
Which service? Im concerned editing this will affect production. sql intergration services? sql server sql analysis services sql server browser sql server agent sql full text search. –  user41313 Apr 23 '10 at 15:33
    
It should be the SQL Server service itself. –  K. Brian Kelley Apr 23 '10 at 17:59
add comment

You are going across the network to a shared folder \\192.168.x.x\vmbackup therefore you need to consider network authentication rules:

  • if the maintenance job connects to SQL Server using SQL authentication then the SQL Server process will connect to the network resource as the service account, and the localsystem service account will authenticate itself across the network as the machine account. So permission needs to be granted to the 'domain\machine$'. Permissions needs to be granted both on the share and on the individual file(s).
  • if the maintenance job connects to SQL Server using Windows authentication (aka 'Trusted connection' or 'integrated security') then the SQL server process will access the network resource under an impersonated context. Permission has to be granted on the network resource to the account that runs the maintenance job (eg. the SQL Agent account). Permissions needs to be granted both on the share and on the individual file(s). Furthermore, if the maintenance job is started by a client connecting remotely (from a different host than the SQL Server host) then delegation occurs and the SQL Server account must be configured for constrained delegation, see Kerberos Protocol Transition and Constrained Delegation.
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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