BI Studio password problems

Question

Post reply

BI Studio password problems

Jeff Modzel

Ten Centuries

Points: 1020
More actions
October 17, 2005 at 2:47 pm

#165664

Anyone else have this problem - The ConnectionManager connections won't retain their passwords, even when I click on the "Save My Password" checkbox. I was working in BI Studio all day with no problems and then out of nowhere, none of the connections will remember their passwords. I'm using the September CTP build.

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

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

Kirk Haselden SSCommitted Points: 1683 More actions · Answer 1

What do you have the package protection value set to? Is it "RemoveSensitive"?

Jeff Modzel Ten Centuries Points: 1020 More actions · Answer 2

Do you mean "RemovePasswords"? That setting is in Project's property pages. I have that set to false. Just to go back to the problem, I was working all day in BI Studio and then out of nowhere, it won't remember the passwords to any of my connections.

I've been working with SSIS for the past year in it's various beta forms and it's been buggy. I've seen improvements from release to release but weird problems like this one pop up. Most of my work involves sucking data from Oracle and I don't think that the Oracle drivers always play nice with SSIS. I'm hoping that the final release here in Nov will clear this stuff up.

Kirk Haselden SSCommitted Points: 1683 More actions · Answer 3

I'm sorry you feel that the product is buggy. Usually, that's the way it is with beta software. That's the reason Microsoft releases betas to get feedback from great customers like you. Since you were in the beta and you were finding bugs, did you file them at betaplace to ensure we knew about them and could fix them? That really assists us in improving the product.

No, I'm not talking about "RemovePasswords" and I am addressing the problem you asked about.

Click on the package in the designer and then look at the properties. Do you see the "ProtectionLevel" property? Is it set to "DontSaveSensitive"?

K

Jeff Modzel Ten Centuries Points: 1020 More actions · Answer 4

In the package properties, I do not have any option "DontSaveSensitive". My VS version info is below.

Secondly, I appreciate your help but I'm not an idiot. I understand that beta software us buggy. I'm not complaining, just looking for a solution. I'm not an MS basher.

I've never heard of betaplace and just went there and apparently you need to be "invited" to participate. Needless to say, I haven't been invited.

Microsoft Visual Basic 2005 55603-000-0000016-00867

Microsoft Visual Basic 2005

Microsoft Visual C# 2005 55603-000-0000016-00867

Microsoft Visual C# 2005

Microsoft Visual C++ 2005 55603-000-0000016-00867

Microsoft Visual C++ 2005

Microsoft Visual J# 2005 55603-000-0000016-00867

Microsoft Visual J# 2005

Microsoft Visual Web Developer 2005 55603-000-0000016-00867

Microsoft Visual Web Developer 2005

Microsoft SQL Server Analysis Services Designer

Version 9.00.1187.00

Microsoft SQL Server Integration Services Designer

Version 9.00.1187.00

Microsoft SQL Server Report Designer

Version 9.00.1187.00

Microsoft SQL Server Report Model Designer

Version 9.00.1187.00

Kirk Haselden SSCommitted Points: 1683 More actions · Answer 5

If you click on the package in the control flow tab and then look at the properties in the property grid, there is a property called Protection Level. Do you see that?

To what is it set?

Thanks,

K

Jeff Modzel Ten Centuries Points: 1020 More actions · Answer 6

I have a package property called ProtectionLevel and it's set to EncryptSensitiveWithUserKey.

Maybe I should start a new thread, but you (KH) are knowledgeable about SSIS so I'll ask here. Where's the best place to find the best practices for SSIS? Any books coming out?

Kirk Haselden SSCommitted Points: 1683 More actions · Answer 7

I know of several books:

Donald Farmer wrote an excellent reference on scripting SSIS.

Brian Knight and company are finishing up a reference.

Joy Mundy and a coauthor are writing an applying SSIS and SQL Server BI tools to the Kimball method book.

I'm writing a book as well.

K

David Everett Newbie Points: 6 More actions · Answer 8

I am seeing the same behavoir. My package worked fine this morning, and it has been working fine for months. This afternoon it tells me I am supplying blank/null password, and no matter what I do I cannot get the password to save.

I am also confused by the instructions from Ken. I don't see any "ProtectionLevel" setting or any choice for "RemoveSensitive".

Did anyone figure out what happened? I am exasperated!!

Jeff Modzel Ten Centuries Points: 1020 More actions · Answer 9

Few things to try - when you have a ConnectionManager window open, enter the correct password info, hit "Test Connection" to verify it works, check the "Save my passowrd" box, then make sure to hit the "Ok" button on the ConnectionManager window to save the changes.

In Business Intelligence Development Studio, get to the Control Flow tab and right click the background and select Properties. This should bring up the Properties for the package. Here you will see the ProtectionLevel property. I set mine to EncryptSensitiveWithUserKey when in development, in production I use a .dtsConfig file to feed the passwords to the packages. I think that the "RemoveSensitive" thing mentioned above was only relevant to the beta version.

Hope that helps.

David Everett Newbie Points: 6 More actions · Answer 10

OK, I have solved all of my problems, and it wasn't easy. The initial confusion where I thought my package suddenly didn't work was caused by me trying to go back in to the connection properties and click on the "Test Connection" button. It was failing with blank password, even though I had entered a password the last time I was on that screen and checked the box "Allow Saving Password".

This is normal behavior. The password is saved, but it is not displayed on the properties dialog (when you double-click on a connection object).

My package actually was working fine on the original box and with the original userid, but I was confused by the "Test Connection" button failure.

What I was REALLY trying to do was to run the package on a different computer with a different userid, and that execution was failing with blank passwords.

On the properties of your Package, there is a setting called "ProtectionLevel". When sorted by category, it's under "Security". The default for this property is "EncryptSensitiveWithUserKey" which means the connection passwords are encrypted with a key that is specific to that userid and machine. If you try move move your package to another computer or execute it with a different userid, it will fail with blank passwords.

The easiest solutionto this is to either use SSPI for your connections, or to store your packages on a DTS Server or an SQL Server.

My solution was to change this property to "EncryptSensitiveWithPassword" and supply a password in the "PackagePassword" setting. Unfortunately, this means every time I open the package in the designer I must specify this password, but at least my package is now portable.

I am using a C# program to run the package, and here is my code:

Application a = new Application();

Package p = new Package();

DTSExecResult r = new DTSExecResult();

a.PackagePassword = "password";

p = a.LoadPackage("myPackage.dtsx",null);

r = p.Execute();

Note that you must specify the PackagePassword on the Application BEFORE you load the package.