November 7, 2002 at 8:16 am
I have few porcedures on server2 that are connecting to server1 and getting data from there and putting into table in server2 database. These procedures had servername, databasename and username parameter that were given when we ran the procedure. User was dbo. Job failed after I changed SA password. I changed to user other user instead of DBO, it's still failing. Does anyone have any ideas?
Also there was a job that was running a pcakage and using sa user. I changed it to other user. It failed. and I can't even open the package. Says invalid parameter.
Thanks for your help.
November 7, 2002 at 8:40 am
Check hoe SQL Agent is connecting to the server. Right click SQL Agent, select properties and then the connection tab. You are probably using sa and so the password is out of date.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
November 7, 2002 at 8:57 am
Thanks, Simon but we are using NT authentication for sql agent.
November 7, 2002 at 10:46 am
Using EM drill to one of the failing jobs. Right click the job, select tasks and do generate script. Oputput to a file and do a search of the file for sa. Also, if these run DTS packages check your DTS package to see how you defined your connections to the SQL server if you have any.
November 7, 2002 at 11:43 am
As far as your first question, if your procedures are using Linked Servers to accomplish the cross-server query, you may need to adjust your Linked Server to accomodate for the new "sa" password.
As for the second package, you may have saved your package with an owner name and password, or a user name and password that is required to run the package. In either case, if the user is "sa", then the password is now invalid on the package (I believe). You should be able to use your packages again by returning the "sa" password back to what it was previously.
Just some thoughts. I won't guarantee these are the solutions, but you might look into them as possible causes to your problem.
Matthew Burr
November 8, 2002 at 7:50 am
Thanks, Matthew.
Yes it was a linked server issue and it's working now. Still working on the package, changed all the passwords but didn't work yet. Thanks everyone for your help.
December 3, 2002 at 6:53 am
Here is something to try with your DTS pkg. Open the package in design mode, right-click your connection and look at the properties. Click the advanced button and you should see a property called 'Persist Security Info'. That value needs to be 1 or your new password will never be saved.
December 5, 2002 at 8:10 pm
DTS is notoriously buggy. Make sure you have the latest service pack installed, store your packages locally and don't password protect them.
Those are our rules of thumb to avoid most of the worst DTS problems. ¢¢¢
Edited by - don1941 on 12/06/2002 4:46:43 PM
December 7, 2002 at 5:19 pm
Actually MS has never changed their stance on Security of packages. You should password protect them (however you should keep up with the password too). And you should use a userpassword to allow individual users to be able to execute packages and not worry about design.
See BOL "Handling Package Security in DTS"
quote:
It is strongly recommended you use DTS package passwords for all packages to ensure both package and database security. At a minimum, always use DTS package passwords when connection information to a data source is saved and Windows Authentication is not used.
But as with anything they you might come across issues and most times the latest SP will correct.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply