Changing sa password breaks SQL Backup

brianh123

If I change the password for "sa", SQL Backup is no longer able to back up any databases on that server. For the life of me I can't figure out why it needs to use sa when its service account is an admin, nor can I figure out where this password is stored in SQL Backup. Please help...

petey

The SQL Backup Agent service can access SQL Server in 2 ways: via Windows authentication where it will use the credentials of the service startup user, and via SQL authentication.

In your case, it appears that the service has been set up to use SQL authentication. To configure the service to use Windows authentication, do the following:

sp_addextendedproc sqbsetlogin, 'xp_sqlbackup.dll'  --  this extended stored procedure is not installed by default

EXEC master..sqbsetlogin '', ''

sp_dropextendedproc sqbsetlogin  --  remove this so users don't get curious about it

If you need to use SQL authentication, run sqbsetlogin with the relevant user name and password e.g.

EXEC master..sqbsetlogin 'sa', 'rugbyrules'