Unexpected net_transport/authentication schemes

Question

Unexpected net_transport/authentication schemes

John Langston

SSCommitted

Points: 1977
More actions
September 2, 2015 at 3:58 pm

#398986

This is SQL 2008 R2 x64 (virtual environment). The Active Directory Delegation tab for both servers is set to 'Trust this server for delegation to any service (Kerberos only)'
I am connected to server TEST in SSMS from my desktop. PROD is a linked server defined on TEST pointing to our production server and I run the query below.
SELECT net_transport, auth_scheme
FROM PROD.master.sys.dm_exec_connections
WHERE session_id = @@SPID;
The results are Shared Memory\NTLM. If I open a new query window connected to TEST and run the query I get TCP\SQL.
1) I am trying to understand the Shared Memory\NTLM response as I expect that if I were in SSMS directly on the target server PROD but not under these circumstances 2) I am trying to find an explanation for a different set of results from the new query window and 3) I find KERBEROS & NTLM documented but nothing on SQL for an auth_scheme.
Can anyone shed any light on what is being observed?
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

I would imagine that the value of @@SPID is being taken from the local session, so is returning details of a completely different session on the PROD server.

e.g. your local SPID is 61, the remote query runs under spid 72 on PROD, but is returning details of spid 61 from PROD.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

mister.magoo SSC-Forever Points: 47174 More actions · Answer 2

Try running

EXEC('SELECT net_transport, auth_scheme

FROM master.sys.dm_exec_connections

WHERE session_id = @@SPID;') at PROD;

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

John Langston SSCommitted Points: 1977 More actions · Answer 3

Thanks for that query. Am further along now than I was before.