July 10, 2012 at 4:02 pm
I have a stored proc coded with Execute As SomeValidCRMUser.
This stored proc last step is to issue xp_cmdshell <something>
where something looks as below:
dtexec /f C:\Projects\GetList.dtsx /set \Package.Variables[User::Var1].Value;"X" /set \Package.Variables[User::Var2].Value;"{660D8575}" /set \Package.Variables[User::Var3].Value;"{BC62DCDC}" /set \Package.Variables[User::Var4].Value;1
where GetList.dtsx will access Microsoft CRM filtered views thus the need to have Execute As SomeValidCRMUser.
BUT
Here is the outcome:
The last step in the SSIS package is a mail component. This mail component sends a mail message indicating that the SSIS Package was executed with <WindowsAccount>. This does not help because <WindowsAccount> does not see any data in Microsoft CRM. SSIS Package data flows use Windows Authentication.
It appears that the xp_cmshell <something> did not run with SomeValidCRMUser.
How can I set it up to run with SomeValidCRMUser account? ALL our CRM user accounts are Windows accounts because they are Active Directory authenticated.
Thanks!
July 12, 2012 at 9:41 am
Hi,
What context is the SSIS package run under?
Cheers
Chris
July 12, 2012 at 9:54 am
Xp_cmdshell kicks off dtexec which runs the SSIS package.
The sproc executes as User1 but it appears that xp_cmdshell is hard wired to sql server agent service account so the SSIS package that uses Windows Auth automatically runs in the context of the SQL server agent service account.
I found this out by putting a mail component in the SSIS package to discover that it did not run as User1 but as the SQL server agent service account.
Thanks!
July 12, 2012 at 9:58 am
That's a little surprising, I would expect a stored proc call to xp_cmdshell which launched DTEXEC to actually use the SQL Service account, not the Agent account.
Either way, your best bet here might be to have the stored procedure execute a job and have that job run under a proxy which would call the SSIS package using the right credentials.
July 12, 2012 at 10:01 am
Thanks!
July 12, 2012 at 10:19 am
One other thing, I just remembered, you could also enable a server proxy account. By doing this any execution of xp_cmdshell would run under that proxy. It might be worth considering (especially as it would help reduce the potential attack vectors you have from enabling xp_cmdshell). You can find it on the Server Properties -> Security page
July 12, 2012 at 10:28 am
Where would we that proxy account connect to?
The sproc alreadyhas EXECUTE AS svcAccount1.
The xp_cmdshell does not seem to have a run as or execute as -- so how do we connect that proxy account to the xp_cmdshell statement?
Thanks
July 12, 2012 at 11:19 am
The server proxy? You don't, any time xp_cmdshell would get called by the proc it would launch as that particular user specified (rather than the service account)
July 12, 2012 at 11:24 am
Thank you for the clarification.
We ended up adding the sql server agent service account as a valid CRM so that when xp_cmshell->dtexec->SSIS package the user is a valid CRM user account and can get returned data.
July 12, 2012 at 11:31 am
Out of curiosity, are your SQL and SQL Agent services using the same account?
July 12, 2012 at 12:22 pm
No they are 2 separate accounts but both are local admins on the sql server box. Thanks
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply