Re: VS 2008 BI does not retrieved password for a connection - 08-18-2009 , 01:44 PM
Thanks for the endorsement! I'm usually blowing my own horn on the blog.
Hope you found it usefull.
Greg: I don't think this is anything to do with your particular driver. AND
(you may not like this part I think this is *BY DESIGN* in BIDS 2005 and
2008. I have seen this with other 3rd party drivers that connect to their
respective source with Username and Password sets (ie: NOT Windows Integrated
Security). I think you may get the package to save the password by selecting
a different Package Protection Level, such as "Encrypt Sensitive with
Password", but then you are just encrypting one password with another. You
will still need THAT password in order to get it to run in SQL Agent.
Bob has a good suggestion in adding an Expression for the Connection String
property of the Connection Manager, but that means that anyone that gains
access to the original dtsx package can extract said password.
using a Config Database as mentioned in my blog, you can limit access to who
can see those Connection String entries, just granting rights to the SQL
Agent Service Account if needed.
If you have any issues or questions, post a response on the blog and I'll
get back to you personally.
Re: VS 2008 BI does not retrieved password for a connection - 08-19-2009 , 06:46 AM
Check the Package Protection Level property of the SSIS package (under the
Security section). If you are in fact using one of the 4 choices there that
starts with "Encrypt..." and your password is still not saved, then you may
have a chance of getting a response from Microsoft. But don't hold your
If you can't wait, please review the methodology in my blog. I have been
using it for 4 years now, with two different companies.
Best of luck.