RedGate Backup job always fails

Forum for users of Red Gate SQL Backup tool

Moderator: Chris Auckland

RedGate Backup job always fails

Postby RemRod » Fri May 11, 2007 12:26 pm

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
RemRod
 
Posts: 4
Joined: Fri May 11, 2007 12:21 pm

Postby petey » Sun May 13, 2007 5:35 am

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 7
petey
 
Posts: 2234
Joined: Sun Apr 24, 2005 12:34 pm

Postby RemRod » Mon May 14, 2007 12:08 pm

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
RemRod
 
Posts: 4
Joined: Fri May 11, 2007 12:21 pm

Postby petey » Tue May 15, 2007 4:38 am

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.

Code: Select all
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.

Code: Select all
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 7
petey
 
Posts: 2234
Joined: Sun Apr 24, 2005 12:34 pm

Postby RemRod » Tue May 15, 2007 3:00 pm

Great Thanks Peter, that worked.

-Chris
RemRod
 
Posts: 4
Joined: Fri May 11, 2007 12:21 pm

Are these SQL commands always needed?

Postby paulppp2004 » Wed Aug 01, 2007 9:07 pm

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
paulppp2004
 
Posts: 6
Joined: Wed Jan 25, 2006 3:06 pm
Location: Georgia

Postby Brian Donahue » Thu Aug 02, 2007 9:14 am

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).
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Re:

Postby SQL_ME_RICH » Thu May 31, 2012 9:20 pm

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.

Code: Select all
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.

Code: Select all
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
SQL_ME_RICH
 
Posts: 64
Joined: Tue May 08, 2012 10:41 pm

Postby Brian Donahue » Fri Jun 01, 2012 9:15 am

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.
Code: Select all
EXEC master..sp_addextendedproc sqbsetlogin, 'xp_sqlbackup.dll'
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am


Return to SQL Backup Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests