Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
add comment

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer
    
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 Jan 21 '13 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 Jan 22 '13 at 22:05
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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