June 3, 2002 at 8:25 am
I am having difficulty in changing my sa password from blank to a real password. Everytime I try to chang it my jobs fail "sa login failed" error. Below is a description of everything that has occurred. I hope someone can help. Thanks.
Heres the entire story line:
1) Installed SQL Server 2000 (svc pak2) on a server.
2) I did not set and sa password (stupid i know).
3) I created several DTS Packages.
4) I created several jobs to run those packages.
5) SQL Server Agent Properties Connections = Windows
Authentication.
6) I changed the sa password from blank to a non-blank
password.
7) I ran the jobs and they failed (Error = "Login
failed for sa.")
Note: I am changing the password from my
workstation and not at the server itself, however, my
username is a member of the sysadmin on this
particular server.
8) As soon as the jobs fail I change the sa password
back to being blank and suddenly everything runs fine.
9) I have followed the same procedure on another
server and it worked just fine. I have no idea what
to do.
June 3, 2002 at 10:28 am
When you created your DTS packages did you create your SQL connections with sa and the blank password. To check open a package and do a save as from the Package menu. If you did then this is your problem.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 3, 2002 at 12:50 pm
No, all DTS Packages are through Windows Authentication. Some of the jobs belong to sa, however it still doesn't make a difference when I change the job owner.
quote:
When you created your DTS packages did you create your SQL connections with sa and the blank password. To check open a package and do a save as from the Package menu. If you did then this is your problem."Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 3, 2002 at 5:55 pm
Had a similar issue. Had to rebuild packages. Never determined why, but could not get the password to change in the package.
Steve Jones
June 3, 2002 at 6:03 pm
You may be able to just open and resave the package then delete the prior versions and see if this helps.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 3, 2002 at 6:33 pm
I came across this link, might be helpful:
Andy
June 7, 2002 at 1:10 pm
I've been having similar problems with replication since changing the SA password.
I changed the password on both the Publisher/Distributor and on the Subscriber.
Both have exactly the same password.
The Distributor job fails because it can not log on (presumably to the Subscriber) with the SA account.
I've tried deleting all publications and subscriptions and then recreating them but no joy.
I came across and article last year that mentioned the ability to specify the account and password to use within a replication job but I haven't been able to find it.
Can anyone help?
June 7, 2002 at 8:17 pm
You change it in the distribution properties in EM, part of replication from the main menu. You can change the password directly in the job step for the distribution agent - search for "Replication Distribution Agent Utility" in BOL. Hopefully that helps!
Andy
June 10, 2002 at 3:21 am
Thanks Andy,
Using the -SubscriberLogin [mylogin] -SubscriberPassword [mypassword]in the distributor agent sorted it.
I don't know why it didn't work before, but it works OK now.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply