Login failed for user 'sa' / Database "XXX" not found

OS

Windows

Software

All

Error message

[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'sa'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '[username]'.
No database file found for 'MSSQL\(local)\XXX'
Database "XXX" not found
Backup missed.

Cause

This happens when the user or password used for the SQL-server access is incorrect or the user does not have backup rights.
The MS SQL-backup works as follows:

1. The backup task is started by the scheduler service, the backup process will start using the Windows-authentication provided.
2. The process will then start a SQL client, which logs onto the SQL-server with the provided SQL-username and matching password.
3. The SQL client will request the SQL-server to export the selected databases to the temporary directory.
4. Here it will compare the export with the data present on the backup server and then upload the difference to the backup server.

Solution

In the MSSQL-backupset a SQL-username and password have to be provided, and optional a Windows log on as well for the temporary directory.

1. Open the software
2. Go to the backupsets
3. Choose the correct backupset
4. Under the 'General' tab supply the Windows authentication and MSSQL server authentication
5. Click 'OK'
6. Save the settings / Close the software

The backup rights of a user can be checked using the MSSQL Management Studio

1. Start the MSSQL Management Studio
2. Login using the same credentials used in the backup software
   • In case this failes you will need to use the sa user to gain enough rights to make changes.
3. Select the user in the Management Studio ([SERVER]\Security\Logins)
4. Selecteer the user mapping option
5. Check here if the user has the db_backupoperator parameter set for the databases that need to be backupped.
   • This rights apply to each user and each database individually!
6. Check the box in front of the db_backupoperator parameter for each database that should be included in the backup.
7. Apply the changes

Alternativly you can also create a seperate SQL user using the Management Studio who has enough rights to make a backup.