Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

replication or monitor breaks after changing sa password Expand / Collapse
Author
Message
Posted Friday, August 31, 2012 6:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 27, 2014 2:44 PM
Points: 11, Visits: 243
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
Post #1352763
Posted Friday, August 31, 2012 7:44 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 9:11 AM
Points: 1,326, Visits: 2,597
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
Post #1352813
Posted Friday, August 31, 2012 8:19 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:34 AM
Points: 6,518, Visits: 14,039
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"
Post #1352842
Posted Friday, August 31, 2012 8:51 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 27, 2014 2:44 PM
Points: 11, Visits: 243
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
Post #1352872
Posted Friday, August 31, 2012 9:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:34 AM
Points: 6,518, Visits: 14,039
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"
Post #1352880
Posted Friday, August 31, 2012 9:07 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 24, 2014 11:52 AM
Points: 9,294, Visits: 9,484
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."
Post #1352888
Posted Friday, August 31, 2012 9:26 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 27, 2014 2:44 PM
Points: 11, Visits: 243
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)
Post #1352908
Posted Saturday, September 1, 2012 3:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:34 AM
Points: 6,518, Visits: 14,039
At present I have not managed to reproduce this issue,

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

"Ya can't make an omelette without breaking just a few eggs"
Post #1353135
Posted Tuesday, September 25, 2012 7:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 27, 2014 2:44 PM
Points: 11, Visits: 243
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
Post #1364014
Posted Tuesday, September 25, 2012 3:05 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 9:34 AM
Points: 402, Visits: 2,013
Distribution agent also gets created using the sql server agent account.

If you resolve the issue through microsoft, please update this thread.
Post #1364298
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse