Sql-server – My Backups Broken

backupservice-accountssql server

Our SQL server was running with a Domain Admin user for the SQL Service account.
Our backups are accomplished with a script: 

DECLARE @name VARCHAR(50) -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name

-- specify database backup directory
SET @path = '\\<remote server>\CADSQL_BU\' 

-- specify filename format
SELECT @fileDate = 'Midnight'

DECLARE db_cursor CURSOR FOR  
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

WHILE @@FETCH_STATUS = 0   
BEGIN   
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
       BACKUP DATABASE @name TO DISK = @fileName  WITH  INIT ,  NOUNLOAD ,  NAME = @filename,  SKIP ,  STATS = 10,  NOFORMAT

       FETCH NEXT FROM db_cursor INTO @name   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor

I changed the SQL Server service account to ‘Network Service’ account (for whatever ‘good’ reasons) and found (days later) that the backup jobs weren’t working … with this error:

Message
Executed as user: NT AUTHORITY\NETWORK SERVICE. Cannot open backup device '\\CADSQL_BU_Test\ReportServer_Midnight.BAK'. Operating system error 67(The network name cannot be found.). [SQLSTATE 42000] (Error 3201)
BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.

The is just another server 5 feet away.
How will I fix this?

I gave the remote location ‘Network Service’ ‘rights’ to the file location, but I believe that is a service account local to that computer and is not really the SQL service account.

Best Answer

Create a domain user that has read/write privileges to both the network share as well as NTFS read/write permissions on the folder.

In Configuration Manager, set the SQL Agent user to this domain user. SQL Agent is the account that will execute the backup as a task. The user does not have to be a domain admin, as long as it has the NTFS permissions it needs. You can also make it an Active Directory Managed Service Account, but that's probably down the road from now.

You should be able to keep the SQL Server Service user as the local NETWORK SERVICE account, though if you were to run this script from SSMS, it will probably still fail. You'll want to schedule a one-time backup job from within the SQL Server Agent jobs in SSMS to get a real test of the configuration.

Another option altogether would be to backup to a local drive and have a Windows scheduled task move the file to the network share. This would prevent the backup from failing if it couldn't reach the other machine for any reason.

EDIT :

For those just finding this, you should know that on any machine when it uses its local SYSTEM or NETWORK SERVICE account, other systems see it as the machine name with a dollar sign ($) after it.

E.g. :

MyDomain\Computer1
User: NETWORK SERVICE

MyDomain\Computer2
User: MyDomain\Computer1$

It even works on machines that are not on a domain at all (so long as they can reach each other on the network).

Related Question