My Backups Broken

by Ross   Last Updated January 13, 2018 22:06 PM

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.



Answers 2


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).

Greg
Greg
September 14, 2015 19:41 PM

NT AUTHORITY\NETWORK SERVICE has a SID on your machine that is unique from all copies of that account on all other servers in your network (there are some instances where setting the password to the same machine can work to sync local/domain accounts or same-named accounts within a workgroup, but I don't think you can do that for certain system accounts). I don't know if I've ever seen an environment where the service account was a local system account and it was able to write directly to network shares.

If you want the SQL Server Agent service account to have usable permissions on shares in other servers in your domain, it will have to be a domain account and, of course, that account will have to have sufficient privileges to read and write to those shares.

Aaron Bertrand
Aaron Bertrand
September 14, 2015 22:03 PM

Related Questions


Sql clustering sql server account

Updated November 16, 2017 17:06 PM