package Execution

Question

package Execution

weston_086

SSCommitted

Points: 1645
More actions
March 4, 2012 at 2:01 am

#269417

I have created two package one migrates data in Same server from database X to database Y.and this works fine in BIDS ,in SQL SERVER Integration services (SSMS)after importing in MSDB,SQL Server Agent.
and
I have created one more package which migrates data from Server 'A' database X to Server 'B' database Y.it works fine in BIDS .but when try to execute that after importing in MSDB SQL SERVER Integration services (SSMS),SQL Server Agent.it gives me this error
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.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

That usually related to encrypted credentials stored in the package.. So you have stored some passwords..

I'm guessing you used trusted security in the one package and not this one OR the package is being accessed by a user other than the one that created it.

CEWII

weston_086 SSCommitted Points: 1645 More actions · Answer 2

the only difference is that the package which runs in all way i am using windows authentication but package which gives me error in integration services (SSMS) and in Sql server Agent i am using sql server authentication. even for this package i tried with package configuration (Table) even though it is same error.Is that bcz of sql server authentication ?.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

Probably..

Keep in mind that when credentials are saved they are saved in such a way that only the last user to have saved the package can decrypt them. With windows (aka trusted) security there are no credentials to encrypt or decrypt..

i usually set my packages to not save ANY protected information. If it needs some I'll give it at run-time through a config or some other method.

Does that answer your question?

CEWII