Determine If I Am Connected to SQL Server using Kerberos Authentication

This is the basic query to determine your authentication type:

select auth_scheme from sys.dm_exec_connections where session_id=@@SPID

You must run this on a client machine and not on the SQL Server you are testing, otherwise it will come back as NTLM even if Kerberos is properly configured. This is due to per-service SID security hardening added in Windows 2008, which makes all local connections use NTLM regardless of whether Kerberos is available.

SQL Server Management Studio

You can run this interactively from SQL Server Management Studio.

An alternative query:

SELECT c.session_id, c.net_transport, c.encrypt_option,
       c.auth_scheme, s.host_name, @@SERVERNAME as "remote_name",
       s.program_name, s.client_interface_name, s.login_name,
       s.nt_domain, s.nt_user_name, s.original_login_name,
       c.connect_time, s.login_time
FROM sys.dm_exec_connections AS c
JOIN sys.dm_exec_sessions AS s ON c.session_id = s.session_id
WHERE c.session_id=@@SPID

SQLCMD

You can run this from the command-line from SQLCMD:

C:\Temp>sqlcmd -S SQLProd01 -E -Q "select auth_scheme from sys.dm_exec_connections where session_id=@@SPID"
auth_scheme
----------------------------------------
NTLM

(1 rows affected)

VBScript

If neither of these options are available, you can copy the following script into Notepad and save as getAuthScheme.VBS.

'
' Auth scheme VB script.
' Run on a client machine, not the server.
' If you run locally, you will always get NTLM even if Kerberos is properly enabled.
'
' USAGE:  CSCRIPT getAuthScheme.vbs tcp:SQLProd01.contoso.com,1433   ' explicitly specify DNS suffix, protocol, and port # ('tcp' must be lower case)
' USAGE:  CSCRIPT getAuthScheme.vbs SQLProd01                        ' let the driver figure out the DNS suffix, protocol, and port #
'
Dim cn, rs, s
s = WScript.Arguments.Item(0)              ' get the server name from the command-line
Set cn = createobject("adodb.connection")
'
' Various connection strings depending on the driver/Provider installed on your machine
' SQLOLEDB is selected as it is on all windows machines, but may have limitations, such as lack of TLS 1.2 support
' Choose a newer provider or driver if you have it installed.
'
cn.open "Provider=SQLOLEDB;Data Source=" & s & ";Initial Catalog=master;Integrated Security=SSPI"          ' On all Windows machines
'cn.open "Provider=SQLNCLI11;Data Source=" & s & ";Initial Catalog=master;Integrated Security=SSPI"        ' Newer
'cn.open "Provider=MSOLEDBSQL;Data Source=" & s & ";Initial Catalog=master;Integrated Security=SSPI"       ' Latest, good for SQL 2012 and newer
'cn.open "Driver={ODBC Driver 17 for SQL Server};Server=" & s & ";Database=master;Trusted_Connection=Yes"  ' Latest
'
' Run the query and display the results
'
set rs = cn.Execute("select auth_scheme from sys.dm_exec_connections where session_id=@@SPID")
WScript.Echo "Auth scheme: " & rs(0)
rs.close
cn.close

Run from a command-prompt:

C:\Temp>cscript getAuthScheme.vbs SQLProd01
Microsoft (R) Windows Script Host Version 5.812
Copyright (C) Microsoft Corporation. All rights reserved.

Auth scheme: NTLM

PowerShell

This PowerShell script can also be a way to test the SqlClient .NET Provider and try to isolate the issue away from your application.

#------------------------------- 
# 
# get-SqlAuthScheme.ps1 
# 
# PowerShell script to test a System.Data.SqlClient database connection
#
# USAGE: .\get-SqlAuthScheme tcp:SQLProd01.contoso.com,1433   ' explicitly specify DNS suffix, protocol, and port # ('tcp' must be lower case)
# USAGE: .\get-SqlAuthScheme SQLProd01                        ' let the driver figure out the DNS suffix, protocol, and port #
# 
#------------------------------- 
param ([string]$server = "localhost")
Set-ExecutionPolicy Unrestricted -Scope CurrentUser
$connstr = "Server=$server;Database=master;Integrated Security=SSPI" 
 
[System.Data.SqlClient.SqlConnection] $conn = New-Object System.Data.SqlClient.SqlConnection 
$conn.ConnectionString = $connstr 
 
[System.DateTime] $start = Get-Date 
 
$conn.Open() 
 
[System.Data.SqlClient.SqlCommand] $cmd = New-Object System.Data.SqlClient.SqlCommand 
$cmd.CommandText = "select auth_scheme from sys.dm_exec_connections where session_id=@@spid" 
$cmd.Connection = $conn 
$dr = $cmd.ExecuteReader() 
$result = $dr.Read() 
$auth_scheme = $dr.GetString(0) 
 
$conn.Close() 
$conn.Dispose() 
 
[System.DateTime] $end = Get-Date 
[System.Timespan] $span = ($end - $start) 
 
"End time: " + $end.ToString("M/d/yyyy HH:mm:ss.fff")  
"Elapsed time was " + $span.Milliseconds + " ms." 
"Auth scheme for " + $server + ": " + $auth_scheme

Run from the PowerShell command-prompt:

C:\temp> .\get-sqlauthscheme sqlprod01
End time: 10/26/2020 18:00:24.753
Elapsed time was 0 ms.
Auth scheme for sqlprod01: NTLM