SQL error code 18456

RussFRussF Posts: 25
edited October 9, 2006 1:49PM in SQL Backup Previous Versions
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

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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.
  • Hi Brian,

    Thanks, sorted.

    Russ
This discussion has been closed.