認証の種類が Kerberos かどうかを判断する方法

この記事では、Microsoft SQL Server に接続するときに使用される認証の種類を判断するのに役立つクエリを提供します。 テストする SQL Server ではなく、クライアント コンピューターでクエリを実行してください。 それ以外の場合、Kerberos が正しく構成されている場合でも、クエリは NTLM として返auth_schemeされます。 これは、Windows 2008 で追加されたサービスごとの SID セキュリティ強化機能が原因で発生します。 この機能により、Kerberos が使用可能かどうかに関係なく、すべてのローカル接続で NTLM が強制的に使用されます。

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

SQL Server Management Studio を使用する

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

コマンド ラインを使用する

コマンド プロンプトで次のクエリを実行します。

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)

代替方法

上記のオプションのいずれかが使用できない場合は、次の別の手順を使用することを検討してください。

1. 次のスクリプトをメモ帳などのテキスト エディターにコピーし、 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. コマンド プロンプトで getAuthScheme.vbs PowerShell スクリプトを実行します。

   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 を使う

PowerShell を使用して、SqlClient .NET プロバイダーをテストして、アプリケーションから問題を分離することができます。

1. 次のスクリプトをメモ帳などのテキスト エディターにコピーし、 get-SqlAuthScheme.ps1として保存します。

2. コマンド プロンプトで次のスクリプトを実行します。

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

次の出力が表示されます。

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

詳細

SQL Server での一貫した認証の問題