SQL Backup Unable to Login to Perform Backup or Restore

Category: Troubleshooting & error messages
Date: 20 Mar 2009
Product: SQL Backup
When attempting to perform a backup or restore task, the job may fail with the following Error:

SQL error 18456: Login failed for user 'OtherServername\Username'.
  Server : ThisServername
  SQL authentication
  User name : OtherServenamer\Username
Failed to connect to SQL Server instance: ThisServername

SQL Backup exit code: 586
SQL error code: 18456

This error may occur after switching the SQL Backup Agent Service from using a SQL Authenticated account back to a Windows Authenticated using sqbsetlogin Extended Stored Procedure.

To resolve this problem, locate the following Registry Key:

32-bit systems
HKEY_LOCAL_MACHINE\SOFTWARE\Red Gate\SQL Backup\BackupSettingsGlobal\(LOCAL) or <SQL Instance Name>

64-bit systems
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Red Gate\SQL Backup\BackupSettingsGlobal\(LOCAL) or <SQL Instance Name>

Locate the registry key called 'ServiceLogin'

This Registry Key is first created when you first switch from the default Windows Authentication to SQL Authentication. When switching back to use Windows Authentication again the data value recorded which is an obfuscated value is not removed.

1. Stop the SQL Backup Agent Service.
2. Edit the ServiceLogin registry key and delete the data value, so the data value is blank.
3. Restart the SQL Backup Agent Service.

Retry performing the backup or restore task.