Solved

sys.linked_logins  remote_names

Posted on 2014-04-17
14
407 Views
Last Modified: 2014-04-22
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
Comment
Question by:pma111
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
14 Comments
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 40006134
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
 
LVL 3

Author Comment

by:pma111
ID: 40006356
Where can you see the login specified in the connection. By "remote login", is that the account in the remote SQL Server?
0
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 500 total points
ID: 40006369
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
Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

 
LVL 3

Author Comment

by:pma111
ID: 40006403
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
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 40006420
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
 
LVL 3

Author Comment

by:pma111
ID: 40006446
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
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 40006449
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
 
LVL 3

Author Comment

by:pma111
ID: 40014684
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
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 40014805
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
 
LVL 3

Author Comment

by:pma111
ID: 40014933
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
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 40014946
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
 
LVL 3

Author Comment

by:pma111
ID: 40014974
Ok thanks, think I have got it !
0
 
LVL 3

Author Comment

by:pma111
ID: 40014978
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
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 40015040
Correct.
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question