Should a linked server be created using the sa account as the security context? Why / why not?

  • 6
    Why do you need to use sa - it is the most privileged account and can do anything ? Always remember the principle of "using least privilege account" – Kin Shah Feb 6 '19 at 14:41

Using the SA account in general is not a good idea.

The sa account is known as the account with the highest privileges and can very well be a target for users with malicious intent. You should at least disable the sa account.

More information on securing this account here

Regarding the linked server login security context

If you are using the the security context, you should use a SQL login with the least privileges needed, remember that anybody that can access the linked server can execute queries under this SQL login's context. If that was sa, you would be able to do anything on the target server, like dropping databases.

In my opinion, the more secure and granular way of going about this, is to create remote users directly per login, changing the permissions based on what the login needs to be able to do.

You could also Impersonate the AD login, but remember that you could bump into 'double hop' problems when trying to access the linked server if kerberos authentication is not installed or not properly configured.

An example of changing the permissions on the target server per login could be:

enter image description here

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