SQL Backup exit code 586

Bill Wychor

I have a server (XXXXXXXX) we have been running SQL Backup 5 on for months without any problems. We are now having failures returning the following:

Windows authentification
Failed to connect to SQL Server instance: XXXXXXXX
SQL Backup exit code: 586

The service SQL Backup Agent logs on with the same user id as MSSQLSERVER and SQLSERVERAGENT services.

I have uninstalled and reinstalled SQL Backup5. The only change I am aware of is that the password for the user id has been changed. I know the password on the services is correct because I can stop and restart them without any problems.

I get the same error "586" if I try backup using the GUI or as a sql command "exec master..sqlbackup N'-SQL "BACKUP DATABASE..."
via Enterprise manager on the server.

It does not matter which database on the server I try to backup they all fail.

priyasinha

Hi,

Did you use 'SQL Server Authentication' mode that the service application will use to log in to the SQL Server while installing the server components? If this is the case then the easiest way to fix this problem is to uninstall the server components and then install it again with the correct password.

Please read this post for more information http://www.red-gate.com/messageboard/viewtopic.php?p=30300&highlight=586

Thanks,
Priya

Bill Wychor

Thanks for the reply.

When I tried 'SQL Server Authentication' it did not pass the test. The server is steup for mixed mode and I tried 3 different user ids all of which work when connecting to the server with via Enterprise Manager.

When I used Local System account in the setup I get the following message.

Unspecified error: This installation may not be able to install the SQl Backup extended stored procedures. Use another login.

priyasinha

Sorry Bill, I should have explained it more clearly.

SQL Backup has a client installation and then you install server components for each of the SQL Server instance. If you look at 'Add and Remove' program then you can see the server components installed separately for each of your SQL Server instance. You can find the server components installer in your client Install directory and it is named as SQBServerSetup.exe.

When you install server components for your SQL Server instance using SQBServerSetup.exe then you get an option to 'Select the authentication mode that the service application will use to log into SQL Server'. So this is the option which I meant you to change. It is independent of the set up which you have done in your Enterprise Manager or SQL Backup GUI.

So you have two options here now:
1. Either use sqbsetlogin as explained here http://www.red-gate.com/supportcenter/Content.aspx?p=SQL%20Backup&c=knowledgebase%5cSQL_Backup%5cKB200701000003.htm
Or
2. Go to your 'Add and Remove' Program and uninstall the server components for this instance. Run SQBServerSetup.exe again and then enter the correct password.

Hopefully this will resolve your problem.

Bill Wychor

I executed exec sqbsetlogin with the user id and the new password
the message returns (0 row(s) affected). I tried runing the SQBServerSetup.exe and got the message "Failed to check that the SQL Backup extended stored procedures were installed."

and a second time with EXEC sqbsetlogin '',''

the message returns (0 row(s) affected) again and again "Failed to check that the SQL Backup extended stored procedures were installed."

I ran SQL Profiler monitoring the sql and saw Reads 0 Writes 0 for the exec sqbsetlogin .

While connected as a domain admin using the domain admin user and password I uninstall the componets via add remove programs the event viewer shows uninstall successful. When I then reinstall I get the message: "Failed to check that the SQL Backup extended stored procedures were installed."

I verified that the SQL Backup Agent service was using the same domain admin user when I tried this.

I tried with creating a new SSA user id with sysadmin and still have the same errors.

Where do I find what the "sqbsetlogin" login id that is stored somewhere that it is expecting to authenticate against?

I appreciate your help

Nigel Morse

Hi Bill,
Sorry you're having problems still.

Were you originally using windows authentication to connect the service to sql server?

I think the best thing to is to make sure the server components are fully uninstalled before reinstalling and that should allow to re-enter the accounts and get the setup straightened out

First remove the SQL Backup 5 (server components) for the instance you're interested in (or the only one if you have only one instance). However make sure you choose No on the dialog asking if you want to remove all the activity history or you'll lose all the data of previous backups

Then check the binn directory of the instance (e.g. mine is C:\Program Files\Microsoft SQL Server\MSSQL\Binn ) and make sure xp_sqlbackup.dll has been removed

Then re-run the server components setup and it should ask for users to run the service as, and next which type of authentication to use.

If all this fails I suggest we ask one of our support guys to help out and see if they can fix it.

Bill Wychor

I found out a coworker of mine had installed SQL Native Client for SQL Server 2005 on the server just prior to all the SQL backups failing. Prior to that only SQL Server 2000 and it's drivers were installed.

I uninstalled SQL Native Client and then successfuly installed SQL Backup Server Componets. Then I stopped and restarted the SQL Backup Agent service and now I can successfully complete the SQL backups.

priyasinha

Its great to hear that it works fine for you now. We will investigate this scenario further in-house to verify why this happened.

Thanks,
Priya