SQL Server Jobs with SSIS packages - Failed to decrypt protected XML node “DTS:Password” with error 0x8009000B

Question

0.00/5 (No votes)

See more:

i have a SQL server job that runs a SSIS package. This job has 9 steps and in each step it extracts data from a different database. the connections strings are defined as parameters in each step.

im getting the following error when i run the job.

Executed as user: USER\MYSERVER$. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 5:50:55 PM Error: 2013-06-21 17:50:55.44 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: 2013-06-21 17:50:55.45 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: 2013-06-21 17:50:55.45 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: 2013-06-21 17:51:06.30 Code: 0xC020901C Source: Data Flow Task Daily Attendance View 1 [34] Description: There was an error with output column "ShiftCode" (54) on output "OLE DB Source Output" (45). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.". End Error Error: 2013-06-21 17:51:06.30 Code: 0xC020902A Source: Data Flow Task Daily Attendance View 1 [34] Description: The "output column "ShiftCode" (54)" failed because truncation occurred, and the truncation row disposition on "output column "ShiftCode" (54)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component. End Error Error: 2013-06-21 17:51:06.30 Code: 0xC0047038 Source: Data Flow Task SSIS.Pipeline Description: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Daily Attendance View 1" (34) returned error code 0xC020902A. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 5:50:55 PM Finished: 5:51:06 PM Elapsed: 10.983 seconds. The package execution failed. The step failed.

can someone please tell me why this happens?

Posted 21-Jun-13 21:53pm

gayanee Wijayasekara

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Kuthuparakkal · Answer 1 · 2013-06-22T02:08:00

Solution 1

There is an option to in SSIS to save passwords(to access DB or anyother stuff), the default setting is "EncryptSensitiveWithUserKey"... You need to change this.

Package Proprties Window >
ProtectionLevel -- Change that to EncryptSensitiveWithPassword
PackagePassword -- enter password-> somepassword

Read: http://msdn.microsoft.com/en-us/library/ms141747.aspx[^]

http://support.microsoft.com/kb/918760[^]

Now you can specify the paassword in SQL Agent Job.

On SQL Agent Job Step, go the Command Line Tab, manually edit command line, then add the following

/De somepassword
After modification you would see(example):

/FILE "\"C:\Users\xxxx\Documents\SQL Server Management Studio\SSIS\Package.dtsx\"" /DECRYPT somepassword /CHECKPOINTING OFF /REPORTING E

Thanks,

Kuthuparakkal

Posted 22-Jun-13 2:08am

Kuthuparakkal

Updated 24-Jul-13 20:42pm

v2

Comments

The Doer 25-Jul-13 1:42am

Hi Kuthuparakkal,
I am also facing the same problem i changed from EncryptSensitiveWithUserKey to EncryptSensitiveWithPassword . and done the modification as you writtten above, but ended up with the following error.

Missing argument for option "decrypt".
The command line parameters are invalid. The step failed.

Kuthuparakkal 25-Jul-13 2:42am

Modified, please try now!

The Doer 25-Jul-13 4:44am

I changed now its displaying-
/FILE "H:\BOOGLE\BOOGLE\bin\Deployment\Package.dtsx" /DECRYPT /CHECKPOINTING OFF /REPORTING E

but it cause error as-

Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E4D Description: "Login failed for user 'sa'.". End Error Error: 2013-07-25 16:39:27.10 Code: 0xC00291EC Source: Sales from MADPOS to B2Lingerie Execute SQL Task Description: Failed to acquire connection "172.16.3.10.AIMB_POS.sa". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 4:39:24 PM Finished: 4:39:27 PM Elapsed: 3.031 seconds. The package execution failed. The step failed.,00:00:03,0,0,,,,0

The Doer 25-Jul-13 4:46am

and then now as soon i change from EncryptSensitiveWithUserKey to EncryptSensitiveWithPassword , my package is not running- it shows error as-
Failed to acquire connection, Or you may not have the right permission on this connection.

Note:- Am deploying it in another Server. Does it cause any effect??

Kuthuparakkal 25-Jul-13 9:15am

Did you give proper password once you change to "EncryptSensitiveWithUserKey" ?
Also, be sure if you are using 32bit execution mode when Excel(.xls or .xlsx) connection involved.

Kuthuparakkal 25-Jul-13 9:16am

I have been using the same approach for the last couple years, it works.. Something wrong with your pkg or settings...

efendi.lim 28-Nov-14 3:31am

Hi Kuthuparakkal,

Thank you so much, your solution works for my case and has solved my problem :)

Cheers,
Efendi

Member 11793073 25-Jun-15 16:51pm

Hi,
I should say you saved my day with this solution. Thanks alot!!

SQL Server Jobs with SSIS packages - Failed to decrypt protected XML node “DTS:Password” with error 0x8009000B

1 solution

Solution 1

Add your solution here

Preview 0

SQL Server Jobs with SSIS packages - Failed to decrypt protected XML node “DTS:Password” with error 0x8009000B

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us