none
Command runs fine in elevated command prompt, but fails when run in a Windows Task Scheduler task. RRS feed

  • Question

  • 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.
    3. Hence the need to automate SQL Server backups "creatively".
    4. 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"".
    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? TIA, Raphael


    rferreira

    Tuesday, August 20, 2019 8:39 PM

Answers

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

    1. 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.)
    2. 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.
    3. 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.


    rferreira


    • Marked as answer by rferreira.dba Wednesday, August 21, 2019 6:11 PM
    • Edited by rferreira.dba Wednesday, August 21, 2019 6:13 PM Incorrect spelling.
    Wednesday, August 21, 2019 6:11 PM

All replies

  • There is apparently an error in the definition of the job in Windows task scheduler, so that the password is incorrect. To simplify things, put the SQLCMD command in a BAT file, run and test it. (No elevated prompt needed.)

    When it's working run the BAT file from Windows Task scheduler.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, August 20, 2019 10:11 PM
  • Thank you for the interesting suggestion. Where did that come from? Do .bat files get treated differently from commands hard coded into the Windows Task? Here are my results:

    Now, using the .bat file, just as before, if I simply run the .bat file the backups are processed perfectly.

    The difference now happens when I use it in a task, and try to run the task. This time around, not only it does not work, but also nothing gets logged in the SQL Error logs. If I look at the Windows Scheduler task history I can see the run, with a result of 1, but again, nothing in the SQL Error Logs. Not even the "Password does not match" message I was getting before... Bizarre... 

    Thoughts? TIA, Raphael.


    rferreira

    Wednesday, August 21, 2019 1:49 AM
  • Hello Friend,
    Have you checked that the user running the task has high enough permissions?
    It could have been that.
    And you still have the option to run with higher privileges in the windows task.

    I had a similar problem with a windows task for Oracle from a client. And it was related to the user running the task. We put a user with high permission on windows and solved.

    Jefferson Clyton Pereira da Silva - [MCSA | MCP | MCTS | MTA | Analista de Banco de Dados - Sql Server e Oracle ]

    Wednesday, August 21, 2019 2:00 AM
  • Thanks guys I resolved the problem. Although I'm not sure what I did that resolved it. What I did:

    1. 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.)
    2. 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.
    3. 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.


    rferreira


    • Marked as answer by rferreira.dba Wednesday, August 21, 2019 6:11 PM
    • Edited by rferreira.dba Wednesday, August 21, 2019 6:13 PM Incorrect spelling.
    Wednesday, August 21, 2019 6:11 PM
  • Thank you for the interesting suggestion. Where did that come from? Do .bat files get treated differently from commands hard coded into the Windows Task? Here are my results:

    My assumption in this case is that you had an error in the command.

    But, yes, BAT files may be different from Task Scheduler. At least I have seen this with Agent, and I would expect Task Scheduler to be the same. They spawn a command, but they down spawn a full-blown command shel, so things like redirection with > etc does not work.

    But my main idea with the BAT file is that you would only have the command in once place, so that you know that it works.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, August 21, 2019 9:55 PM
  • Yeah... But, from the opening statements:

    1. 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.
    2. 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
    3. 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.
    4. 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"

    So, no, there was nothing wrong with our command syntax, as I mentioned more than once. But hey thank you much for trying to help. I appreciate your attempt at an assistance. R.


    rferreira

    Wednesday, August 21, 2019 10:51 PM