1

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.

  • 2
    '\\CADSQL_BU_Test\ReportServer_Midnight.BAK is not a valid name. It needs a share name in there between the server name and the file name. – Max Vernon Sep 14 '15 at 19:07
  • @rossi, yes, due to service account privilege you are getting this error. Because you service account in domain. so, Consult with your system administrator team. – Md Haidar Ali Khan Sep 14 '15 at 19:13
  • There could be multiple problems but Max one of them for sure is what @MaxVernon stated. Go to that location manually outside of SQL Server, do you see the file? Put in the right path then try again. If it doesn't work then go focus on permissions. – Ali Razeghi Sep 14 '15 at 19:23
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).

  • Thank you for your answer. I created a domain acct and am using it. It appears to have fixed the problem. Thank you. – Ross Sep 16 '15 at 13:31
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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