July 12, 2018 at 2:31 pm
Hi,
I am in process of disabling 'sa' account on server.
Server is running transaction replication
I created new login for replication (repl_agent_d) assigned to all replication agents, restarted replication agents
I changed owners of all sql agent jobs owners from 'sa' to account 'server_service'
to make sure that all changes been applied I restarted the sql server and sql server agent
When I run profiler I see that same SPID = 67 with same Application name='Repl-LogReader-0-pricingdb-5' use 2 login names 'SA' and 'repl_agent_d'
example
exec @retcode = sys.sp_MSreplcheck_publish --executed by 'sa' with SPID = 67
exec @retcode = sys.sp_MSreplcheck_publish -- executed by 'repl_agent_d' with SPID = 67
since I changed security of all agents and ownership of the the job I expect 'sa' no longer be used by any replication process
Is any way to identify why replication still using 'sa' login ?
Thanks
July 12, 2018 at 7:02 pm
ebooklub - Thursday, July 12, 2018 2:31 PMHi,
I am in process of disabling 'sa' account on server.
Server is running transaction replication
I created new login for replication (repl_agent_d) assigned to all replication agents, restarted replication agents
I changed owners of all sql agent jobs owners from 'sa' to account 'server_service'to make sure that all changes been applied I restarted the sql server and sql server agent
When I run profiler I see that same SPID = 67 with same Application name='Repl-LogReader-0-pricingdb-5' use 2 login names 'SA' and 'repl_agent_d'
example
exec @retcode = sys.sp_MSreplcheck_publish --executed by 'sa' with SPID = 67
exec @retcode = sys.sp_MSreplcheck_publish -- executed by 'repl_agent_d' with SPID = 67since I changed security of all agents and ownership of the the job I expect 'sa' no longer be used by any replication process
Is any way to identify why replication still using 'sa' login ?Thanks
Did you check if they are in the Publication Access List?
Sue
July 12, 2018 at 7:12 pm
And check the run as for the job step, not just the job owner.
Sue
July 13, 2018 at 9:25 am
removed 'sa' from "Publication Access List" still see 'sa' in profiler
Stoped job
Name = MyServer-Pricingdb-12
Owner = server_service
Category = REPL-LogReader
job step 1
name = Log reader agent startup message
Type = TSQL
Run as = ''
Command = sp_MSadd_logreader_history @perfmon_increment = 0, @agent_id = 12, @runstatus = 1, @comments = N'Starting agent.'
job step 2
name = Run agent
Type = Replication Transaction- log reader
Run as = SQL server agent service account ---(account is running under .\sql_service as member of local admin group)
Command = Publisher [Myserver] -PublisherDB [Pricingdb] -Distributor [Myserver] -DistributorSecurityMode 1 -Continuous
job step2
Name = Detect nonlogged agent shutdown.
Type = TSQL
Run as =
Command sp_MSdetect_nonlogged_shutdown @subsystem = 'LogReader', @agent_id = 12
When I stop and disable this job profiler stop showing Application name='Repl-LogReader-0-pricingdb-5'
When I enable this job Application name='Repl-LogReader-0-pricingdb-5' returns ....
I double check
all publications have log reader agent security set as
login = repl_agent_d
pass = *****
July 13, 2018 at 12:53 pm
There is much more to it than just changing the log reader agent. There are a lot of other variables that can impact what to change where such as push/pull, immediately updating subscriber, Linked server/distributor_admin, local or remote distributor, etc. You probably need to go through each one of the steps in this article:
View and Modify Replication Security Settings
It sounds like a lot of the replication was configured using sa so you will need to go through everything. It's more than just the agents and jobs. You need to check it for all publications.
If you wanted to trace the use of sa further, you need to filter the trace to just sa and you want to know more than executions of sys.sp_MSreplcheck_publish as that is called in over 100 other replication stored procedures. You want to capture the host as well. You'll need to capture a lot more activity to figure out when it's being used and by what process.
Repl-LogReader-0-pricingdb-5 is just a log reader job. So yes when you stop the job, you won't see it anymore. When you start the job, you see it again.
Sue
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply