SSIS – How to handle sensitive data

While working with developing SSIS you will eventually have to deploy your packages to a production environment. Chances are that you run in to this error message

Error at  []: 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 do then this blog post and this one will help you understand why and how you should handle it in the future.

In short: Before you start developing your packages you need to think about deploying the solution to the production environment. What you should do is set your package cariable called ProtectionLevel to DontSaveSensitive, why? Cause you should store your sensitive data in a database and keep your connection data to that table in one package variable. The first link above explains why and the second link above explains how.



This entry was posted in IT, SSIS and tagged , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *