Backups stop working when the SQL account passwords are changed

Category: Troubleshooting & error messages
Date: 20 May 2009
Product: SQL Backup
If SQL Backup is configured to log into the SQL Server using SQL authentication, any backup or restore operations may fail with

"SQL error 18456: login failed for user sa"

...where "sa" is the SQL account configured for SQL Backup using the sqbsetlogin extended stored procedure. If this error occurs and references a SQL security account, the reason for the failure may be that the "sa" account's password has been changed in SQL Server and the corresponding password change had not been performed for the SQL Backup Agent Service. If the referenced account is a Windows account, then the SQL Backup Agent Service's startup account password should be changed in Windows Control Panel's Administrative Tools area.

By default, the SQL Backup Agent Service connects to the SQL Server instance using Windows authentication. If you want to connect using SQL authentication, use the sqbsetlogin extended stored procedure to provide the login name and password. You will need to add the stored procedure before you use it, and you are recommended to remove it when you have finished using it. For example:

exec sp_addextendedproc sqbsetlogin, 'xp_sqlbackup.dll' / add the extended stored procedure to the server /
exec sqbsetlogin 'sa', 'sqbpassword' / set SQL Backup Agent Service to log in as user "sa" /
exec sp_dropextendedproc sqbsetlogin / remove the extended stored procedure from the server /

To revert to Windows authentication, call sqbsetlogin with blank values:

/ assuming sqbsetlogin has already been added and not removed /
exec sqbsetlogin '', ''

If you encounter errors related to permissions and access rights, ensure that the startup user for SQL Backup Agent Service application has been granted the necessary permissions. All known rights necessary to run backups using SQL Backup are documented at:

http://www.red-gate.com/supportcenter/Content.aspx?p=SQL%20Backup&c=knowledgebase/SQL_Backup/KB200710000173.htm