Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


replication or monitor breaks after changing sa password


replication or monitor breaks after changing sa password

Author
Message
karen_jonesdba
karen_jonesdba
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 280
Good Morning
I am about to open a case on this with MS but thought I would post here first. (great website for sql server dbas)

I haven't used replication long so this might explain this question however here goes.

Background..
We have an audit policy in place that requires the sa pwd to be changed every 90 days.

I have transactional replication running and have written procs to post tracer tokens etc to monitor it. These procs page us if there is no ability to write the tracer token or if over a latency threshold.

Issue
We changed the sa pwd last week and last night I checked the replication monitor (just do this periodically for sanity check) and it had the big red x.

We have not been alerted on replication breaking or latency and running tablediff showed no differences.

I put the pwd back to what it was and the big red x went away.

Question
Does anyone know if replication is broken.. the monitor clearly is.
Does anyone know how to correct this other than putting the password back.

Thanks
Karen
Divine Flame
Divine Flame
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1569 Visits: 2801
Seems like "sa" login has been used while configuring replication. In that case you might need to update the replication passwords too when you change the password for "sa".

Read more here:

http://msdn.microsoft.com/en-us/library/ms151761(v=sql.90).aspx

I would suggest to first test this on some test environment to verify it works the way you want.


Sujeet Singh
Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8784 Visits: 16558
Divine Flame (8/31/2012)
Seems like "sa" login has been used while configuring replication. In that case you might need to update the replication passwords too when you change the password for "sa".

Read more here:

http://msdn.microsoft.com/en-us/library/ms151761(v=sql.90).aspx

I would suggest to first test this on some test environment to verify it works the way you want.

+1
more importantly, change the security to use an account other than sa. Passwords are indeed updated using the stored procedure sp_changereplicationserverpasswords

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
karen_jonesdba
karen_jonesdba
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 280
Thank you for getting back.

Is there a way to verify where sa is being used in all the replication processes? Other than changing the sa password and then having them break.

How can the replication processes continue to work (the password change had been in for a week yet there were no tablediff issues, and tracer tokens were getting through the architecture) however the monitor fail after a password change.

Thanks
Karen
Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8784 Visits: 16558
karen_jonesdba (8/31/2012)
Thank you for getting back.

Is there a way to verify where sa is being used in all the replication processes?

open the publication properties and go to the agent security section. check the security settings and verify the accounts being used

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
RBarryYoung
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9438 Visits: 9517
Though, given that your tracer tokens seemed to be getting through, it seems like it was the monitor that was affected.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
karen_jonesdba
karen_jonesdba
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 280
Thanks for getting back

The 'SQL Server Agent account' is being used in the Log Reader Agent and the SnapShot Agent under publication properties => agent security

Still trying to understand where else sa is being used in this.. Somewhere in my setup sa is configured.

The monitor appeared to be broken, not replication itself but this is only a hunch because tablediff was clean (this has very active replication processes) and tracer tokens were getting through (I verified this before putting the password back and we were not getting paged or emails for a week)
Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8784 Visits: 16558
At present I have not managed to reproduce this issue,

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
karen_jonesdba
karen_jonesdba
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 280
Further note to this.. I traced starting up the replication monitor in both prod and test. Even though I log on to SSMS with windows authentication in prod and test .. the trace of prod shows LoginName of 'sa' whereas the trace of test shows LoginName as my windows authenticated id. I just dont see how this is happening. Somehow in production even though I use windows auth. in SSMS, the monitor is being launched with 'sa'.

I have opened a case with MS and am waiting to hear back
muth_51
muth_51
Mr or Mrs. 500
Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)

Group: General Forum Members
Points: 532 Visits: 2862
Distribution agent also gets created using the sql server agent account.

If you resolve the issue through microsoft, please update this thread.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search