SQL Backup 7

SQL Server error 18456 - login failed for user

A backup or restore task may fail with the following error:

SQL error 18456: Login failed for user <value>

There are two possible causes for this error.

If you have changed the authentication mode used by the SQL Backup Agent service from SQL Server authentication to Windows authentication, you may need to clear the ServiceLogin registry entry.

You will need to stop the SQL Server service briefly to do this.
  1. From the Windows Control Panel, select Administrative Tools, then Services.
  2. Find the SQL Server service for the relevant instance of SQL Server, called "SQL Server (<instance name>)".
  3. Right-click the SQL Server service and select Stop.
  4. Open the Registry Editor and locate the ServiceLogin registry entry:
    1. 32-bit systems: HKEY_LOCAL_MACHINE\SOFTWARE\Red Gate\SQL Backup\BackupSettingsGlobal\<instance name>
    2. 64-bit systems: HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Red Gate\SQL Backup\BackupSettingsGlobal\<iInstance name>
  5. Clear the data value for the ServiceLogin registry entry, but do not delete the entry itself.
  6. In Services, right-click the SQL Server service and select Start.

If the SQL Backup Agent service connects using SQL Server authentication, and you have changed the SQL Server account password, you need to update the password for the SQL Backup Agent service.

  1. Add the sqbsetlogin extended stored procedure from the SQL Backup extended stored procedure dynamic link library xp_sqlbackup.dll.
  2. Execute sqbsetlogin and specify the user name and new password.
  3. Remove the sqbsetlogin extended stored procedure when you have finished using it. (This step is optional but recommended.)

For example:

EXECUTE master..sp_addextendedproc sqbsetlogin, 'xp_sqlbackup.dll'
EXECUTE master..sqbsetlogin 'sa', 'newpassword'
EXECUTE master..sp_dropextendedproc sqbsetlogin

For more information on the sqbsetlogin extended procedure, see Permissions.

Didn't find what you were looking for?