Search code examples
sqlsql-servert-sqlreplication

Error when adding replication logreader agent with login

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.

  • It's using a local windows account
  • SQL Server 2017 Dev edition
  • Windows 10 Home edition (maybe this?)

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.

Solution

  • 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\[email protected]

    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.