June 27, 2004 at 10:59 am
I have changed the SA password on multiple servers (required procedure by security).
This caused DTS packages to fail. Before changing the SA, I removed all SA references within the package using a different login information for connectivity. However when running DTS packages via SQL Server Agent jobs the SA failures are written to the error log and the job fails.
I have a solution to do a 'save as' of the DTS package and recreate a job. This could become a tedious process on multiple servers and many many jobs.
There must be a different approach to fix this.
Any hints are really appreciated!
Thank you!
June 27, 2004 at 5:53 pm
You did restart the server (the physical server, not SQL Server) after doing this didn't you? If not, you need to. We had the same problem a couple weeks ago. I didn't restart a couple of them. After I restarted the, the DTS packages worked fine. I have no idea why this happened, as I don't remember it in the past.
Derrick Leggett
Mean Old DBA
When life gives you a lemon, fire the DBA.
June 28, 2004 at 6:53 am
I once found a connection not visible on my DTS package sheet using "Disconnected Edit..." which is under the Package menu. Just look thru all the connections there and see if any still contain a reference to SA.
June 28, 2004 at 7:47 am
I did restart the physical machines after the password has changed. I also looked thru all the connections within DTS packages and did not find any reference to SA.
I ended up spending all day re-saving the packages and rescheduling them.
I have to change the passwords again in 45 days and it will be a nightmare!
June 28, 2004 at 9:50 am
If you are using SQL 2000 or can get a SQL 2000 EM you could open the package and save as VIsual Basic file then do a search for sa to see if it really is still in that package. If not then did you maybe use the sa account to conect DTSRun i Agent to the SQL Server to run the package itself?
June 28, 2004 at 1:38 pm
Hi,
In the Run prompt, open the DCOMCNFG and check the login credentials for SQL Server Agent. There will be a login id assocaited with that SQL Server Agent service. If you are using the same SA account there also, change the password there also and see.
Give this a try. Worth trying.
Hope this help!!!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply