Changing sa password breaks SQL Backup

brianh123brianh123 Posts: 8
edited October 4, 2007 12:41AM in SQL Backup Previous Versions
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...
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    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'
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
Sign In or Register to comment.