Login Failed for 'sa'

wkl471

Apologies if this question has been posed previously, but I have a curious error that I need to rectify. We are using SQLBackup 6.2 on a SQL Server 2005 SP3 QA box. The backups have been failing for some time, and they are locking the 'sa' user. My concern is that RedGate should not be using 'sa' to log into the SQL Server, but I cannot find in the RG UI where to verify which account SQLBackup is actually using. Obviously it is sa, but I am not sure were to set it, or how it was set to use 'sa'. Previous forums explain to use sqbsetlogin to reset to Windows Auth. Is that still the case? Where can I verify the account to be used?

petey

The only way to set up SQL Backup to use a SQL login is via the sqbsetlogin extended stored procedure. The GUI doesn't expose this setting.

To reset it, just run sqbsetlogin with empty parameters e.g.

EXEC master..sqbsetlogin '', ''

You will then need to ensure that the SQL Backup Agent service account is a member of the SQL Server sysadmin fixed server role.

To check if SQL Backup has been set up to use a SQL login, look in the registry key HKLM\Software\Red Gate\SQL Backup\BackupSettingsGlobal\<instance name>, for the value 'ServiceLogin'. If its not empty, that means SQL Backup is using a SQL login to perform backups and restores.

wkl471

Thank you Petey! I verified the registry setting, and installed/ran/uninstalled the sqbsetlogin extended SP. I refreshed my registry view, and the value that was there was gone. I tested by running our backup job, and the backups succeeded. I really appreaciate your prompt reply!