"BOKU"のITな日常

BOKUが勉強したり、考えたことを頭の整理を兼ねてまとめてます。

WIndows版OpenSSLの自己証明書でMariaDB(MySQL)をSSL化する手順/HeidiSQLからSSL接続

f:id:arakan_no_boku:20211207220619p:plain

目次

Windows版OpenSSLインストール

Windows版のOpenSSLはこちらからインストールします。

slproweb.com

Win64のLight版をダウンロードします。

f:id:arakan_no_boku:20211227164200p:plain

今回は「Win64OpenSSL_Light-3_0_1.msi」がダウンロードされたので、これでインストールします。

インストールはデフォルトを変更せずに「next」でいいです。

cmdをたちあげて「opsnSSL」とうって、パスが通っていることを確認します。

f:id:arakan_no_boku:20211227164720p:plain

こんな風な表示になったのでOK・・いったん、Ctrl-Cで終わらせておきます。

 

手順1:サーバー証明書の作成

準備として、保存するフォルダを作り、そこをカレントフォルダにします。

cmdを管理者権限で立ち上げます。

(管理者権限以外で試したことがないので、必須かどうかはわかりませんが)

順序としては。

  1. 秘密キーの生成
  2. ルート認証局の証明書の作成
  3. 中間認証協の証明書の作成
  4. クライアント用証明書の作成

です。

 

手順2:秘密キーの生成

コマンドです。

openssl genrsa -out ca-key.key

デフォルトで鍵長は自動で 2048bit です。

できあがるファイルの内容はこんな感じのテキストです。

-----BEGIN RSA PRIVATE KEY-----
MIIEpAIBAAKCAQEAx3LXdIbmG6wU7AIELwM1vS3RUSxdK+4YTJKgS6EngV0BZ+Mc
LCNK7hol~ ・・・~ 7NTt83rKgEuG8FWciiaqitTq4VD+5WCMgacJMyzY5XFUEUnG
+..............................................................
-----END RSA PRIVATE KEY-----

 

手順3:ルート認証局の証明書の作成

秘密キーファイル(ca-key.key)を使って、証明書署名要求ファイル(ca-cert.pem)を作ります。

コマンドは。

openssl req -new -x509 -nodes -days 3650 -key ca-key.key -out ca-cert.pem

です。

この時「OPENSSL_CONF]環境変数に「openssl.cnf」ファイルへのパスが正しく設定されていないと、以下のようなエラーになります。

Can't open C:\・・・・/openssl.cnf for reading, No such file or directory

とりあえず、ファイルを探してPathの設定が必要です。

自分の環境だと「openssl.cnf」は「C:\Program Files\Common Files\SSL」にありましたので、それで環境変数を設定しています。

f:id:arakan_no_boku:20211227230155p:plain

うまく実行できたら、いろいろ聞いてきます。

Country Name (2 letter code) [AU]:jp
State or Province Name (full name) [Some-State]:oosaka
Locality Name (eg, city) :hogehoge
Organization Name (eg, company) [Internet Widgits Pty Ltd]:hogehoge
Organizational Unit Name (eg, section)
:hoge
Common Name (e.g. server FQDN or YOUR name) :boku
Email Address
:boku

自分は自分のPC内で動作確認に使うだけなので、かなり適当ですが、仕事でテスト環境などに使う場合は真面目にいれたほうがいいと思います。

できあがった「ca-cert.pem」の内容は、こんな感じのテキストです。

-----BEGIN CERTIFICATE-----
MIIDzzCCAregAwIBAgIUQtaYmC1t0p3ZRxZFbhvw6zsIhe4wDQYJKoZIhvcNAQEL
BQA ~・・・~wdzELMAkGA1UEBhMCanAxDzANBgNVBAgMBm9vc2FrYTERMA8G
-----END CERTIFICATE-----

 

手順4:中間認証局の証明書の作成

順番にコマンドを発行していきます。

days 3650なので、10年間有効な証明書にしています。

openssl req -newkey rsa:2048 -days 3650 -nodes -keyout server-key.pem -out server-req.pem

いろいろ、聞いてきます。

Country Name (2 letter code) [AU]:jp
State or Province Name (full name) [Some-State]:oosaka
Locality Name (eg, city) :hogehoge
Organization Name (eg, company) [Internet Widgits Pty Ltd]:hogehoge
Organizational Unit Name (eg, section)
:hoge
Common Name (e.g. server FQDN or YOUR name) :boku
Email Address
:boku

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password :bokupass
An optional company name
:hogehoge

前半はルート証明書と同じような内容です。

ルートと中間なので、同じである必要はないと思うのですが、今回は同じ内容を入力してみました。

最後に「challenge password」を聞いてくるので、ここに入力した内容を忘れないようにしたほうがいいです・・今回は使わないですけど。

次は

openssl rsa -in server-key.pem -out server-key.pem

これは一瞬で終わります。

続けて以下を実行します。

openssl x509 -req -in server-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.key -set_serial 01 -out server-cert.pem

うまく実行できたら、

Signature ok
Getting CA Private Key

みたいなメッセージで終わります。

この時点でできている「server-cert.pem」は

-----BEGIN CERTIFICATE-----
MIIDYjCCAkoCAQEwDQYJKoZIhvcNAQELBQAwdzELMAkGA1UEBhMCanAxDzANBgNV
BAgMBm~ ・・~MA8GA1UEBwwIaG9nZWhvZ2UxETAPBgNVBAoMCGhvZ2Vob2dl
-----END CERTIFICATE-----

もう一つ「server-key.pem」の内容は

-----BEGIN RSA PRIVATE KEY-----
MIIEpAIBAAKCAQEAyc3BziF6eEJDjlsh3yhrKuGnsHKTrkPzBjuFMjuXgOfLUDA7
nbAs ~・・~ 6Y0L3xECVyf74PDGlxGKhbrkezcBuBqw5n6O+ArPZH075Xw
-----END RSA PRIVATE KEY-----

両方とも、テキストで臨む形でできてます。

これでMariaDBサーバーをSSL化するのに必要な証明書ファイルはそろいました。

でも、MariaDBサーバーをSSL化したら、HeidiSQLなどのクライアントからのアクセスもSSL化しないといけないので、そのクライアント用の証明書とキーも作っておく必要があります。

 

手順5:クライアント証明書の作成

順番にコマンドを実行していきます。

openssl req -newkey rsa:2048 -days 3650 -nodes -keyout client-key.pem -out client-req.pem

前2回と同じように聞いてくるので、同じ内容を入力しておきます。

(別に変えてもいいかもしれませんが、やったことないので)

Country Name (2 letter code) [AU]:jp
State or Province Name (full name) [Some-State]:oosaka
Locality Name (eg, city) :hogehoge
Organization Name (eg, company) [Internet Widgits Pty Ltd]:hogehoge
Organizational Unit Name (eg, section)
:hoge
Common Name (e.g. server FQDN or YOUR name) :boku
Email Address
:boku

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password :bokupass
An optional company name
:hogehoge

続いて

openssl rsa -in client-key.pem -out client-key.pem

さらに

openssl x509 -req -in client-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.key -set_serial 01 -out client-cert.pem

これが正常終了したら、すべての証明書がそろったことになります。

 

手順6:MariaDBサーバーのSSL

今回はXAMPP付属のMariaDB(ver10.4.6)で動作確認します。

mysql.iniのあるフォルダ(../mysql/data)に作成したファイルのうち

  • ca-cert.pem
  • server-cert.pem
  • server-key.pem

をコピーし、mysql.iniに以下を記入して、再起動します。

例として、C:¥直下にXAMPPをインストールしている環境のケースを書いてます。

パスは環境にあわせて修正が必要なのは、いうまでもなく・・。

ssl-ca = "C:/xampp/mysql/data/ca-cert.pem"
ssl-cert = "C:/xampp/mysql/data/server-cert.pem"
ssl-key = "C:/xampp/mysql/data/server-key.pem"

 

手順7:確認用にSSL接続強制のユーザを作る

ユーザ作成と権限付与時にSSL接続を強制するとしていない場合は、SSL接続を回避できてしまうので、確認用にSSL接続必須のユーザを作ります。

実運用するときは、ほぼSSL接続必須にするはずですから。

 create user 'sslonly'@'localhost' identified by 'sslpass';

このユーザにALLの権限を与えるわけですが、その時にSSLオプションを指定するとSSL接続専用ユーザになります。

DB名は適当に「bltdb」とかにしてます。

 grant all on bltdb.* to sslonly@localhost require ssl;

では、このユーザでSSLオプションをつけないで接続してみます。

mysql -u sslonly -p bltdb

きちんとはじかれるのを確認します。

Access denied for user 'sslonly'@'localhost' (using password: YES)

 

手順8:SSLで接続してみる

mysqlコマンド」で、SSLオプションをつけて接続します。

mysql -u sslonly -p bltdb --ssl-ca=C:\xampp\mysql\data\ca-cert.pem

上記の「ssl-ca」で指定したパスとファイルを指定して接続します。

接続できたら「status」コマンドで状況をみます。

f:id:arakan_no_boku:20211228182211p:plain

おお。ちゃんと、SSLのところが「Cipher in use is」となっています。

念のため、SSL対応状況も確認します。

show variables like '%ssl%';

f:id:arakan_no_boku:20211228182303p:plain

have_sslがYESになってます・・とりあえず、OKとしときます。

 

HeidiSQLでSSL接続してみる

今度は以下のクライアント配布用のファイルを使います。

client-cert.pem

client-key.pem

これらのファイルを適当なところにコピーします。

今回は「cert」というフォルダにおきました。

HeidiSQLの接続設定をする際に、SSLタグも設定します。

f:id:arakan_no_boku:20211228183052p:plain

SSLを使用するにチェックをつけます。

SSL秘密鍵に「client-key.pem」を指定します。

SSL証明書に「client-cert.pem」を指定します。

もちろん、どちらもフルパスで指定が必要です。

これで接続してみます。

f:id:arakan_no_boku:20211228183509p:plain

いけました。

 

同じ手順でもWindows Server 2019で実行すると結果が違う??

Windows10で実行すると上記の通りでうまくいきました。

ところがAmazonのEC2(Windows Server 2019)上で同じようにやると、MariaDBSSLを有効にすることができませんでした。

同じコマンドを実行しても、キーファイルが違う形式でできてしまいます。

Windows10の場合は「-----BEGIN RSA PRIVATE KEY-----」のPKCS#1形式で生成されるのですが、EC2で実行したときはPKCS#8形式「-----BEGIN PRIVATE KEY-----」になっているなど、微妙に違います。

PKCS#1形式は暗号化にRSAアルゴリズムを使用することが特徴で、PKCS#8形式は、 は、RSAだけでなく、すべてのアルゴリズム秘密鍵を処理するための標準です。

どう考えても、PKCS#8の方が新しいのですが、MariaDB(少なくとも、自分が指定されたバージョン)ではPKCS#8形式に対応していませんでした。

なので、PKCS#1形式にコンバートしないといけないと思って

openssl rsa –in pkcs8_key.pem –out pkcs1_key.pem

とかやってみたのですが、うまくいきませんでした。

結局、その時はWindows10で証明書とキーファイルを作り直して、EC2にコピーするというズルをしてにげたのですが・・いや、はまりました。

PKCS#1出力を強制するopensslのオプションがないかと探したりしてますが、今のところ、EC2(Windows Server 2019)上で、Windows版のopensslを動かして、PKCS#1形式のキーファイルを出力する方法はわかっていません。

とりあえず、情報として追記しておきます。

ではでは。