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****
User name : sa
Failed to connect to SQL Server instance: SQLServer****
SQL Backup exit code: 586
SQL error code: 18456
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 –
If for any reason that doesn’t work at all then try the following which fixed the problem in my case –
- 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 switch from the default Windows Authentication to SQL Authentication.
- Stop the SQL Backup Agent Service.
- Edit the ServiceLogin registry key and delete the data value, so the data value is blank.
- Restart the SQL Backup Agent Service.
- Retry performing the backup or restore task.