Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Agent Powershell - Remote permissions Expand / Collapse
Author
Message
Posted Monday, June 25, 2012 1:56 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, December 15, 2014 1:07 PM
Points: 12, Visits: 261
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.
Post #1320870
Posted Tuesday, June 26, 2012 10:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 11, 2014 10:06 PM
Points: 158, Visits: 776
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 !!!
Post #1321364
Posted Tuesday, June 26, 2012 11:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, December 15, 2014 1:07 PM
Points: 12, Visits: 261
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.

Post #1321414
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse