Options
using Managed Service Accounts to restore across servers via SQL Backup
jonesric
Posts: 25 Bronze 1
in SQL Backup
Hi,
I have SQL Backup 10.1 taking a backup of a SQL2017 database on serverA, via a scheduled job, to a local drive and to a cloud storage location.
I have created a restore job via the SQL Backup GUI, on serverB, which points to the aforementioned local backup location of serverA and restore it to serverB (also SQL 2017) for reporting purposes.
The backup job works like a charm but the restore job fails with:
SQL Backup failed with exit code: 890 SQL error code: 15157 [SQLSTATE 42000] (Error 50000). The step failed.
which when i look in the log, expands with the below:
"Setuser failed because of one of the following reasons: the database principal xxxxxxxx does not exist, its corresponding server principal does not have server access, this type of database principal cannot be impersonated, or you do not have permission."
My SQL Agent account on serverB has permissions to the backup location on serverA (and I have re-started the Agent service since the permissions were added) and is a sysadmin on serverA.
The SQL Agent account is also a managed service account - is this the issue? Whilst the database principal might (does?) exist, is the issue with the "cannot be impersonated" part of the error?
Thanks.
I have SQL Backup 10.1 taking a backup of a SQL2017 database on serverA, via a scheduled job, to a local drive and to a cloud storage location.
I have created a restore job via the SQL Backup GUI, on serverB, which points to the aforementioned local backup location of serverA and restore it to serverB (also SQL 2017) for reporting purposes.
The backup job works like a charm but the restore job fails with:
SQL Backup failed with exit code: 890 SQL error code: 15157 [SQLSTATE 42000] (Error 50000). The step failed.
which when i look in the log, expands with the below:
"Setuser failed because of one of the following reasons: the database principal xxxxxxxx does not exist, its corresponding server principal does not have server access, this type of database principal cannot be impersonated, or you do not have permission."
My SQL Agent account on serverB has permissions to the backup location on serverA (and I have re-started the Agent service since the permissions were added) and is a sysadmin on serverA.
The SQL Agent account is also a managed service account - is this the issue? Whilst the database principal might (does?) exist, is the issue with the "cannot be impersonated" part of the error?
Thanks.
Tagged:
Answers
my server admin has helped me resolve this. If anyone is looking for this in the future, the MSA had nothing to do with it - my restore path was looking for an admin share on serverA when the SQL Agent account wasn't a local admin. I've pointed the Agent account to the non-admin share and the restore is working as expected.