Can't take backup to the shared folder

Question

Need your help. My sql job is not able to take backup to the shared folder although the domain account, under which the sql agent service account is running, has full permission on that shared folder.

In addition to that, my sql service account uses default virtual login. So when I change that account to the domain account that I also used for sql agent service account, then the problem is fixed.

Why can't I take backup to shared folder using sql agent service account?

Answer 1

The service account for Agent is irrelevant. All Agent does it to tell SQL Server to produce a backup file. It is SQL Server that produces the backup file, hence it is the service account for the SQL server service that matters. You can't do anything about that.

So, either run SQL Server using a domain account; or use the virtual service account and assign privileges on the share to the domain computer account.

Answer 2

Thats my powershell code

$user = "sa" # SQL username
$pass = Get-Content -Path C:\Scripts\pass.txt | ConvertTo-SecureString # SQL user password as securestring
$Cred = New-Object System.Management.Automation.PSCredential ($user, $pass) # Credentials
$sqlHost = "<ip address>"
$databases = Get-SqlDatabase -ServerInstance $sqlHost -Credential $Cred
foreach($db in $databases){
    $a = $db.Name #Database name
    $backupFileName = "\\<shared folder path>\\"+$a+".bak"
    Write-Host $backupFileName
    Backup-SqlDatabase -ServerInstance $sqlHost -Database $a -BackupFile $backupFileName -Credential $Cred
}

This script can Iexecute in Task Scheduler