SSIS: Just started getting a “Key not valid for us

Question

I have 2 scheduled jobs on my SQL Server 2005 machine that are scheduled to run each morning (around 2:00 AM). These jobs have worked fine (mostly) for years and although I've had a few hiccups that I've had to work through this problem is completely stumping me.

Two mornings ago, one of my packages started reporting the following error:

Executed as user: [Service Acount]. ...n 9.00.4035.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
     Started:  1:15:01 AM  Error: 2012-10-17 01:15:03.98
     Code: 0xC0016016
     Source:
       Description: 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.  End Error  Error: 2012-10-17 01:15:03.99
     Code: 0xC0016016
     Source:
       Description: 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.  End Error  Error: 2012-10-17 01:15:04.01
     Code: 0xC0016016     
Source:       
Description: Failed to ...  The package execution fa...  The step failed.

This appears to be a common problem, however, none of the recommendations that I've found either apply to my scenario nor does my instance seem to match most of the other cases where this occurs. Here are the important details regarding my implementation.

• This package is exporting data from an iSeries system, to SQL Server 2005 data tables.
• This process works successfully but keeps crashing on one, specific table export. In fact, it runs without any trouble for over 2 hours before it dies. After inspecting all properties associated with this step, I can see that there is nothing different about this step compared to the other table export steps, other than the table/column export mappings.
• The package ProtectionLevel is set to DontSaveSensitive and the iSeries credentials are stored in a config file that is accessed by SQL Server.
• I can execute the failing step on my machine, in BIDS. Regardless, it doesn't work on the server, though the server is using the exact same credentials.
• As I mentioned, I have two packages. They are effectively the same thing, except one is exporting the data from one iSeries database, and the other is exporting data that is almost the exact same structure from another iSeries DB. The first package doesn't have any trouble even though it is using the same iSeries credentials.
• To be clear, nothing on my server has changed in months (that I am aware of.) This just started happening yesterday morning.

Any tips, or thoughts would be tremendously helpful. This export is extremely important and many users/worker rely on this data for their day-to-day work.

Answer 1

Well, I hate having to post such a response but I've solved the problem.

The short answer reason why I had this problem is because one of the fields in a data table was improperly defined. In this case it was declared as a decimal (11, 3) and it should have been a decimal (13, 3). I didn't experience this problem until a value was being posted to the table that didn't fit the (11, 3) range.

This issue highlights one of my biggest complaints with SSIS. On occasion I get errors that are often well documented on the internet. I search through all of my logs and I try to set up various test scenarios under the assumption that the error message is honest. Yet, when I finally solve the problem, it's completely unrelated to the error message that is written to the log file.

In this case, the error mentioned above had absolutely nothing to do with the problem?! In fact, I was very lucky to see the problem at all. I knew the update on my table might be a potential fix because I've seen SSIS mis-communicate like this before.

I'd like to blame this on neutrinos from space bombarding my server but the best take-away from this experience is to try and solve your SSIS problems based off of the advice of others, however, if their advice doesn't help, realize the issue may be unrelated to the SSIS error message and triple-check everything associated with the point of failure.

Answer 2

For me it was just the password for a connection Manager in SSIS, which was not saved. Input the password -> OK-> close dtsx file & reopen. Error gone.

Answer 3

This is a workaround to avoid the issue in the first place of saving sensitive passwords in the package.

I removed the FTP task and used a File System task instead to copied the file to the same location via a network share on the ftp server and avoided using the ftp protocol.

I was also able to save the package to the file system instead of the sql server store and it ran fine.

Hopefully it helps you!

Answer 4

Open the package dtsx then go to properties set ProtectionLevel EncryptSensitiveWithPassword and put password . Connect the package form sql sever job with same password.

See http://support.microsoft.com/kb/918760 and http://www.mssqltips.com/sqlservertip/2091/securing-your-ssis-packages-using-package-protection-level/

Answer 5

I could not post the image in the comments, so posting it as an answer.

When you try to import the package to SQL Server, as soon as you right click and do "Import Package" you will get the following window.

Click on the rectangle box to the right of the window. It will give you the option of changing the protection level of the package. Change it to "Do Not Save Sensitive" and try running the package. A word of caution, this will require you to remove the existing package and re-import it again. So, you may want to try it on another machine before touching the existing configuration.

Answer 6

Ran into the same error message in SQL Server 2012. For me, restarting SQL Server Management Studio resolved the problem. Issue was likely caused by my domain password changing a few days ago while SSMS was open. If you run into a similar problem and it doesn't go away with a simple restart, make sure you check Control Panel\User Accounts\Credential Manager for cached account info and/or try a reboot.