Developing Matt

My Technical Journal

Package Configurations won’t work with SQL Server Authentication

leave a comment »

I like package configurations, I really do.  It’s a great why to add a bit of dynamicy* to your integration services packages.  The basic concept is that you pick a table (or file) to store these dynamic properties and then you pick which properties you want to store.  For me, I want to store database names so that I can easily move where my connections are pointing.  As a rule, I only store the essential items.   I don’t like to peruse through my table through a bunch of junk I’ll never change.  (By the way, right click on your control flow area to find the Package Configuration Wizard).  Here is what it looks like:

New Picture (7)

And that’s it.  When you run the package it grabs your values from your table (or file) and uses them.  (for the purpose of this post…I’ve only used package configurations with a table)  The trickiest part about using Package Configurations is to remember that it’s there … so when you have just changed all your connection strings you inevitably forget and run your package and viola!  All your connection strings will go back to their old value as they are populated with the data from your table and you waste another 30 minutes trying to figure out what happened.

Don’t panic.  Just fix it how it is supposed to be and step through the wizard again and it will repopulate your table with your alterations.  Then you can run your package.

Regarding connections though, there is one little hiccup that isn’t appeased with throwing water at it.  When your connections are windows authentication it works great, but when your connections are sql server authentication you might possibly spend 2 days battling the why of life with ssis like I just did.  The problem is the password.  The connection string doesn’t contain the password, nor does it appear to be saved  (even when you click ‘save password.’  Go ahead try it. It’s magic.).  Technically it does save the password encrypted in your package, but it fails when attempting to use package configurations (unless your package is actually all stars I guess).  So when your connection information is pulled from your table then it will be missing a password.   It is really hard to do anything without a password.  Here’s an error you will likely see:

Error code: 0x80040E09.  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E14  Description: "Deferred prepare could not be completed.".  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E09  Description: "SELECT permission denied on object…

The solution: Don’t use Package Configurations with connections using sql server authentication.  (bold for emphasis and for those users who don’t like to read all the preface crap)

Other things I learnt* on this trip through Never Never Land Sql Server Authentication Strings in your Config Table

1.  Regarding Running SSIS from a job:  It requires an account (like windows/ntauthority) that has permission to access ALL YOUR PACKAGE OBJECTS.  Use this on the job owner section and leave the step as windows authentication.  This is important, so read it again.   GOOD CLEAR LINK, MICROSOFT LINK

2.  Encrypt your package with a password instead of using a user key (a user key is dependant upon the user being you)  LINK  This will help you avoid this error

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:   Code: 0xC0202009 

   

 

* yes, made up words

Advertisements

Written by matt

March 21, 2010 at 9:52 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: