Solved

sys.linked_logins  remote_names

Posted on 2014-04-17
14
386 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
  • 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

825 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