This is a very frustrating problem.
Let me break it down for the reader.
I have an agent service account that works fine - the account that jobs run under.
I know it works fine because this is the same account all my SQL Backup jobs run under - without a hitch.
I also have a valid sql server account that I use for connection string logins when building packages -
that account works fine. I know it works fine because I can login to the SQL Server Manager with this account.
So, I build a package with the sql server account and password for the OLE DB connection manager in a package. All the package does is a simple query on a table and outputs to a flat file.
I create a job, with 'sa' as the owner. in step 1 I select SSIS package, run as SQL Agent Service Account, package source is file system and I point to the package. The package itself uses the sql server login account to execute.
If I run the package it works fine. I close the solution, someone else comes along and opens the solution - THEY CHANGE ABSOLUTELY NOTHING ABOUT THE LOGIN OR SQL AGENT SERVICE ACCOUNT, but apparently just the fact that they opened the solution and looked at the package, breaks the login. If they try to run the package, they get a login failure, whereas I did not. After they close it, it breaks for me as well. If they open the solution, open the connection manager, re-enter the sql login's password, the package works fine for them, until I come along and open the solution, then it is broken again.
This makes no sense to me. Why would an 'author' opening a solution impact the connection manager when ABOLUTELY NOTHING ABOUT THE CONNECTION MANAGER WAS CHANGED?!
Seems like a bug to me. Anyone seen something like this?
Thanks in advance.
randy
Read up on the package ProtectionLevel property in this forum and in Books-Online.I'd bet the package is set to EncryptSensitiveWithUserKey, which would prevent others from being able to run the package. You can try EncryptSensitiveWithPassword, but then all developers would need to know the package's password and would be required to enter it to open the package and be able to use it. Either way, Microsoft did not want to be responsible for package security and made sure that the packages are secure.
You could also try DontSaveSensitive, but then you'd have to use a package configuration file to pass in the passwords to the connection managers. It's a touch cumbersome, but in the end it's worth the extra security.|||
Thanks very much. I'll take a look.
Regards
|||Randyvol,
What you described in your first post is just the normal behavior of SSIS. SSIS does not save sensitive information like connection manager's passwords unless you use an encrypted protection level in your package. If the protection level of the package is set to DonSaveSensitive; then you have to provide the connection credentials every time you open the package. That is what seems to be happening.
As a good practice you can use package configuration to set the connection string of connection managers at run time; that way the package will always get the connection credential when is executed. That will not change the behavior of the package when editing it.
