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