4

I have a very simple setup. SQL Server X (2012) and SQL Server Y (2016). X has a linked server to Y using the setting "Connections will: Be made using the login's current security context". SPNs are set up, and internal users on our domain can query the linked server. I can see their connections authenticating through KERBEROS if I run this query:

select session_id,net_transport,client_net_address,auth_scheme 
from sys.dm_exec_connections WHERE net_Transport = 'TCP'

However, a few external users connect via VPN. They then shift+right click on SSMS and "run as a different user" and use their account located on our domain. They can connect to the instances separately, but hit the 'double-hop' issue like below when trying to query the linked server:

Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

If I run the above query, I can see their connections are using NTLM authentication, not KERBEROS, so that explains why credentials are not passed through. Why are being authenticated with NTLM? Is this a configuration issue with the VPN? Maybe a limitation to a VPN?

**** UPDATE ****

Further testing has shown that if a user connects to a machine on our network via RDP and leaves that connection open, connections from their local SSMS then seem to use KERBEROS. When the user logs off of the RDP session, new connections from SSMS then start using NTLM again. In other words, they are doing NOTHING inside the RDP session, but as long as that's open, connections from SSMS use KERBEROS. (Maybe a coincidence, but that's the best I've got now)

1
  • After connecting to VPN, are any results returned by running the following cmd prompt command: setspn -q MSSQLSvc/*? This should return all MSSQL SPNs registered on the domain. If this returns nothing, I suspect the VPN is configured in such a way that certain traffic from the domain server is being blocked, and this would prevent the kerberos handshake from completing successfully. May 9, 2017 at 14:05

1 Answer 1

1

Assuming the client has SSMS 2012 installed, you might have one of them try running the following from a command-prompt as a test:

%SystemRoot%\System32\runas.exe /netonly /user:DOMAIN\Username "%ProgramFiles(X86)%\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\ssms.exe"

If they have some other version of SSMS, you'd need to replace the "110" with the appropriate version number, such as 120 for SSMS 2014 or 130 for SSMS 2016, etc.

If that does work, and you are so-inclined, you could use my answer on StackOverflow to create a small User Interface for your users that could prompt them for the domain\username\password then launch SSMS using netonly authentication. This would essentially mean they wouldn't need to run the "runas" command manually.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.