Hi
Any thoughts on this one? We recently added a password to user SA on a server(server a) which previously had none (null). We had another server(server b) set up with this server as a linked server, but failed to change the sa password on the properties of the link on the other server. This caused some problems in DTS packages running on server a which had sql statements in a sql task accessing both servers with the connection set as server b. The statement was selecting data from server b but to criteria established by a sub query against data on server a. The statement failed with
Server: Msg 18456, Level 14, State 1, Line 1
Login failed for user 'sa'.
When we corrected the problem with the sa password on the linked server setup to server a on server b this problem went away.
However when we tried it 10 minutes later it failed again.
We reset the password again, it worked again.
The next morning the job had failed again, we reset the password and restarted the job & it worked.
Since then ( 2 days ) it has carried on working & hopefully will carry on doing so.
Any idea why the password has needed resetting 2 times?
Thanks in advance
Any thoughts on this one? We recently added a password to user SA on a server(server a) which previously had none (null). We had another server(server b) set up with this server as a linked server, but failed to change the sa password on the properties of the link on the other server. This caused some problems in DTS packages running on server a which had sql statements in a sql task accessing both servers with the connection set as server b. The statement was selecting data from server b but to criteria established by a sub query against data on server a. The statement failed with
Server: Msg 18456, Level 14, State 1, Line 1
Login failed for user 'sa'.
When we corrected the problem with the sa password on the linked server setup to server a on server b this problem went away.
However when we tried it 10 minutes later it failed again.
We reset the password again, it worked again.
The next morning the job had failed again, we reset the password and restarted the job & it worked.
Since then ( 2 days ) it has carried on working & hopefully will carry on doing so.
Any idea why the password has needed resetting 2 times?
Thanks in advance