共用方式為

如何判斷驗證類型是否為 Kerberos

本文提供逐步指示，可協助您判斷連線到 Microsoft SQL Server 時所使用的驗證類型。 請確定您在用戶端電腦上執行步驟，而不是在您測試之 SQL Server 實例安裝所在的伺服器上。 否則，即使 Kerberos 已正確設定，輸出中的 auth_scheme 值一律會是 NTLM。 這是因為 Windows 2008 中新增的個別服務 SID 安全性強化功能。 不論 Kerberos 是否可用，此功能都會強制所有本機連線使用 NTLM。

使用 SQL Server Management Studio

  1. 開啟 SQL Server Management Studio 並連線到 SQL Server 實例。

  2. 執行下列查詢：

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

  3. 或者，若要擷取其他連線詳細數據，請執行下列查詢：

    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

  4. 檢閱結果中的 [auth_scheme] 數據行，以判斷驗證類型。

使用命令列

  1. 開啟命令提示字元。

  2. 執行下列命令，將 <ServerName> 取代為您的伺服器名稱：

    sqlcmd -S <ServerName> -E -Q "SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@SPID"

  3. 類似下列輸出的結果會指出驗證類型：

    auth_scheme
----------------------------------------
NTLM

(1 rows affected)

使用 VBScript

  1. 將下列 VBScript 程式代碼複製到文本編輯器，例如記事本，並將它儲存為 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. 從命令列執行下列命令，並將 <ServerName> 替換為您的伺服器名稱：

    cscript getAuthScheme.vbs <ServerName>

  3. 類似下列輸出的結果會指出驗證類型：

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

使用 Windows PowerShell

您可以使用 Windows PowerShell 來測試 SqlClient .NET 提供者，以嘗試將問題與應用程式隔離：

  1. 將下列 PowerShell 文稿複製到文字編輯器，例如記事本，並將它儲存為 get-SqlAuthScheme.ps1。

    #-------------------------------
#
# 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 lowercase)
#   .\get-SqlAuthScheme SQLProd01                        # Let the driver figure out the DNS suffix, protocol, and port
#
#-------------------------------
# Define a parameter for the server name, defaulting to "localhost" if not provided
param ([string]$server = "localhost")

# Set the execution policy for the current user to Unrestricted
Set-ExecutionPolicy -ExecutionPolicy Unrestricted -Scope CurrentUser -Force

# Build the connection string for the SQL Server connection
$connstr = "Server=$($server);Database=master;Integrated Security=SSPI"

# Create a new SQL connection object
$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = $connstr

# Record the start time of the operation
$start = Get-Date

# Open the SQL connection
$conn.Open()

# Create a new SQL command object
$cmd = $conn.CreateCommand()
$cmd.CommandText = "SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@SPID" # Query to get the authentication scheme

# Execute the query and retrieve the result
$dr = $cmd.ExecuteReader()
$dr.Read() | Out-Null # Read the first row of the result set
$auth_scheme = $dr.GetString(0) # Get the authentication scheme from the first column

# Close and dispose of the SQL connection
$conn.Close()
$conn.Dispose()

# Record the end time of the operation
$end = Get-Date

# Calculate the elapsed time
$span = $end - $start

# Output the results
Write-Output "Elapsed time was $($span.TotalMilliseconds) ms."    # Display the elapsed time in milliseconds
Write-Output "Auth scheme for $($server): $auth_scheme"   # Display the authentication scheme for the server

  2. 開啟 Windows PowerShell，瀏覽至包含文稿的資料夾，然後執行下列命令：

    .\get-sqlauthscheme <ServerName>  # Replace "<ServerName>" with your server name.

  3. 類似下列輸出的結果會指出驗證類型：

    Elapsed time was 0 ms.
Auth scheme for <ServerName>: NTLM

其他相關資訊