What are the challenges you face when working across database platforms? Take the survey
Options

Cannot login to do backup

jlehewjlehew Posts: 6
edited June 1, 2006 1:10PM in SQL Backup Previous Versions
I setup Red-Gate on our second cluster but cannot get any backups to run because it says it cannot log in. The first cluster is working great. I thought the username and password it uses for backups were stored in the Windows Sql Backup Agent service which started successfully. If you put in an incorrect password into the service it will not start. Here is the output from the backup job:

Thank you,
John

====================

Job 'test atmon' : Step 1, 'Step 1' : Began Executing 2006-05-31 08:47:10
SQL Backup (DLL v4.1.0.207)
Login failed for user 'usapsys\sqladmin'.
SQL error code: 18456
name value

exitcode 0
sqlerrorcode 18456
filename01 G:\LAVSQLSRV\Backup\<AUTO>
Msg 50000, Sev 16: SQL Backup job failed with exitcode: 0 SQL error code: 18456 [SQLSTATE 42000]

Comments

  • Options
    peteypetey Posts: 2,358 New member
    Is it possible that you have set up the service app to log in to SQL Server using SQL authentication? Look in the help file, under the topic 'Using SQL Backup', on how to configure the service app to connect using Windows authentication and SQL authentication.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    I have not used the sqbsetlogin command so to my knowledge I'm using Windows authentication. If a wrong password is put into the service, it does not start so I know it is using Windows authentication and the service starts and is running. The username sqladmin we are using has sa privs on Sql.

    I think it may have something to do with the install on the cluster. It is somewhat of a manual process.

    John
  • Options
    peteypetey Posts: 2,358 New member
    Using sqbsetlogin is not the only way to use SQL authentication. During the installation process, there are options to set how the SQL Backup service will log in to SQL Server. This may have been incorrectly set.

    To check, see if there is any data in the following registry value:

    HKEY_LOCAL_MACHINE\Software\Red Gate\SQL Backup\InstalledInstances\<instance name>\ServiceLogin
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    There is some data in that reg key. Is there any way to reset how it logs on without reinstalling?

    John
  • Options
    peteypetey Posts: 2,358 New member
    If you're feeling lucky, delete the ServiceLogin key, otherwise

    sp_addextendedproc sqbsetlogin, 'xp_sqlbackup.dll'
    exec master..sqbsetlogin '', ''
    sp_dropextendedproc sqbsetlogin
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.