Backups for one database fail on SQL Server 2019 instance

TonyW

We are running SQL Backup version 10.1.24.2187. We have 3 SQL Server instances running on a Windows VM: Dev, Test and UAT. Dev and Test are configured for SQL Backup full and transaction log backups, running on a schedule. For Dev, all databases are backed up successfully as scheduled. For the Test instance, backups for 1 of 18 databases fails every time the schedule runs; the other 17 backups run successfully every time. The following errors are entered into the SQL Backup log for this one database backup that fails (while nothing is written to the SQL Server logs):

10/6/2023 9:00:01 AM: Error 880: BACKUP DATABASE permission denied in database:  (PIVService)

10/6/2023 9:00:01 AM: SQL error 916: The server principal "cld\gss_npsql" is not able to access the database "PIVService" under the current security context.

The account in the error message is a Windows AD account and is a SQL Server login for the Test instance, only assigned to the public server role. We tried assigning it the sysadmin role, but the same error occurred. 

The SQL Server Agent service is configured to log in with this account, while the SQL Backup Agent service is configured to log in with a different Windows AD account that is also a SQL Server login and set as sysadmin. 

We have Dev and Test configured the same for SQL Server Agent and SQL Backup services. 

Any help would be appreciated.

Tony

TonyW

Well, after tinkering some more with settings and permissions, the backup is now working. I assigned the cld\gss_npsql login sysadmin permissions and it now works. However, on the Dev instance, this login does not have sysadmin permissions and works (with similar database configurations). I don't understand, but now it works. 

I have another question that came up during all of this testing. Why does SQL Backup use the account configured for the SQL Server Agent service instead of the account configured for the SQL Backup agent service?

Dan_J

Hi @TonyW

Thanks for reaching you to us regarding this! Thanks also for the update, glad you were able to resolve the initial issues.

Regarding the question around the account used, would you mind confirming if you use the SQL Backup UI to schedule jobs, or if you are creating these jobs through just SSMS?

TonyW

I had other backups failing with the same errors as in my first post; we resolved them by adding the Skipchecks registry entry. 

For the answer to your question, I am using the Redgate SQL Backup UI to create the job schedules. 

Dan_J

Hi @TonyW,

Thanks for your reply on this.

Regarding your question on the account SQL Backup uses, I started to put together a reply to this but it is probably best to have the full context outlined in the permissions page of the SQL Backup documentation, which can be found here: https://documentation.red-gate.com/sbu/permissions#Permissions-UsingSQLBackupProfromtheextendedstoredprocedure

If you do have any questions after reviewing this document, please don't hesitate in coming back to us.

TonyW

Thanks, Dan. I'll check out the article.

Tony