Red Gate forums :: View topic - RedGate Backup job always fails
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Backup Previous Versions
SQL Backup Previous Versions forum

RedGate Backup job always fails

Search in SQL Backup Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
RemRod



Joined: 11 May 2007
Posts: 4

PostPosted: Fri May 11, 2007 12:26 pm    Post subject: RedGate Backup job always fails Reply with quote

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
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2277

PostPosted: Sun May 13, 2007 5:35 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
RemRod



Joined: 11 May 2007
Posts: 4

PostPosted: Mon May 14, 2007 12:08 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2277

PostPosted: Tue May 15, 2007 4:38 am    Post subject: Reply with quote

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:
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:
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
Back to top
View user's profile Send private message Send e-mail
RemRod



Joined: 11 May 2007
Posts: 4

PostPosted: Tue May 15, 2007 3:00 pm    Post subject: Reply with quote

Great Thanks Peter, that worked.

-Chris
Back to top
View user's profile Send private message
paulppp2004



Joined: 25 Jan 2006
Posts: 6
Location: Georgia

PostPosted: Wed Aug 01, 2007 9:07 pm    Post subject: Are these SQL commands always needed? Reply with quote

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
Back to top
View user's profile Send private message Yahoo Messenger
Brian Donahue



Joined: 23 Aug 2004
Posts: 6580

PostPosted: Thu Aug 02, 2007 9:14 am    Post subject: Reply with quote

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).
Back to top
View user's profile Send private message
SQL_ME_RICH



Joined: 08 May 2012
Posts: 60

PostPosted: Thu May 31, 2012 9:20 pm    Post subject: Re: Reply with quote

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:
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:
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
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6580

PostPosted: Fri Jun 01, 2012 9:15 am    Post subject: Reply with quote

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:
EXEC master..sp_addextendedproc sqbsetlogin, 'xp_sqlbackup.dll'
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group