Solved

sys.linked_logins  remote_names

Posted on 2014-04-17
14
396 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

749 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