a red  cross in SQL server 2008 with SP3  publisher from replication monitor after changing password

marrowyung
marrowyung used Ask the Experts™
on
hi all,

right now can't open the monitoring session in replication monitor after changing the password as local policy ma ma said change password, once that I see keep seeing message said my account has been locked out but I can login.

sometime can't see log history but wait for 1/2 day replication job history can be seen, sometimes it will said account being locked out but it is not, later on if I go back to the security page again the account really show it is locked out.

how can I remove the red cross in replication monitor?

red cross
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Check your sql services and see if they are running. And the check the services and see if they are using an account with them. Username and password attached to the service. If so then update the password to the new  one.

Commented:
Your session has been disconnected from distributor

You just need Right click on Red Cross and Click on connect to Distributor.
marrowyungSenior Technical architecture (Data)

Author

Commented:
but publisher and distributor is the same box...
Ensure you’re charging the right price for your IT

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!

Commented:
It does not matter.You are monitoring the transaction flow status from Replication monitor which is just a tool.

Have you tried by right clicking on the Red cross and connect to distributor.
marrowyungSenior Technical architecture (Data)

Author

Commented:
tks ,let me try, but that SQL server just told me again my account was locked !
marrowyungSenior Technical architecture (Data)

Author

Commented:
I am not sure if this link works :

http://sqldbpool.com/tag/sp_browsereplcmds/

Commented:
Above link showing use of Replication stored procedure "Sp_browsereplcmds" which use for querying the replication commands.

But screen shot which you have shared in the question is clearly showing disconnected session.

However account locked is also an another issue which needs to be identified as well.

If account is locking again and again this means you have left your account logged on somewhere or you have saved your password might be somewhere any scheduled job.
marrowyungSenior Technical architecture (Data)

Author

Commented:
"which use for querying the replication commands. "

what replication command you means ? the T-SQL going to replicate to the subscriber, which execute on subscriber side?

"But screen shot which you have shared in the question is clearly showing disconnected session. "

what I don't understand is, why the publisher and distributor on the same serve still have this problem. no network in between.

""If account is locking again and again this means you have left your account logged on somewhere or you have saved your password might be somewhere any scheduled job. "

no. it will not be that simply as it keep locked out just after I change password. if the password policy is in place, I searched that we need to disable the password policy first and then change password, then turn the password policy back on.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
after changing the password as local policy
Which account? Your personal account? If so you shouldn't use it for configure services as Replication.
Go to SQL Agent and check who's the owner of the Replication jobs.
Check this Replication Security Best Practices article and act accordling.
marrowyungSenior Technical architecture (Data)

Author

Commented:
deepakChauhan,

"However account locked is also an another issue which needs to be identified as well. "

this is about SQL server local password policy can make it hard to change password, I found instruction on the web that we need to remove policy first, then unlock account, then enable policy again.

it seems working for me now.

Vitor,

it is about security policy on SQL server.
marrowyungSenior Technical architecture (Data)

Author

Commented:
after I change the password the replication monitor still show me red cross but my boss's login session can see green sign.

login problem
this is the screenshot when I try to reconnect to distributor, it said the login failed, this is my login and I can connect to SSMS without any problem.
marrowyungSenior Technical architecture (Data)

Author

Commented:
how can I change THAT password, by this ?


change-replication-passwords.jpg
Commented:
@but my boss's login session can see green sign.

If so.... It means replication is working fine you don't need to update the password in replication settings.

Well i tried to reproduce the issue like you but did get the same.
Can you try like this. First you have to remove the Publisher and add it again. Don't worry.... no impact on running replication.
Replication_Monitor.JPG

Commented:
Ok. I got it. You are using SQL Authentication account and Old password is stored in Replication monitor so your account is locking again while clicking on the connect to Distributor option.

Solution:

Disconnect  SSMS which is connected by this login.
Removed Publisher entry from Replication monitor.
Unlock the account if it locked out again.
Change the password With enforce password policy.

Add publisher in Replication monitor.
marrowyungSenior Technical architecture (Data)

Author

Commented:
deepakChauhan,

"Disconnect  SSMS which is connected by this login.
 Removed Publisher entry from Replication monitor.
 Unlock the account if it locked out again.
 Change the password With enforce password policy."

I don't think I can remove publisher entries from replication monitor if I disconnect from SSMS , right?

"Add publisher in Replication monitor. "

publisher already there, I tried to add again, it doesn't works.

"Can you try like this. First you have to remove the Publisher and add it again. Don't worry.... no impact on running replication. "

so just add and remove again?  that simple ?

right now I can tell you that after one day my boss follow my insturcution to unlock account by remove policy first , unlock account and the enable policy again, my replication monitor works back on without doing anything !

funny isn't it ?

Commented:
@ I don't think I can remove publisher entries from replication monitor if I disconnect from SSMS , right?

SSMS and replication monitor both are different tools. You can just type SQLMonitor in window run text box. Replication monitor will not close if you disconnect the ssms.

@publisher already there, I tried to add again, it doesn't works.

You can remove it from replication monitor just in one click....... Right click on Red cross and remove and Add it again.
marrowyungSenior Technical architecture (Data)

Author

Commented:
"SSMS and replication monitor both are different tools. You can just type SQLMonitor in window run text box. Replication monitor will not close if you disconnect the ssms."

very good, are you running merge replication or ... ?

"You can remove it from replication monitor just in one click....... Right click on Red cross and remove and Add it again. "

ahaa, red cross icon appear AGAIN, I didn't do anything man..

"Can you try like this. First you have to remove the Publisher and add it again. Don't worry.... no impact on running replication. "

just done, seems ok..

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