Fail to Decrypt XML for Server Password on SSH

[Grant Koeneke]

I'm trying to eval this product and everytime I run this in a SQL job
I get the same error. Not in a Job, no problem. Help!!!!

Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1
for 64-bit Copyright (C) Microsoft Corporation 2010. All rights
reserved. Started: 6:17:22 PM Error: 2011-06-17 18:17:22.25
Code: 0xC0016016 Source: Description: Failed to decrypt
protected XML node "ServerPassword" 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. End
Error Error: 2011-06-17 18:17:22.25 Code: 0xC0016016
Source: Description: Failed to decrypt protected XML node
"ProxyPassword" 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. End Error Error:
2011-06-17 18:17:24.68 Code: 0xC002F304 Source: SFTP Task SFTP
Task Description: An error occurred with the following error
message: "Please specify server password.". End Error DTExec: The
package execution returned DTSER_FAILURE (1). Started: 6:17:22 PM
Finished: 6:17:24 PM Elapsed: 2.574 seconds. The package execution
failed.

[Ivan Peev]

Hi Grant,

What do you have specified for package protection level? I suspect you
have set the default EncryptSensitiveWithUserKey , which will not work
unless you setup your SQL Job Step to use proxy account and setup to
use exactly the same user account you have used to design the package.

We recommend using EncryptSensitiveWithPassword and specifying
password. Then setup your job step type to be OS CmdExec and setup
your command line to include /DE <password> parameter. Do not forget
to prefix your command line with DTEXEC.

Regards,
Ivan

[Grant Koeneke]

I do have it set with a password and not with a key. That's where my
confusion is. It is only when I run it from the job. But, even if
you look at the Connection String in the Properties in VS it still has
a 'ServerKeyFile=' in it and I cannot get rid of it. I have tried
just about every way, including DTEXEC to run the package from the SQL
Jobs but they all come back with that same error. I will try it with
the /DE and see if that works.

When I look at the SSH connection it IS setup with a Password. The
KeyFile radio button is not checked.

[Ivan Peev]

Hi Grant,

You are confusing the SSH connection manager with the SSIS package
protection level . These are two completely different topics. Open the
package and select the canvas. Then right-click and select Properties.
Scroll-down and find ProtectionLevel property. This is the setting I'm
referring to.

[Grant Koeneke]

Woo Hoo! That was it! After that change I was also able to run it as
a SSIS Package in the SQL Job. It worked both ways.

Thanks, Ivan.

Grant