0

I am using this script to check the connections to my sql server:

select
C.client_net_address,
S.host_name,
 c.session_id,
 c.net_transport,
 c.protocol_type,
 c.auth_scheme,
 s.program_name
from sys.dm_exec_connections c
inner join sys.dm_exec_sessions s
on c.session_id = s.session_id;

I have noticed that our servers in the UK are generally different to the servers in the US.

US:

enter image description here

UK:

enter image description here

I have found a very interesting link:

Kerberos Authentication requires that you have Service Principal Names registered

and I was wondering if that could be the case for the differences in the authentication scheme on the pictures above.

1
  • 1
    Are you referring to the SQL vs. KEREROS in the auth_scheme? If yes, SQL authentication does not use Kerberos, so if they are using a SQL login it will always be SQL. If a Windows account is used, it will try Kerberos first, and if that fails, it will use NTLM. Jul 11 '19 at 17:48
1

When the auth_scheme is SQL, that means the user is using SQL authentication, which does not, and cannot use Kerboros.

When a user that is using Windows authentication connects, authentication will first try to use Kerberos. If Kerberos fails for some reason, authentication will fall back to NTLM. Probably the most common cause of Kerberos failure is that the correct SPNs are not present in Active Directory (see Register a Service Principal Name for Kerberos Connections.

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.