Do linked and remote server use Windows authentication?

Question

Do linked and remote server use Windows authentication?

Shanke

SSC-Addicted

Points: 494
More actions
August 10, 2010 at 7:31 am

#141647

As a part of administration task I need to find out if we have configured linked servers to use windows authentication
What I am currently doing is as follows:
1. I first verify that local db instance is configured in windows authentication mode.
2. Then I verify that 'Be made using the login's current security context' is chosen for linked server
HOW? validate that there is only one entry for the linked server and "uses_self_credential" is set to '1' AND remote_name == NULL , by running the following query:
'select a.name srvname, b.local_principal_id lpid, b.uses_self_credential self, b.remote_name rmtname
from sys.servers a, sys.linked_logins b
where a.is_linked = 1 and a.server_id = b.server_id'
The query should return only one result with (lpid = 0 && self = 1 && rmtname = NULL). Else it means that we have a problem!
Am I correct in doing so?
Please help!
Thanks ,
Damodar Shanke
If U need things 2 b done,
then do the things that U need to be doing! :cool:

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

try this: this(in theory) should show you your username/context when on the remote /linked server, assuming the linked server is a SQL server:

select * from openquery

(YourLinkedServer,'select

user_name() AS [user_name],

suser_name() AS [suser_name],

current_user AS [current_user],

system_user AS [system_user],

session_user AS [session_user],

user AS '

)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

magasvs SSCertifiable Points: 7660 More actions · Answer 2

You can have Windows logins on source database , but they have to be mapped to SQl Server logins on remote server. Windows to Windows logins mapping does not work.

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 3

magasvs (9/5/2010)
You can have Windows logins on source database , but they have to be mapped to SQl Server logins on remote server. Windows to Windows logins mapping does not work.

Not quite true - in order to be able to do this, you need to have Kerberos setup in your environment. That will allow the authentication to pass through to the other system.

If you don't have Kerberos setup - then it will not work.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

magasvs SSCertifiable Points: 7660 More actions · Answer 4

Yes, I tested it on environment without Kerberos. Thanks for correction.