Skip to content

Latest commit

 

History

History
148 lines (122 loc) · 6.1 KB

determine-the-authentication-type.md

File metadata and controls

148 lines (122 loc) · 6.1 KB
Raw
title description ms.date author ms.author ms.reviewer ms.custom ms.topic
Determine the authentication type
This article explains about how to determine the type of authentication that's used when you connect to SQL Server.
02/20/2024
Malcolm-Stewart
mastewa
jopilov, haiyingyu, prmadhes, v-jayaramanp
sap:Database Connectivity and Authentication
how-to

How to determine if the authentication type is Kerberos

This article provides a query to help you determine the type of authentication that's used when you connect to Microsoft SQL Server. Make sure that you run the query on a client computer, not on the SQL Server that you're testing. Otherwise the query returns auth_scheme as NTLM even if Kerberos is configured correctly. This occurs because of a per-service SID security hardening feature that was added in Windows 2008. This feature forces all local connections to use NTLM regardless of whether Kerberos is available.

 SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id=@@SPID

Use SQL Server Management Studio

Run the following query in SQL Server Management Studio:

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

Use the command line

Run the following query at a command prompt:

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)

Alternative method

If either of the previous options aren't available, consider using the following alternative procedure:

  1. Copy the following script into a text editor, such as Notepad, and save it 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

  2. Run the getAuthScheme.vbs PowerShell script at a command prompt:

    C:\Temp>cscript getAuthScheme.vbs SQLProd01

    You should see the following output:

    Microsoft (R) Windows Script Host Version 5.812
Copyright (C) Microsoft Corporation. All rights reserved.
Auth scheme: NTLM

Use PowerShell

You can use PowerShell to test the SqlClient .NET provider to try to isolate the issue from your application:

  1. Copy the following script into a text editor, such as Notepad, and save it as get-SqlAuthScheme.ps1.

  2. Run the following script at a command prompt:

    #-------------------------------
#
# 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

You should see the following output:

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

More information

Consistent authentication issues in SQL Server