SQL Backup Error : Cannot open backup device NT AUTHORITY\SYSTEM

Even if backing up database directly to remote server, some people often do this because they worry about the backup file loss in case of the DB machine crash. Sure better approach will be to backup DB to local disk and copy the .bak file to remote file share.
Anyway, the topic of this post is about database backup error 3201. SQL Server service was running as NT AUTHORITY\SYSTEM account and daily backup job was scheduled as SQL Agent job. Backup job directly backs up database to remote file share. When the job was run, the following error occurred.

Message Executed as user: NT AUTHORITY\SYSTEM. Backing up DB to \\BAKSERVER\DBBackups\DB_FULL_Daily_2013_02_01_14_00_00.bak... [SQLSTATE 01000] (Message 0) Cannot open backup device '\\BAKSERVER\DBBackups\DB_FULL_Daily_2013_02_01_14_00_00.bak'. Operating system error 5(Access is denied.). [SQLSTATE 42000] (Error 3201) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013)Unable to open Step output file. The step failed.

The error message is clear - access denied. The machine cannot access the remote file share.
This is because the BACKUP statement is run by SQL Server service account which is SYSTEM account and the SYSTEM cannot access remote fileshare. Even if SYSTEM account can do everything in local machine, it does not have permission to other machines.

So the possible solution might be (1) change SQL Server service account to other domain account (2) add machine account to a domain (or machine) group and give share permission to the group.

How to add machine account manually

1) Open Computer Management
2) Goto [Local Users and Groups] - [Groups]
3) Doubleclick a group you want to use
4) By default, [Computer] object type is unchecked, so you have enable the [Type].
5) Click [Object Types] button
6) Check [Computer] and OK



7) And then add machine name

How to add machine account by using script

The following is a VBScript example that adds a machine account to local Administrators group.

Set Netw = WScript.CreateObject("WScript.Network")
Local = Netw.ComputerName
DomainName = "MyDomain"
MachineAccount = "DBSERVER1"
Set Group1 = GetObject("WinNT://"& local &"/Administrators")
Group1.Add "WinNT://"& DomainName &"/"& MachineAccount &"$"