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.