Post reply

SQL Agent Powershell - Remote permissions

  • June 25, 2012 at 1:56 pm

    #276843

    My first attempt to use Powershell is proving frustrating:

    I have a SQL 2008R2 server and two other Win 2003 servers sitting outside the domain. I need to sync a directory between the two 2003 boxes, and I wanted to use a PS step in a SQL job to do it rather than installing an app to do it. I have a user on each box with matching username & password that has access to the shares involved. I already have an SSIS job running (and working) on the SQL server that proxies that account and accesses the share.

    I added Powershell to the existing proxy, and created a new job & step (job owner is sa, run as proxy account). Write-Output $env:username returns the proxied login, but gci \\10.0.0.1\Share returns: "Cannot find path '\\10.0.0.1\Share' because it does not exist."

    It works fine from SQLPS, and the job works for local paths (even w/ the loopback IP). I cannot figure out why the path doesn't work when the proxy is working and the user has permissions.

  • June 26, 2012 at 10:15 am

    #1505688

    Try to change the Job Owner to a NON-SA Account.

    if the job owner is sysadmin fixed server role step will be executed under the account used by the Sql Server Agent service, and maybe not have the right credential to access the path

    $hell your Experience !!![/url]

  • June 26, 2012 at 11:05 am

    #1505732

    I thought I had verified that everything worked through the Powershell console, but just to be sure, I logged on interactively as the proxy user and stepped through the whole process...and got a "password expired" error.

    It's working in SQL agent now, but I still find it strange that SSIS never errored out using the same login.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply