SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Agent Powershell - Remote permissions


SQL Agent Powershell - Remote permissions

Author
Message
sminar
sminar
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 285
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.
Laerte Poltronieri...
Laerte Poltronieri Junior-367636
Old Hand
Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)

Group: General Forum Members
Points: 331 Visits: 836
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 !!!
sminar
sminar
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 285
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search