SQL error code 18456

RussF

Hi,

I am trying to restore a DB using SQL backup and get the error as per the subject line. The rest of the error is as follows:

Login failed for user 'sa'.

BUT, I can do a restore on the DB using plain old SQL.

- Changed 'sa' password
- Registered the server on SQL backup using 'sa', connects perfectly

I have other SQL instances on the same server which restore perfectly with SQL backup.

Please advise.

Thanks

Brian Donahue

Hi Russ,

This happens occasionally as a result of installing the software and telling it to use SQL authentication, then the password changes. To change the password for the SQL authentication that the SQL Backup Agent service uses, open a Query Analyzer session or Management Studio query and change the database context to MASTER and run this:

exec sp_addextendedproc 'sqbsetlogin','xp_sqlbackup'
exec sqbsetlogin 'sa','password'
/*If you want to use default Windows authentication instead, run this: */
/*exec sqbsetlogin '','' */

This will set the SQL Authentication used by the SQL Backup Agent Service. If the user and password are blank, then the service will log into the SQL Server as the login account that the service runs under.

RussF

Hi Brian,

Thanks, sorted.

Russ