Security issue with SSIS called from proc

Who is online?  0 guests and 0 members
Home  »  Forums   »  microsoft business intelligence   »  integration services   » Security issue with SSIS called from proc

Security issue with SSIS called from proc

Topic RSS Feed

Posts under the topic: Security issue with SSIS called from proc

Posted: 12/3/2011

Jedi Youngling 4  points  Jedi Youngling
  • Joined on: 12/3/2011
  • Posts: 2

I am writing a stored proc that calls a SSIS package, that works on its own, but gives me the following error when I run it from a stored proc: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.

The SSIS package runs properly if I run it from the commandline as: dtexec /f "L:\Data\LPS\_EZPikins\CLAIMSIII__LPS_Extract_DO_NOT_Execute.dtsx"

This is the proc:
declare @ssisstr varchar(8000), @packagename varchar(200)
set @packagename = '"L:\Data\LPS\_EZPikins\CLAIMSIII__LPS_Extract_DO_NOT_Execute.dtsx"'
set @ssisstr = 'dtexec /f ' + @packagename
DECLARE @returncode int
EXEC @returncode = xp_cmdshell @ssisstr
select @returncode


Posted: 12/3/2011

Jedi Knight 1516  points  Jedi Knight
  • Joined on: 1/3/2010
  • Posts: 266

In you package, what is your Protection Level? I believe you have "Encrypt all with password".

Take a look: http://msdn.microsoft.com/en-us/library/ms141747.aspx

 


Posted: 12/6/2011

Jedi Youngling 4  points  Jedi Youngling
  • Joined on: 12/3/2011
  • Posts: 2

I think it has to do with the account I am running on the server, it has admin sql permissions and therefore trusts me to run this. When I run it from a stored proc, sql server tells me "bad login for user". I can force the issue by editing the .dtsx file to include the password in the connection strings, but this is cumbersome as I have to edit the file everytime I save it from the GUI. I have tried all the security combinations, to no avail. My question is: How do I get the sql password through the stored proc call? Would it be a /set on the dtexec command line through the sql xp_cmdshell? And if so, what is the correct syntax of the /set?

 


Page 1 of 1 (3 items)