On attempting to add the logreader agent for SQL Server replication with the following:
exec sp_addlogreader_agent
@job_login = 'DANSLAB\repl_logreader'
, @job_password = 'replP4ss'
, @publisher_security_mode = 1
I get the following error:
Msg 3930, Level 16, State 1, Procedure msdb.dbo.sp_grant_login_to_proxy,
Line 70 [Batch Start Line 2]
The current transaction cannot be committed and cannot support operations
that write to the log file. Roll back the transaction.
Msg 3931, Level 16, State 1, Procedure sys.sp_MSrepladdproxyaccount, Line
129 [Batch Start Line 2]
The current transaction cannot be committed and cannot be rolled back to a
savepoint. Roll back the entire transaction.
However if I run the stored proc without the credential to just use the Agent service account, it works. I can't for the life of me, figure out why.
This is me playing around on my own machine, and I'm starting to wonder if maybe it's my setup.
Everything else prior to this, e.g. setting up dist database, publisher and setting DB for publication seems to be fine.
I ran the following fine (action immediately prior to adding logreader job):
exec sp_replicationdboption @dbname= 'WideWorldImporters-Full', @optname =
'publish', @value = 'true'
I wondered if it might be permissions, but after giving the repl_logreader user sysadmin access on the instace, and even full admin rights on my machine, I still get the same.
After two nights on this, any help would be greatly appreciated.
Finally! I don't quite understand the mechanics of it so if anyone can explain, please do so.
This seems to be a product of the microsoft online login. I log in to my windows 10 using my live account. I then run SSMS, PoSh etc, from there. executing "select susername()" gives me:
MicrosoftAccount\myuser@domain.com
of course the local users that I created would be something like "PCName\replUser"
If I run the commands from a proper local user rather than my Microsoft Account it works. So I guess I'll set up a real local user to my work from and all should be good.
If there's a way to do this without having to do so, that'd be great. But happy with this for now.