1

I have three servers: P1, A1, and R1. P1 and A1 are production oltp servers. On each there are two databases that are being replicated down to R1 for olap (mostly SSRS reports). I am working on cleaning up our linked server infrastructure. P1 and A1 both have linked servers to R1, and SSMS won't let me remove them because they are being used for replication.

Currently both of these linked servers are using the sa account on R1 for authentication. I'd like to replace this with a least privileged account. What privileges would P1 and A1 need in order for replication to work?

In case it matters, all of the publications are transactional replication.

Also, sql server versions:

  • P1: 10.0.5500
  • A1: 9.0.5069
  • R1: 10.0.4000
Improve this question

1 Answer 1

Reset to default
3

I just reviewed the replication on one of my clients systems and I see similar linked servers both on the publisher and the distributor. In my case the linked servers are all setup using the "Be made using the login's current security context".

If you've got the linked servers setup using a specific security context that's probably because the machines aren't on the same domain. If they are, and the rights are setup correctly you can try changing things. The linked servers should only be used when making changes to the replication configuration so if there's a problem you may not see it until later.

Improve this answer
2
  • I haven't had a lot of good luck using "be made using the login's current security context" in the past. Sadly we're not using service accounts properly at the moment so the set spn's that would work to allow Kerberos authentication that would allow the security context to be forwarded isn't in place. I can see if it works though.
    – DForck42
    Commented Jan 21, 2013 at 22:03
  • That probably explains the current configuration. That or at some point in the past someone decided to use the linked servers for something else and couldn't get them to work using the current security context setting so they just changed them to SA. Change it back and see what breaks / who screams.
    – mrdenny
    Commented Jan 22, 2013 at 22:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.