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)

  • How are the VPN users connecting to the database server? Via a fully qualified network name, IP, etc.? – John Eisbrener May 8 '17 at 18:02
  • @JohnEisbrener The users are connecting by FQDN only. – SomeGuy May 9 '17 at 13:01
  • 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. – John Eisbrener May 9 '17 at 14:05
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.

  • The clients do have SSMS 2012 and already had a "runas" shortcut setup to launch SSMS with the /netonly switch. I tested with one user, and their connection changed over to KERBEROS after disconnecting from the VPN, and then reconnecting and opening SSMS. I don't know how to explain that one, but the linked server is working now. – SomeGuy May 4 '17 at 19:03
  • I'd say that sounds like an issue connecting from the client workstation to the domain controller via the vpn. – Max Vernon May 4 '17 at 19:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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