none
Automating with Task Scheduler Using Service Account (Active Directory Windows Login) to Connect to SQL Server from Powershell RRS feed

  • Question

  • Hello,

    I need to set up a daily task to login to a Sql server, query a table, write the results to a file.  Then bcp the or insert the data into a table on a different Sql Server table.  Normally I would use perl and cron job on a unix server to move data from one db server to another.  And would use a sql server login that could be used in the script.  

    The issue is that I have been given an Active Directory Service Account to login to the first Sql Server - so I need to login using Window Authentication.  They want this because they want a centrally managed account and password.  So, my admin account on the Windows server can run powershell, but the service account cannot.  So, I can start powershell with the following bat file: 

    runas /netonly /user:domain\svcuser "powershell.exe c:\mydir\myscript.ps1"

    But then I have to enter the svc user credentials - interactively. 

    And I can't figure out out to automate this.  I thought I could specify in the task scheduler to run the task as the svc user, but that doesn't work.  What is the generally accepted way to automate a powershell script that needs to use one set of credentials to run powershell, but another set to connect to a sql server with windows authentication?      

    Thank you very much.  I do apologize if this answer is readily available, but I just cannot find it.  

    Friday, July 13, 2018 11:10 PM

Answers

  • You will have to allow local logon for the service account. You will not be able to automate without doing that.


    -- Bill Stewart [Bill_Stewart]

    • Marked as answer by ckb_ Friday, July 20, 2018 1:34 PM
    Saturday, July 14, 2018 6:56 PM
    Moderator
  • I've done the same thing.  Open "Local Security Policy" and add it to "Local Policy > User Rights Assignments" Log on as a service AND Log on as a batch job.  From there, you can then schedule a task for it to run.   You will have to have rights directly to the source DB as this user, and run invoke-sqlcmd (within the SQLServer module) without specifying credentials.  And ensure the user account also has rights to the target DB, too. 
    • Marked as answer by ckb_ Friday, July 20, 2018 1:34 PM
    Monday, July 16, 2018 2:19 PM

All replies

  • What you are trying to do cannot be done.  You cannot use a service account in an interactive script.  YOu need a full account.

    Create a task on one SQLServer  that pushes or pulls data from the second server. 

    or

    Create a table replication task to do this.  Post in SQLServer forum for help with this.


    \_(ツ)_/


    • Edited by jrv Friday, July 13, 2018 11:28 PM
    • Marked as answer by ckb_ Friday, July 20, 2018 1:34 PM
    • Unmarked as answer by ckb_ Friday, July 20, 2018 1:40 PM
    Friday, July 13, 2018 11:27 PM
  • So, my admin account on the Windows server can run powershell, but the service account cannot.

    That sentence doesn't make sense. PowerShell is just an executable. Why would a service account not be able to run it? I have a number of production servers that execute scheduled PowerShell tasks using different service accounts. They work just fine.


    -- Bill Stewart [Bill_Stewart]

    Saturday, July 14, 2018 2:06 PM
    Moderator
  • Hi Bill,

    Thank you for pointing that out.  I don't know exactly why the service user cannot run powershell.  The service user has been denied "local logon"  or interactive logon which seems to be recommended for service accounts.  If I try to run powershell as the service user I get the error:  Logon failure: the user has not been granted the requested logon type at this computer.  Is there a way to give the service user the ability to run powershell without granting logon?  Or a way to feed the credentials to runas /netonly non-interactively?

    Thank you,

    Caroline



    Saturday, July 14, 2018 5:28 PM
  • You will have to allow local logon for the service account. You will not be able to automate without doing that.


    -- Bill Stewart [Bill_Stewart]

    • Marked as answer by ckb_ Friday, July 20, 2018 1:34 PM
    Saturday, July 14, 2018 6:56 PM
    Moderator
  • Thank you.  
    Saturday, July 14, 2018 7:55 PM
  • I've done the same thing.  Open "Local Security Policy" and add it to "Local Policy > User Rights Assignments" Log on as a service AND Log on as a batch job.  From there, you can then schedule a task for it to run.   You will have to have rights directly to the source DB as this user, and run invoke-sqlcmd (within the SQLServer module) without specifying credentials.  And ensure the user account also has rights to the target DB, too. 
    • Marked as answer by ckb_ Friday, July 20, 2018 1:34 PM
    Monday, July 16, 2018 2:19 PM
  • Thank you very much all three of you that replied.  All three of these solutions are good suggestions.  In the end, because this is to support a c# web application and my background is programming, not so much sysadmin, I wrote a c# console application and that runs as a task as the service user and it uses the integrated security to connect to the db as the service user.  When I have time I want to go back and confirm I didn't do anything else differently while I was flailing around, but it seems the c# exe has different security requirements from the powershell ps1 script.  


    Friday, July 20, 2018 1:34 PM