SE の雑記

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

Makecert を使用した SQL Server の接続暗号化用の自己署名証明書の作成

leave a comment

SQL Database ではデフォルトで使用することができますが、SQL Server では接続を暗号化するための証明書を使用することができます。
SQL Server への接続の暗号化

RDS for SQL Server でもサポートされていますね。
【AWS発表】Amazon RDS – SQL Server が SSL のサポートを開始

検証用に自己署名証明書を使用して接続を暗号化するための設定をまとめてみたいと思います。

自己署名証明書の作成には makecert を使用しており、使用方法については、以下が参考になります。

【Azure for ITPRO】自己署名証明書を作成する
付録 B: Windows Azure Service Management API の使用
SSL に使用する証明書の構成
Makecert.exe (証明書作成ツール)

暗号化した接続については以下の技術情報が参考になります。
SQL Server fails to start with error 17182 “TDSSNIClient initialization failed with error 0xd, status code 0x38” when server is configured to use SSL
When using SSL, SQL Failover Cluster Instance fails to start with error 17182
TDSSNIClient initialization failed with error 0xd, status code 0x36.

■Makecert のインストール


Makecert のインストールですが、Windows SDK をインストールすることで使用することができます。
Windows SDK は以下からダウンロードすることができ、本投稿では Windows 8.1 向けの SDK を使用しています。
Microsoft Windows SDK for Windows 7 and .NET Framework 4
Windows 8 用 Windows ソフトウェア開発キット (Windows SDK)
Windows SDK for Windows 8.1

Windows SDK のインストールですが、「Windows Software Develepment Kit」をインストールすることで利用できます。

image

インストールをすると「C:Program Files (x86)Windows Kits8.1bin<プロセッサアーキテクチャ>」に配置されますので、実行時にはこのパスから EXE を起動します。

■自己署名証明書の作成


Makecert のインストールが完了したら自己署名証明書を作成します。

Makecert を使用した自己署名証明書は以下のようなコマンドで作成ができます。
秘密キーを含む必要がありますので、cer と pvk を合わせた PFX 形式の証明書を生成しています。
# makecert を実行した際に、パスワードを指定した場合は pvk2pfx を実行する際に 「–pi <パスワード>」のオプションを指定します。

makecert -r -pe -n "CN=%COMPUTERNAME%" -b 01/01/2000 -e 01/01/2100 -eku 1.3.6.1.5.5.7.3.1 -len 2048 -sp "Microsoft Enhanced RSA and AES Cryptographic Provider" -sy 24 -sky exchange "c:\certtemp\testcert.cer" -sv "c:\certtemp\testcert.pvk"
pvk2pfx -pvk "c:\certtemp\testcert.pvk" -spc "c:\certtemp\testcert.cer" -pfx "c:\certtemp\testcert.pfx"

これで PFX の証明書ができますので、MMC で証明書のスナップインをコンピューター アカウントの個人に対して取り込みを行います。

■SQL Server の接続の証明書として設定


SQL Server の接続の証明書の設定については「SQL Server 構成マネージャー」から設定することができます。
「SQL Server ネットワークの構成」から、接続を暗号化したいインスタンスを右クリックして「プロパティ」を開きます。

image

「証明書」タブから接続の暗号化に使用する証明書を選択します。
image

先ほどの Makecert で作成した証明書ですが、コンピューター名で作成をしています。
今回の環境はドメインに参加している環境なのですが、この場合追加した証明書を選択することができません。

image

証明書ですが、ドメインに参加している環境については FQDN の証明書でないと GUI から選択することができないようです。
先ほどの Makecert のコマンドを

makecert -r -pe -n "CN=&lt;FQDN&gt" -b 01/01/2000 -e 01/01/2100 -eku 1.3.6.1.5.5.7.3.1 -len 2048 -sp "Microsoft Enhanced RSA and AES Cryptographic Provider" -sy 24 -sky exchange "c:\certtemp\testcert.cer" -sv "c:\certtemp\testcert.pvk"
pvk2pfx -pvk "c:\certtemp\testcert.pvk" -spc "c:\certtemp\testcert.cer" -pfx "C:\certtemp\testcert.pfx"

のような形式で FQDN の証明書を作成することで、GUI から選択することができます。

image

設定後にサービスを再起動することで、暗号化した接続が可能になります。

サービスの再起動が失敗した場合、インポートした証明書の秘密キーのアクセス権を「秘密キーの管理」から確認をしたほうがよいかも知れません。
SQL Server のサービスアカウントに対して秘密キーの読み取り権限がない場合は、サービスの起動に失敗しますので。
image

ただし、自己署名証明書の場合、信頼されていない証明書となるため、以下のエラーが発生してしまいます。
# 個人の証明書ストアに証明書をインポートしても信頼された証明書とならないため。
imageimage

接続元の「信頼されたルート証明機関」に、Makecert で生成された「.cer」の証明書をインポートすることで、暗号化された接続により接続が可能となります。

「sys.dm_exec_connections」の「encrypt_option」を確認すると「TRUE」になっているのが確認できますね。

image

■GUI から選択できない証明書を設定


証明書の共通名の設定内容によっては GUI から選択できない状態が発生しますが、接続の方法によってはサーバー名やサーバー名の FQDN 以外の共通名を使用しなくてはいけないケースも実際にはあると思います。

証明書の設定ですがレジストリに設定がされており、GUI から選択できない証明書についてはレジストリを直接変更することで設定する必要があります。

接続の暗号化に使用している証明書は「HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL Server<インスタンスID>MSSQLServerSuperSocketNetLib」の「Certificate」に拇印が設定されています。

image

このレジストリに直接、接続の暗号化に使用する証明書の拇印を設定することで、GUI から選択できない証明書を設定することができます。

証明書の拇印の取得ですが、

Get-ChildItem Cert:LocalMachineMy

で取得したほうがよいかと思います。

image

MMC の証明書のスナップインから拇印をコピーして、スペースを除いた形式を使用した場合、先頭に認識できない文字(コード?)が入ってしまっているようで、レジストリにうまく設定ができないケースがあります。
# メモ帳に張り付けて確認した場合は、正常に見えるのですが、秀丸等のエディタに張り付けた場合は、先頭に文字が含まれた状態で認識がされるケースがあります。
image

証明書の拇印が正しく設定されていない、証明書が正しくない場合などは以下のようなエラーが ERRORLOG に出力されます。

      <br />2015-01-13 22:11:03.38 spid8s SQL Trace ID 1 was started by login &amp;quot;sa&amp;quot;.       <br />2015-01-13 22:11:03.38 spid15s Error: 17182, Severity: 16, State: 1.       <br />2015-01-13 22:11:03.38 spid15s TDSSNIClient initialization failed with error 0xd, status code 0x38. Reason: An error occurred while obtaining or using the certificate for SSL. Check settings in Configuration Manager. The data is invalid.       <br />2015-01-13 22:11:03.39 spid15s Error: 17182, Severity: 16, State: 1.       <br />2015-01-13 22:11:03.39 spid15s TDSSNIClient initialization failed with error 0xd, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. The data is invalid.       <br />2015-01-13 22:11:03.39 spid15s Error: 17826, Severity: 18, State: 3.       <br />2015-01-13 22:11:03.39 spid15s Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.       <br />2015-01-13 22:11:03.39 spid15s Error: 17120, Severity: 16, State: 1.       <br />2015-01-13 22:11:03.39 spid15s SQL Server could not spawn FRunCommunicationsManager thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.       <br />

この場合はレジストリの拇印の設定に余計な文字が含まれていないかをきちんと確認したほうがよいかと思います。
# PowerShell の出力結果をコピー&ペーストすれば余計な文字は含まれていなかったかと。

接続に使用する証明書は「暗号化キーが含まれたサーバー認証用の証明書」を「ローカル コンピューター」の「個人」に設定すれば使用することができますので、一般的な証明機関から発行された証明書も使用することができます。
# EZ ドメインで取得した「EssentialSSL Wildcard」の証明書も設定することができました。

Written by masayuki.ozawa

1月 13th, 2015 at 9:07 pm

Posted in SQL Server

Tagged with

Leave a Reply

*