[Replication] Snapshot agent executes sp_addlinkedsrvlogin
-
I'm trying to set up a transactional replication of database inside AlwaysOn using remote distributor. My snapshot agent returns this error:
Validation failed for the publisher 'TESTSQL' with error 21878 severity 16 message 'Unable to create a linked server to use in contacting the remote publisher for original publisher 'TESTSQL', publisher database 'TESTSQL-1', and redirected publisher 'MyDatabase'. The command 'sys.sp_addlinkedserver' failed with Error '15247', Error Message 'Error 15247, Level 16, State 1, Message: User does not have permission to perform this action.'.'.
Values in this error are switched so I thought I made some mistake, so I dropped and recreated whole configuration but still got the same error.
Looks like snapshot agent needs to execute sp_addlinkedserver and sp_addlinkedsrvlogin in order to work, but I created all necessary linked servers (TESTSQL-1, TESTSQL-2, TESTSQL) myself while configuring whole replication, tested their connection, but in the end agent still wants to create linked server. When I grant him (meaning Domain\Replication_account, under which replication runs) permissions to ALTER ANY LINKED SERVER and ALTER ANY LOGIN, replication works, but such step is not mentioned in Snapshot Agent Security. Also no changes are being made to linked servers.
Are these two permissions really necessary in configuring replication using remote distributor?
-
Are there differences in the linked servers created by the snapshot agent than the ones you created? If they have the same name it would throw a different error trying to create it even with correct permissions, so confirm which server they're being created on (distributor or publisher). It sounds like you've missed creating the linked servers somewhere.
-
I believe I found the difference in linked server options. My previous approach was to first create all linked servers and then begin to configure replication. Today I tried it again, but without creating any linked servers first. Linked servers to publishers (from distributor) were therefore created automatically when I added TESTSQL-1 and TESTSQL-2 as publishers in distributor properties. Only difference I noticed was "Publisher" value:
On my first attempt I set it to be True, since it was a publisher in the distributor's eyes, or so I believed. After the linked servers were created by server itself, no additional ALTER permissions were required and replication was working.
Interesting is that when I recreated my first attempt and gave the login requested ALTER permissions, he didn't change this setting and kept the publisher value set to True. I also tried changing other values like Data Access, but again no change from snapshot agent account. Replication was working again.EDIT: Replication broke after server restart. Now even Log Reader won't start. Asking for ALTER permissions again. Seems like I have to live with them.
- Majk - Tuesday, March 12, 2019 6:36 AMI believe I found the difference in linked server options. My previous approach was to first create all linked servers and then begin to configure replication. Today I tried it again, but without creating any linked servers first. Linked servers to publishers (from distributor) were therefore created automatically when I added TESTSQL-1 and TESTSQL-2 as publishers in distributor properties. Only difference I noticed was "Publisher" value:
On my first attempt I set it to be True, since it was a publisher in the distributor's eyes, or so I believed. After the linked servers were created by server itself, no additional ALTER permissions were required and replication was working.
Interesting is that when I recreated my first attempt and gave the login requested ALTER permissions, he didn't change this setting and kept the publisher value set to True. I also tried changing other values like Data Access, but again no change from snapshot agent account. Replication was working again.EDIT: Replication broke after server restart. Now even Log Reader won't start. Asking for ALTER permissions again. Seems like I have to live with them.
There are a lot more differences than what you would see on the properties. You would need to query sys.servers to see more differences.
You would really be better off letting replication create the components.Sue
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply