When attempting to perform a backup via Redgate SQL Backup it fails instantly with the following error, this started to occur after being forced to change the sql logins password as far as I can tell but most likely coincided with changing from Windows to Windows & SQL Authentication on the database –

localhost: Backup - Failed
==========================
Performing transaction log backups - Failed
-------------------------------------------
This operation failed with errors.
 
SQL error 18456: Login failed for user 'sa'.
Server : SQLServer****
SQL authentication
User name : sa
Failed to connect to SQL Server instance: SQLServer**** 
SQL Backup exit code: 586
SQL error code: 18456   

---

Common Fix

Before trying the fix I’ll put further down which solved my issue I would suggest following Redgate’s own support article on this specific issue. Although it didn’t work for me, it should presumably fix most peoples issues –

documentation.red-gate.com/sbu7/errors-and-warnings/sql-server-errors/sql-server-error-18456-login-failed-for-user

If for any reason that doesn’t work at all then try the following which fixed the problem in my case –

Other Fix

1. 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>
2. Locate the registry key called ‘ServiceLogin’.
3. This Registry Key is first created when you switch from the default Windows Authentication to SQL Authentication.
4. Stop the SQL Backup Agent Service.
5. Edit the ServiceLogin registry key and delete the data value, so the data value is blank.
6. Restart the SQL Backup Agent Service.
7. Retry performing the backup or restore task.