Here's my predicament:
- The version on the server is SQL Server Express. And no, we cannot use any other version. (Long story, but: vendor will not allow without $ome...)
- From 1, you can deduct that there is no SQL Server Agent jobs. Hence the need to automate SQL Server backups "creatively".
- We did so years ago, and it was working perfectly for years (more than 2).
- What did we do? We created the command string
sqlcmd -U SQLBackupOper -P <password> -S GSUDM -Q "EXEC sp_BackupDatabases".
sp_BackupDatabasesis a stored procedure in the Master database that backs up all DBs.
- Yes, if I login to the server and open an elevate (as administrator) command prompt and run the command in number 4, it works perfectly. So, the password is correct. I copied and pasted several times to make sure, and no, the problem is not the password.
- Yes, the login SQLBackupOper is mapped to all DBs and has the role membership of db_backupoperator.
- We also created a task in the Windows Task Scheduler that is meant to run the command string in number 4, and it worked for years. Now, for some indeterminate time (I am not sure how long) the task is failing. When I look at the SQL Server error logs I am greeted with the error that reads
Login failed for user 'SQLBackupOper'. Reason: Password did not match that for the login provided.
- Yes, the password in the command string is correct. I tested it from the command prompt via copy and paste. Again, it works from the command prompt. It only fails if run from the task scheduler, the same exact command string.
- Yes, I did try varying the user that runs the job. Still failing.
- Yes, I do run the task with the highest privileges.
With that said, has anyone faced this problem before? Again: It works from an elevated command prompt, but it complains when using the same command string citing wrong password when run as a task in task scheduler. Thoughts? Suggestions?