RedGate Backup job always fails

RemRodRemRod Posts: 6
edited June 1, 2012 4:15AM in SQL Backup Previous Versions
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
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    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?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • RemRodRemRod Posts: 6
    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
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • RemRodRemRod Posts: 6
    Great Thanks Peter, that worked.

    -Chris
    · Share on Twitter
  • paulppp2004paulppp2004 Posts: 7
    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
    PP
    · Share on Twitter
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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).
    · Share on Twitter
  • SQL_ME_RICHSQL_ME_RICH Posts: 112
    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
    · Share on Twitter
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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'
    · Share on Twitter
Sign In or Register to comment.