SQL Server error 18456 - login failed for user
Published 18 February 2013
A backup or restore task may fail with the following error:
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.
- From the Windows Control Panel, select Administrative Tools, then Services.
- Find the SQL Server service for the relevant instance of SQL Server, called "SQL Server (<instance name>)".
- Right-click the SQL Server service and select Stop.
- Open the Registry Editor and locate the ServiceLogin registry entry:
- 32-bit systems: HKEY_LOCAL_MACHINE\SOFTWARE\Red Gate\SQL Backup\BackupSettingsGlobal\<instance name>
- 64-bit systems: HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Red Gate\SQL Backup\BackupSettingsGlobal\<iInstance name>
- Clear the data value for the ServiceLogin registry entry, but do not delete the entry itself.
- 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.
- Add the sqbsetlogin extended stored procedure from the SQL Backup extended stored procedure dynamic link library xp_sqlbackup.dll.
- Execute sqbsetlogin and specify the user name and new password.
- Remove the sqbsetlogin extended stored procedure when you have finished using it. (This step is optional but recommended.)
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.