2

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?

enter image description here

enter image description here

2
  • Your SQL service is running under NT account, its safer to use domain account for this kind of access issue. As you can see, message says access is denied, cross-check whether secbackup account has read-write permission on the shared folder or not. Dec 28, 2020 at 11:30
  • @Learning_DBAdmin I know about that. That is not a problem to me. As I said before, that account has full permission on that shared folder. Plus it works when I change [NT Service\MSSQLSERVER] service account to secbackup. I do not understand why it does not work with sql server agent service account Dec 28, 2020 at 11:54

2 Answers 2

5

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.

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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