Backup database on local pc

Question

I am using this script to backup database on computer A:

sqlcmd -U MyDatabase -P MyPassword -S .\SQLEXPRESS -Q "EXEC sp_BackupDatabases @backupLocation = 'c:\shared', @databaseName='MyDatabase', @BackupType='F'"   

everything works fine, but I decided to store backup files to computer B. I shared folder on computer B, which is accessible from:

\\computerB\shared

I changed backup script to include new backup location:

sqlcmd -U MyDatabase -P MyPassword -S .\SQLEXPRESS -Q "EXEC sp_BackupDatabases @backupLocation = '\\computerB\shared', @databaseName='MyDatabase', @BackupType='F'"   

But when I run this, I am getting an error:

Cannot open backup device '\computerB\shared\MyDatabase_FULL_101 22022_232734.BAK'. Operating system error 5(Access denied .). Msg 3013, Level 16, State 1, Server WIN-C28934ASNF1\SQLEXPRESS, Line 1 BACKUP DATABASE is terminating abnormally.

I don't understand why am I getting Access denied. I can easly eccess \\computerB\shared from file explorer on computer A. What is wrong there?

Answer 1

I can easly eccess \computerB\shared from file explorer on computer A.

When you access a share from file explorer as you did, your credentials are passed to the remote computer, but when you execute the backup command either SQL Server service account or the computer account is passed to access the share. If the share security has no entry allowing the proper account, you might get the error message you described.

A computer account in your case would be something like YourDomain\ComputerA$. You can verify which account is being used checking event viewer on ComputerB filtering by event id 4624: