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

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
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dan LuteyCommented:
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.
Deepak ChauhanSQL Server DBACommented:
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...
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Deepak ChauhanSQL Server DBACommented:
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/
Deepak ChauhanSQL Server DBACommented:
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ãoMSSQL Senior EngineerCommented:
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
Deepak ChauhanSQL Server DBACommented:
@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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Deepak ChauhanSQL Server DBACommented:
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 ?
Deepak ChauhanSQL Server DBACommented:
@ 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..
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.