1

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

0

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]

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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