1

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?

Ben Pilbrow
  • 12,041
  • 5
  • 36
  • 57
user41313
  • 11
  • 2

2 Answers2

3

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.
K. Brian Kelley
  • 9,034
  • 32
  • 33
  • 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
2

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.
Remus Rusanu
  • 8,283
  • 1
  • 21
  • 23