Solved

sys.linked_logins  remote_names

Posted on 2014-04-17
14
375 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 3

Author Comment

by:pma111
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 3

Author Comment

by:pma111
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Ok thanks, think I have got it !
0
 
LVL 3

Author Comment

by:pma111
Comment Utility
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
Comment Utility
Correct.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

762 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now