Who can read data from Linked Server?

Question

Hi,

We are setting up a Linked SQL Server so that we can retrieve data from database from that Linked Server.

When we set up the Linked SQL Server, we use a SQL Login that gets Read Only rights to all tables in the Linked Server.

We would like to seek your advice who can read data from a Linked Server ? Would it be all databases from the Linked Server ? OR we need to specify which SQL Server Login or access ?

Currently, we find that a Linked Server is set up but there is no Local Server Login to remote server login mappings.

Thanks

Answer 1

A linked server is not tied to a database in any way. You map logins from the local server to logins to the linked server. The permissions then depends on what prviliges the login/user you map to has in the linked server.

Answer 2

Hi @TonyJK ,

When you mapped Local login to remote SQL login in linked server, what we can do depend on the remote login permission.

If you do not mapped the local login to remote SQL login in linked server, we can use Impersonate option. 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( linked 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. If you use this option, what we can do depend on the SQL login permissions.

There are other options for SQL linked server security. Suggest you read the MS document security part of create linked server or this blog to better understand the security for linked server.

---

If the response is helpful, please click "Accept Answer" and upvote it, thank you.

Answer 3

Hi @TonyJK ,

> If I choose "Be made using this security context" & enter a SQL Server Login (Read Only access right to a number of databases) for the Linked Server, does it mean that any SQL Login can access those databases in the Linked Server ?

Yes, i check this in my environment. For example, I created a linked server in Node4 to query data in Node 3( remote server). I using ‘Be made using this security context’ , then I need to using the SQL login account(such as SQL login Cathy) that exist in remote sever node3. Cathy has the read only access for the databases on node 3( remote server). Then any logins in Node4 such as user1 or user2 access the databases on Node3.

Be made using this security context
Specify that a connection will be made using the login and password specified in the Remote login and With password boxes for logins not defined in the list. The remote login must be a SQL Server Authentication login on the remote server.

---

If the response is helpful, please click "Accept Answer" and upvote it, thank you.