June 29, 2011 at 2:31 pm
I am migrating (more precisely re-creating from scratch) a SQL Job from 2005 to 2008 which calls an SSIS package. In the process I am trying to clean up and lock down security so that everything isn't running under a Domain Admin account with the highest possible security that can be granted (everywhere). Basically I have an SSIS Package which was upgraded in BIDS 2008 and deployed to the File System which contains a single Execute Process Task which calls the SysInternals tool PsExec.exe to remotely launch a custom .NET executable on a different server to parse an Excel document and load it into a database via a vendor's WebService.
This SQL Job is scheduled nightly to run with a local SQL account being set as the Job owner but where the Job Step is using a Domain Proxy Account which was solely created for the purpose of running this SSIS package. I have set up a Credential for this Domain\UserAcct and set up the Proxy access granting access to SSIS Package Execution, Operating System (CmdExec) and even ActiveX Script. The Domain\UserAcct does not have access to see the SQL Jobs at all, but it does have limited read/write access to the database (which it doesn't really need). I have granted the Domain\UserAcct the Local Security Policy of "Log on as a batch Job" in order to execute the SSIS Package as well as "Log on as a service" in order for the Proxy account run the PsExec.exe utility as a Windows Service. This account is even in the local machine's "Administrators" group (for another job which it runs). When the SQL Job runs at the scheduled time, it never succeeds but it also never fails. Even more so, when I open up the OS Processes window and view all processes, I see the PsExec.exe utility running under the Domain\UserAcct Proxy which I have configured.
I tried turning on SSIS logging and it gets to the OnProgress event with 0% and just hangs. Now for the fun part of trying to figure this out, my domain account has Admin rights on the box and owner on the SQL Server but when I run the Job (even though the owner is set as a local SQL account and using a Domain Proxy account) I get the same results.
When I'm logged onto the DB Server OS with my Domain Account and I launch a Command Prompt using RunAs with the Domain\UserAcct (i.e. the same one used in the Proxy) and manually call the "dtexec.exe /DTS <blah> <blah> <blah>" copied directly from the SQL Job Step the SSIS package runs successfully with no errors and completes as expected. If I change the Job Owner over to be the Domain\SQLService account which is running the SQL Agent Service and the Job Step to use the SQL Agent Service as well (instead of the Proxy acct) and run the same SQL Job all runs without any problems as well. This indicates to me that I most likely have a permissions issue in SQL somewhere. I don't know if this is an impersonation issue caused by SQL Servers use of a Proxy account or something else. Does anyone have any ideas on a setting that I may have missed in my upgrade to SQL 2008 R2 that would prevent my simple (I used the term lightly) SSIS package which launches the PsExec utility.
TIA
July 7, 2011 at 2:27 am
Hmmm,
I have had a very similar scenario, but I think my problems were different to yours.
Have you tried to run a simple job step with the proxy account that does something like, create a folder on the local server. Have you tried just running the PSEXEC as a command line step in the job with the proxy account ?
I think it is a permission problem..it's just where, as you say.
Graeme
July 11, 2011 at 8:00 am
I have tried running the SSIS Package from cmdline logged in at the the domain Proxy account and have no problems with is running. I can run the PSExec manually from my domain account as well and it too runs without problems. I have a feeling there is something unusual going on under the hood with Impersonation that SQL is doing via the Proxy account which is causing these issues, but I'm stumped as to how to prove this or proceed. If I grant the domain Proxy account Local Admin rights on the box, I can get it to run as well but this is not ideal.
July 11, 2011 at 8:11 am
Like I said have tried getting the proxy account to do something simple through a job step..like create a folder or copy a file. Does the sql Agent account have any permissions on the local server ??
July 11, 2011 at 2:50 pm
Unless I'm missing something here, you can't use a proxy account to run a simple SQL Job step. This removes the ability to run a simple EXEC xp_cmdshell 'mkdir C:\Temp'. If you are asking about running a Job Step of type Operating System (CmdExec) via the command "mkdir TempTest" running under the Proxy account, then the answer is yes I can do this without any problems. (NOTE: I made sure the Proxy account had access to CmdExec and that the xp_cmdshell were both enabled).
The SQL Agent account is a Domain Account as well but there isn't anything special on the box (that I know of) which grants extra permissions to this account. When the SQL Instance was installed, we set the account at that point which could be granting access to something else, but I'm not sure what (I say this because if you look at local GP on the machine the SQL Agent account appears to have been added to some of the policies).
I just wish I knew more about the impersonation being done to run the Job under the Proxy account by SQL in order to track down other options.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply