Developing Matt

My Technical Journal

Sensitive SSIS and Encryption

leave a comment »

If you ENCRYPT SENSITIVE DATA WITH USER KEY everything will work fine until you import and run the job.  You might get:

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.

If you set it to be a password (ENCRYPT SENSITIVE WITH USER KEY)) and don’t set up that password when setting up the job you will get:

Failed to decrypt an encrypted XML node because the password was not specified or not correct.

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 .". 

The way I like to do it is to set it at ENCRYPT SENSITIVE WITH USER KEY which will enable the passwords to be saved in the package for development and upon import change the encryption to use Rely on server storage and roles for access control which will use the permissions inherent to the job owner.

New Picture (8)

Advertisements

Written by matt

January 18, 2012 at 5:20 pm

Posted in SSIS

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: