• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 540
  • Last Modified:

sys.linked_logins remote_names

What does it mean if the sys.linked_logins remote names column = "NULL", I was tryng to determine which account is used in the link, which is very hard to do unless you can see an actual account, so what does the NULL indicate?
0
pma111
Asked:
pma111
  • 7
  • 7
1 Solution
 
Carl TawnSystems and Integration DeveloperCommented:
It means that the login is not being mapped to a remote login, but is instead passing through the login specified in the connection.
0
 
pma111Author Commented:
Where can you see the login specified in the connection. By "remote login", is that the account in the remote SQL Server?
0
 
Carl TawnSystems and Integration DeveloperCommented:
The login specified by the connection is the account making the request to the remote server.

Say, for example, I was logged in to the local SQL Server as bob.smith; by default, if i made a query to the linked server, it would attempt to map me to a user named bob.smith in the remote server.

If you had configured a mapped login for bob.smith to, say, john.doe; when i made a query through the linked server it would authenticate me as john.doe. In that scenario your query would show john.doe in the remote_name column.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
pma111Author Commented:
Say for example then there is a link bewtween server 1 and server 2, using the account pma111, does that mean only pma111 can access data on server 2 via this link. our concern was once the link was setup, every user on server 1 could access data on server 2 using the permissions owned by pma111 on the remote server?

Or have we got that wrong? And is that not correct then? So say pma111 had sysadmin on server 2, then our fear was any user in our server would then have sysadmin permissions over server 2. I wasnt sure once a link was established whether only the named user in the link could use the link to access the remote database, or whether everyone on the local server could use the link to access the data on the remote server. i.e. public type access.

Really new to this so apologies if I am missing anything obvious.
0
 
Carl TawnSystems and Integration DeveloperCommented:
The mapping only applies to the login specified. So, if you have a login called pma111 on server1, mapped to pma111 on server2, then those permissions will only apply if the person querying the linked server is logged on as pma111.

What happens for everyone else depends on your settings. If you had it setup like this:
Security settings
Then the local pma111 login would map to pma111 on the remote server, and all other accounts would be denied access through the linked server.
0
 
pma111Author Commented:
Ah, so not as bad as first feared. Where did you get to that screen? We thought once the link was created is was like public access and anyone could make use of the link, I assume such a public link can be created looking at those other options so that all users can access the remote server.
0
 
Carl TawnSystems and Integration DeveloperCommented:
If you are setting up the linked server through the GUI, there should be an option in the list on the left called Security.

Just remember - in SQL Server security is configurable for pretty much everything.
0
 
pma111Author Commented:
Is there anyway to see the "for a login not defined in the above, connections will", for linked servers already setup, or can you only see this information when setting up a new linked server.
0
 
Carl TawnSystems and Integration DeveloperCommented:
The options set different fields in the sys.linked_logins table, as follows:

"Not be made" - No linked_logins record

"Be made without using a security context" - uses_self_credential=0, remote_name=NULL

"Be made using the login's current security context" - uses_self_credential=1, remote_name=NULL

"Be made using this security context" - uses_self_credential=0, remote_name=login name specified in dialog.
0
 
pma111Author Commented:
So for example:

"Be made using the login's current security context" - uses_self_credential=1, remote_name=NULL"

Which is how ours is setup.... If the account used in the linked server is a sysadmin on the remote server, does the above mean all users in the local server can then access the remote server with sysadmin permissions? I am guessing the 2nd and 4th options work together.
0
 
Carl TawnSystems and Integration DeveloperCommented:
No, it means it will use the context of the user querying the remote server.

If the user isn't a sysadmin on the remote server then they won't get sysadmin rights. If the user doesn't exist on the remote server then they won't get access at all.

So, if pma111 logs into ServerA and queries linked server ServerB, he will authenticate as pma111 on the remote server and get whatever permissions s/he is assigned on the remote server.

The access level on the remote server doesn't have to match the access level on the local server. I could have sysadmin rights on the local server, but only have read access to a single table on the linked server if that is how it was set up.
0
 
pma111Author Commented:
Ok thanks, think I have got it !
0
 
pma111Author Commented:
worst case I guess from security angle would be the option "Be made using this security context" and that security context being a sysadmin account on the remote system, therefore all users would get sysadmin to the remote server?
0
 
Carl TawnSystems and Integration DeveloperCommented:
Correct.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now