How do I update an SSIS SQL 2005 package password

RosalindP
RosalindP used Ask the Experts™
on
Our system uses NT authentication for everything.  The password (even on server boxes) has to change every 90 days.  We are trying to point out that this really does not make sense for long running application.  The password just changed and my package job no longer runs from sql agent.  The error is that the password/username is not correct.  I used Configurations packages thinking that I would be able to change the password there.  For password all that is there is Integrated Security=SSPI;  I would think this would automatically update the password.  What am I doing incorrectly?
Rosalind
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mohed SharfiVice CEO

Commented:
Hi
How are you, please see this System store procedure here:
http://msdn.microsoft.com/en-us/library/ms187402(SQL.90).aspx
thanks

Author

Commented:
Your response makes a certain amount of sense.  What confuses me is that the general directions for setting up the configuration manager for an SSIS project to be run from SQL agent include setting up a proxy and then a credential.  So if I run [ @job_login= ] 'job_login' and [ @job_password= ] 'job_password' will that keep the NT accounts in synch including the proxy and credential?

Are you using a proxy / credential?
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Yes I am.  Due to slowness of our network department, I still have not been issued a special account for running SSIS jobs.  Therefore I am stuck with using my account.  Hence, the password problem every 90 days.
Yes, I hate that. Can you use the ALTER CREDENTIAL sql to update your password?
ALTER CREDENTIAL Saddles WITH IDENTITY = 'RettigB',
    SECRET = 'sdrlk8$40-dksli87nNN8';
 

Author

Commented:
I ran the ALTER CREDENTIAL on my QA server to see if it would work and now the error is:
Unable to start execution of step 1 (reason: Could not get proxy data for proxy_id = 1).  The step failed. Instead of Message
Unable to start execution of step 1 (reason: Error authenticating proxy DOH\rep1303, system error: Logon failure: unknown user name or bad password.).  The step failed.
I ran the command against the MSDB and then Master.  
It seems to me though that what should be altered is the configuration file.  Do you think I just need to recreate the configuration file in BIDS?
Thanks,
Rosalind
Your configuration file should not be passing a password for a Credential. So I apologize for confusing the subject, what does your configuration file have that is "expired"?
Thanks,

Author

Commented:
On Tuesday I had to change my network password.  When I tested the job to see if it would still run from the sql agent job, I got the following message.
Unable to start execution of step 1 (reason: Error authenticating proxy DOH\rep1303, system error: Logon failure: unknown user name or bad password.).  The step failed.
I can run the SSIS package fine if I log onto the database server and run the package from Integration services.  I made the configuration file, thinking that as long as the package itself did not have the connection string, the integrated security would continue to work.  
In SQL Server Agent the package is going to connect to the database using the Proxy account. In the view history, the first sentence.
Message
Executed as user: DOH\rep1303.
This user is setup in the Proxy, by Credential and must have the current password.

Author

Commented:
So would I use sp_update_proxy or just delete the credential and proxy and make a new one?  I will try it out in the morning.  It's been a long day day.  Thank you so much for your help.

Author

Commented:
Thank you for all your help.  With it I figured out that all I need to do is open the properties for the credential and change the password there.

Author

Commented:
The answer was in your response, it just took me a while to figure out where to go.  A more helpful response would have been to write select properties for the credential.  From there the password can be changed.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial