Upgrade to 2016 SP 1 - Restores don't work anymore

WindyCityGirl

Hello

We have upgraded to SQL Server 2016 SP 1 and SQL Backup version 8

when we run the restores, they fail with the following message.

Executed as user: XXXXX SQL Backup failed with exit code: 890

SQL error code: 15157 the database principal 'XXXXXXDEV01$' 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.

I cant find where this is coming from so I can correct it. Where do I need to go to correct the LOGIN the tool is using?

Thank you!
Kim

squigley

Thanks for your inquiry about SQL Backup.
I would recommend checking the account running the SQL Backup Agent service in Windows Services.
If using the restore wizard in the GUI, the logged in user of the GUI is impersonated, check this user account.
If a scheduled job, check the job owner.

I would recommend making sure you have correct permissions for the SQL Backup Agent Service: http://documentation.red-gate.com/displ ... ermissions
Let me know if you have any questions or concerns.

WindyCityGirl

Thank you for your reply.

I have tried all of the suggestions from the link you provided, without success. What does seem to work if I add the database user name parameter in the job as follows.

@database_name=N'master',
@database_user_name=N'dbo',
@flags=0
@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

I must be missing something -

W

WindyCityGirl

I should have also said, all other (non SQL Backup) jobs are running from start to finish - its only the SQL Backup jobs that are having this issue.

W

squigley

Hellow W,

When you run the sqlbackup extended stored procedure, SQL Backup will first check the backup/restore rights of the user account requesting the task, it uses the SETUSER function to impersonate the logged on user account. For scheduled tasks this is the owner of job or the SQL Agent account, for ad-hoc backup or restore tasks using the Backup or Restore wizards in the GUI, this is the account running the GUI.

In SQL 2008 and higher, the SETUSER function maybe disabled or the account authentication will fail due to an issue known as 'double hop authentication', and has to do with how user security is set up on your Windows domain.

To work around this, you can disable the security check that SQL Backup performs by creating the SkipChecks registry key. To create the SkipChecks key, using Regedit navigate to the following Registry Folder:
HKEY_LOCAL MACHINESoftwareRed GateSQL BackupBackupSettingsGlobal(LOCAL) or <SQL Instance Name>
Create a new DWORD type registry key called SkipChecks and give this new key a data value of 1.
Stop and restart the SQL Backup Agent service.

Once the registry key has been created, SQL Backup will skip the user right checks for backup and restore tasks.

Let us know if this helps with your issue.