0

Here's my predicament:

  1. 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...)
  2. From 1, you can deduct that there is no SQL Server Agent jobs. Hence the need to automate SQL Server backups "creatively".
  3. We did so years ago, and it was working perfectly for years (more than 2).
  4. What did we do? We created the command string sqlcmd -U SQLBackupOper -P <password> -S GSUDM -Q "EXEC sp_BackupDatabases".
  5. Yes, sp_BackupDatabases is a stored procedure in the Master database that backs up all DBs.
  6. 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.
  7. Yes, the login SQLBackupOper is mapped to all DBs and has the role membership of db_backupoperator.
  8. 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.
  9. 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.
  10. Yes, I did try varying the user that runs the job. Still failing.
  11. 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?

  • 1
    Does the password have any special characters? Maybe try with something simple to rule that out. – Jacob H Aug 20 at 21:19
  • Look over detail that may help Task Scheduler wise in my answer here: superuser.com/questions/1005192/… and see if such simple overlooked settings, etc. help any. Otherwise, simple run as SYSTEM from task scheduler and see if that does it. – Pimp Juice IT Aug 21 at 1:46
  • @JacobH: Yes, it does have special characters. And no, changing the password to something simple with no special characters did not work. – user1596979 Aug 21 at 19:43
  • @PimpJuiceIT: Yeah, I did try pretty much everything in the link you provided. Tks. Nothing worked. But, I got it to work by creating brand new users, both at the machine level as local admin and SQL Server as sys admin. So, something related to permissions, but I cannot pinpoint what. Cheers! – user1596979 Aug 21 at 19:46
  • Probably needs execute permission to the stored proc and also the db_backupoperator role assigned to the account for each DB the SP backs up. I assume SQL Express has those roles but if not, then perhaps execute to the SP and db_owner on the applicable DBs. I don't have an SQL Express to test but those are my quick thoughts but sysadmin is definitely overkill but if you're not concerned with that, then that'll work as well obviously. – Pimp Juice IT Aug 21 at 20:35
0

Thanks guys I resolved the problem. Although I'm not sure what I did that resolved it. What I did:

Created a brand new SQL Server login with sysadmin rights (I know it shouldn't need that much, but hey, I needed to fix this.) Added a new local user login in users and computers MMC in the SQL Server hosting the machine, and made it a local admin to the machine. Updated everything to use those users, instead of what we had before, and suddenly it started working. So, it's something related to permissions, most likely for the SQL Server login. But still, as far as I can tell the initial user had everything it needed. So much so that I could confirm that to be the case both using a command line as well as a batch file.

Thanks for trying to help. Closing this here. Best, Raphael.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.