RedGate Backup job always fails

RemRod

I have 4 production servers that all have redgate sql back installed. 3 of them run great and I never had any problems with them. The 4th one I have never been able to run it successfully.

Here is the error messages I am receiving.

Executed as user: AD_Domain\UserName. SQL Backup job failed with exitcode: 0 SQL error code: 18456 [SQLSTATE 42000] (Error 50000). The step failed.

I am saving all 4 backups to the AD_Server using a shared directory. The user I am running the job under has Full Rights to this directory for the server in question.

Can someone elaborate as to what this error is and if there is anything I can do to fix it.

Thanks in advance.

-Chris

petey

SQL error code 18456 indicates that the SQL Backup Agent failed to log in to SQL Server to perform the backup.

Can you run a test backup manually using Query Analyzer or Management Studio? Does the backup succeed, or do you get the same error?

RemRod

No, I tried using the Query analyzer to do a RedGate back up and it failed for the same reason.

I also tried to use the RedGate GUI and it failed also.

I also tried to change the log in for the SQL Back up Service but it failed as well and it failed trying to use the original user name not the one I changed the service to or who I am logged in as?

-Chris

petey

The SQL Backup Agent service logs in to SQL Server using either Windows authentication or SQL Server authentication.

The default mode is Windows authentication, where the SQL Backup Agent service will log in using the credentials of the service startup user.

To log in using SQL Server authentication, you need to use the sqbsetlogin extended stored procedure e.g.

EXEC master..sp_addextendedproc sqbsetlogin, 'xp_sqlbackup.dll'
EXEC master..sqbsetlogin 'sa', 'sapassword'
EXEC master..sp_dropextendedproc sqbsetlogin

This will set up the SQL Backup service to log in using the sa user. To revert back to Windows authentication, run sqbsetlogin with blank values e.g.

EXEC master..sqbsetlogin '', ''

Try running sqbsetlogin without any values, in case the SQL Backup Agent service has been set up to use SQL Server authentication. Then try running a backup, after setting up the service startup user to be an account you know has sysadmin rights to SQL Server.

RemRod

Great Thanks Peter, that worked.

-Chris

paulppp2004

I was having the same problems doing RedGate backups on SQL2005. After I followed Peter's directions, it got corrected. Thank you much! The question I have though if if these SQL commands need to be run every time you reboot the server. I would love to get some information, if possible, on what they mean exactly. Thanks a lot. Pab

Brian Donahue

Hi,

The sqbsetlogin stored procedure causes the SQL Backup Agent Service to us SQL accounts to connect to the database rather than the default Windows credentials that the server runs under. If you have set the service to use SQL authentication in this way, the username and password is encrypted and saved in the registry.

You wouldn't need to run this procedure again unless you change your SQL Server's sa password (or whatever account you had specified).

SQL_ME_RICH

petey wrote:

The SQL Backup Agent service logs in to SQL Server using either Windows authentication or SQL Server authentication.

The default mode is Windows authentication, where the SQL Backup Agent service will log in using the credentials of the service startup user.

To log in using SQL Server authentication, you need to use the sqbsetlogin extended stored procedure e.g.

EXEC master..sp_addextendedproc sqbsetlogin, 'xp_sqlbackup.dll'
EXEC master..sqbsetlogin 'sa', 'sapassword'
EXEC master..sp_dropextendedproc sqbsetlogin

This will set up the SQL Backup service to log in using the sa user. To revert back to Windows authentication, run sqbsetlogin with blank values e.g.

EXEC master..sqbsetlogin '', ''

Try running sqbsetlogin without any values, in case the SQL Backup Agent service has been set up to use SQL Server authentication. Then try running a backup, after setting up the service startup user to be an account you know has sysadmin rights to SQL Server.

This appears to be the solution to a problem I am having in getting my SQL Backups to run (they keep erroring out saying that they cannot log in with the sa account, and that the password is wrong). This makes sense as I just recently changed the sa password on 2 diiferent servers and instances, and now this won't work - but I really do not want SQL Authentication being used to run the RedGate service/backup with anyway. So - I tried to run the code above to reset it back to Windows Authentication, but all I get is a 'Could not find stored procedure 'master...sqbsetlogin'. If this is a PROC - it's definitely not in the master db of the instance I am trying to fix...Does this work for SQL Backup 7? I am using SQL Backup 7 (7.0.5.1).

Thanks

Brian Donahue

As it mentions in the post that you quoted, it is necessary to install sqbsetlogin manually, as it is not configured with the shipped version of SQL Backup.

EXEC master..sp_addextendedproc sqbsetlogin, 'xp_sqlbackup.dll'