Linked Server Configuration using remote user logins

Question

I'm trying to configure a Linked Server using remote logins reducing who has access to the linked server by taking advantage of the not be made option. For test purposes alone, I have used SA, obviously I won't be using that going forwards however I just wanted to make sure the procedure worked and SA having all privileges would cater for this.

Again for troubleshooting purposes, I initially tried SA using the 'be made using this security context' option which worked well but obviously is not secure as I want to reduce who has access to the linked server.

When I tried the 'not be made' option with a local and remote login I receive an error suggesting that no login mapping exists although the login worked when I used the 'be made using this security context' option. What am I doing wrong?

enter image description here

• SqlWorldWide: Did you see this? stackoverflow.com/questions/32084453/…
• Krishn: Hi @SqlWorldWide, yes I have and a sa login does exist on the remote server

Answer 1

I think you need to check the Impersonate option or not use sa for testing—use another SQL Login instead for testing this functionality. I'm not 100% certain it's required but you may also need to consider ensuring the remote logins have a matching sid on both SQL Server instances as I wrote about in the User mapping to a standby database post if you do not use impersonation so something easy enough to test using sp_helplogins and CREATE Login if needed.

Why

I don't recall where I heard or read about it, or if it's even applicable in this case, but I thought the impersonate rights were inherited implicitly for logins with sysadmin and local db_owner rights.

Impersonate

Pass the username and password from the local login to the linked server. For SQL Server Authentication, a login with the exact same name and password must exist on the remote server. For Windows logins, the login must be a valid login on the linked server.

To use impersonation, the configuration must meet the requirement for delegation.

---

Not be made

Specify that a connection will not be made for logins not defined in the list.

Source

Impersonation

If you go with using impersonation, below are a couple syntax examples to use for domain user account logins and local SQL logins which connected to the master DB.

GRANT IMPERSONATE ON LOGIN::[domain\username1_to_be_impersonated] TO [domain\username2]
GRANT IMPERSONATE ON USER::[user1_to_be_impersonated] TO [user2]