SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

Microsoft.Data.SqlClient 4.0 以降で SQL Server に接続する際の暗号化オプション

leave a comment

先日、Microsoft.Data.SqlClient 5.0 がリリースされました。

新機能については、次の情報からも確認することができます。

4.0 の破壊的変更に含まれるのですが、4.0 以降は接続文字列の「Encrypt」が「true」に設定されることがデフォルトとなりました。

Encrypt 接続文字列プロパティが既定で true になるように変更しました。 #1210詳細を読む

これにより、オンプレミスの SQL Server のような環境では、4.0 より前と同一の接続文字列で接続を行うと「 信頼されていない機関によって証明書チェーンが発行されました。」のエラーが発生する可能性があります。(Azure の PaaS については、信頼されている証明書による接続になっているので、PaaS を使っている場合は、本投稿は意識しないでもよかったはずです)

今回は、Windows PowerShell に Microsoft.Data.SqlClient 5.0 を使用できるようにして、この暗号化接続部分について動作を確認してみました。

Windows PowerShell で Microsoft.Data.SqlClient 5.0 を使えるようにするのは面倒ですが、動かしてみたい方は Microsoft.Data.SqlClient 5.0.0.ps1 を実行してみてください。

SQL Server の接続の暗号化のデフォルトの設定

SQL Server の初期状態で、サーバーの暗号化接続の設定を変更しなくても、SQL Server Native Clientでの検証なしの暗号化の使用 に記載されているように、自己署名証明書による、暗号化接続を使用することができるようになっています。(SQL Server SSL Encryption – SelfSign Cert working – why? も参考になります)

SQL Server は、常に、ログインに関連するネットワーク パケットを暗号化します。 サーバーの起動時に証明書がサーバーにプロビジョニングされていない場合、SQL Server は、ログイン パケットの暗号化に使用される自己署名証明書を生成します。

そのため、SQL Server 側で証明書を明示的に設定していなくても、クライアント側の接続文字列で暗号化接続が有効になっている (Encrypt=True) 場合は、SQL Server で自動的に生成された自己署名を使用して接続が行われます。

しかし、クライアント側では、SQL Server で使用されている自己署名は信頼された証明書期間から発行されたものとして認識されませんので、冒頭に記載した「信頼されていない機関による~」のエラーが発生します。

 

SQL Server の設定を変更せずにクライアント側で回避

SQL Server 側の設定を変更せずに、Microsoft.Data.SqlClient で接続を行うためには、接続文字列の設定として、次の二つの方法のいずれかをとることができます。

  • 「Encrypt=false」 を設定する
  • 「TrustServerCertificate=true」を設定する

暗号化接続を無効にするか、暗号化接続は有効な状態でクライアントで証明書を信頼するかのいずれかを設定することで、暗号化接続のエラーを抑制することができるようになります。

 

TDS 8.0 と組み合わせるためには

Microsoft.Data.SqlClient 5.0 では、新しく「Encrypt=Strict」という設定を使用することができるようになっています。

この設定を使用する場合「TrustServerCertificate=true」を使用することができないため、クライアントで信頼されている証明書を SQL Server で設定しておく必要があります。

「Encrypt=Strict」は SQL Server 2022 で新しく導入された「TDS 8.0」を使用するために必要な設定となります。

TDS 8.0 の検証を実施するためには、クライアントで信頼されている証明書を使用して、SQL Server に暗号化接続ができる状態にする必要があります。

 

自己署名証明書による SQL Server の暗号化接続設定

SQL Server の暗号化接続に使用する証明書の要件については次の情報に記載されています。

この情報には、検証用に自己署名証明書を使用することができる記載があり、実際に SQL Server の暗号化接続用に、New-SelfSignedCertificate で自己署名証明書を作成する方法として次のような情報も公開されています。

「C:\temp」ディレクトリがあることが前提ですが、次のようなスクリプトで SQL Server 向けの証明書を作成し、信頼された証明書としてインポートすることができます。(今回はサーバー / クライアントが同一環境となっています)

$cert = New-SelfSignedCertificate -Type SSLServerAuthentication `
-Subject "SQL Server TLS Certificate" `
-FriendlyName "SQL Server TLS Certificate" `
-DnsName "$env:COMPUTERNAME","localhost.",$(((Get-NetIPAddress) | Where-Object {$_.AddressFamily -eq "IPv4" -and $_.InterfaceAlias -notlike "*Loopback*"}).IPAddress) `
-KeyAlgorithm RSA -KeyLength 2048 -Hash 'SHA256' `
-TextExtension '2.5.29.37={text}1.3.6.1.5.5.7.3.1' `
-NotAfter (Get-Date).AddYears(30) `
-KeySpec KeyExchange `
-Provider "Microsoft RSA SChannel Cryptographic Provider" `
-CertStoreLocation "Cert:\LocalMachine\My"

$cert | Export-Certificate -Type CERT -FilePath "C:\temp\rootcertificate.cer"

Import-Certificate -CertStoreLocation "Cert:\LocalMachine\AuthRoot" -FilePath "C:\temp\rootcertificate.cer"

 

これで

  • サーバー向けの用途として、コンピューターの証明書として自己署名証明書を導入
    • サーバー側としてみた場合は、信頼された証明機関に設定されていなくても問題ありません。
  • クライアント向けの用途として、信頼された証明機関として自己署名証明書を導入

することができます。

SQL Server でコンピューターの証明書として導入されている自己署名証明書を使用するためには、証明書の MMCから、作成した証明書の「秘密キーの管理」から「SQL Server のサービスアカウントに対して読み取りの権限を付与」し、

image

SQL Server 構成マネージャーの「MSSQLSERVER のプロトコル」のプロパティから作成した証明書を使用するように設定し、SQL Server のサービスを再起動します。

image

想定通り設定ができていれば SQL Server のサービスが起動します。(秘密キーの管理でサービスアカウントにアクセス許可を忘れていると、サービスが起動できず ERRORLOG に証明書の読み取りのエラーが出力されます)

これで TDS 8.0 による接続ができるようになっているはずです。どの TDS のバージョンが使用されているは次のようなクエリで確認できます。

Clear-Host
$con = New-Object Microsoft.Data.SqlClient.Sqlconnection("Server=tcp:localhost;Application Name=Pwsh;Integrated Security=True;Encrypt=Strict")
$con.Open()

$cmd = $con.CreateCommand()
$cmd.CommandText ="SELECT @@VERSION"
$cmd.ExecuteScalar()

$cmd.CommandText ="select CONVERT(varchar(20), (CAST(protocol_version AS BINARY(4))),1) from sys.dm_exec_connections where session_id = @@SPID"
$cmd.ExecuteScalar()

$con.Dispose()

「protocol_version」が「0x80」になっていれば、TDS 8.0 で接続されています。

image

TDS 7.4 の場合は、「0x74」となっています。

image

 

まとめ

Microsoft.Data.SqlClient 4.0 以降は、暗号化接続のデフォルトが「Encrypt=true」となっており、それより前の接続文字列と同様の設定を使用していても、暗号化設定の違いにより、接続ができなくなっている可能性があります。

SQL Server では自己署名証明書を使用して暗号化接続を可能とする設定もできますので、SQL Server 2022 の新機能である TDS 8.0 の検証を実施したい場合には、サーバー側で証明書を使用した暗号化接続の有効化の設定方法も覚えておくとよいのではないでしょうか。

Share

Written by Masayuki.Ozawa

8月 31st, 2022 at 4:58 pm

Posted in SQL Server

Tagged with

Leave a Reply