| Author |
Message |
RemRod
Joined: 11 May 2007
Posts: 4
|
 Posted: Fri May 11, 2007 12:26 pm Post subject: RedGate Backup job always fails |
 |
|
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 |
|
 |
petey
Joined: 24 Apr 2005
Posts: 2218
|
| Posted: Sun May 13, 2007 5:35 am Post subject: |
|
|
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 |
|
|
RemRod
Joined: 11 May 2007
Posts: 4
|
| Posted: Mon May 14, 2007 12:08 pm Post subject: |
|
|
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 |
|
|
petey
Joined: 24 Apr 2005
Posts: 2218
|
| Posted: Tue May 15, 2007 4:38 am Post subject: |
|
|
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 |
|
|
RemRod
Joined: 11 May 2007
Posts: 4
|
| Posted: Tue May 15, 2007 3:00 pm Post subject: |
|
|
Great Thanks Peter, that worked.
-Chris |
|
| Back to top |
|
|
paulppp2004
Joined: 25 Jan 2006
Posts: 6
Location: Georgia
|
| Posted: Wed Aug 01, 2007 9:07 pm Post subject: Are these SQL commands always needed? |
|
|
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 |
|
|
Brian Donahue
Joined: 23 Aug 2004
Posts: 6345
Location: Red Gate Software
|
| Posted: Thu Aug 02, 2007 9:14 am Post subject: |
|
|
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
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521 |
|
| Back to top |
|
|
SQL_ME_RICH
Joined: 08 May 2012
Posts: 58
|
| Posted: Thu May 31, 2012 9:20 pm Post subject: Re: |
|
|
| 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 |
|
|
Brian Donahue
Joined: 23 Aug 2004
Posts: 6345
Location: Red Gate Software
|
| Posted: Fri Jun 01, 2012 9:15 am Post subject: |
|
|
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' |
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521 |
|
| Back to top |
|
|
|
