replication or monitor breaks after changing sa password

Question

replication or monitor breaks after changing sa password

karen_jonesdba

SSC-Addicted

Points: 411
More actions
August 31, 2012 at 6:44 am

#261317

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

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Divine Flame SSCoach Points: 15943 More actions · Answer 1

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 SSC Guru Points: 233847 More actions · Answer 2

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 SSC-Addicted Points: 411 More actions · Answer 3

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 SSC Guru Points: 233847 More actions · Answer 4

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 SSC Guru Points: 143329 More actions · Answer 5

Though, given that your tracer tokens seemed to be getting through, it seems like it was the monitor that was affected.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

karen_jonesdba SSC-Addicted Points: 411 More actions · Answer 6

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 SSC Guru Points: 233847 More actions · Answer 7

At present I have not managed to reproduce this issue,

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

"Ya can't make an omelette without breaking just a few eggs" 😉

karen_jonesdba SSC-Addicted Points: 411 More actions · Answer 8

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

Dhruva_51 SSChampion Points: 10521 More actions · Answer 9

Distribution agent also gets created using the sql server agent account.

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