WIndows版OpenSSLで自己証明書作成とMariaDB（MySQL）のSSL化／HeidiSQLからSSL接続
目次
- WIndows版OpenSSLで自己証明書作成とMariaDB（MySQL）のSSL化
- サーバー証明書の作成
- MariaDBサーバーのSSL化
- 同じ手順でもWindows Server 2019で実行すると結果が違う？？
WIndows版OpenSSLで自己証明書作成とMariaDB（MySQL）のSSL化
OpenSSLで自己証明書（オレオレ証明書）を作成して、MariaDBをSSL化します。
今回はWindows版のOpenSSLとMariaDBを使いました。
MariaDBはインストール済の前提です。
Windows版のOpenSSLがはいっていないなら、こちらからインストールします。
Win64のLight版をダウンロードします。
今回は「Win64OpenSSL_Light-3_0_1.msi」がダウンロードされたので、これでインストールします。
インストールはデフォルトを変更せずに「next」でいいです。
cmdをたちあげて「opsnSSL」とうって、パスが通っていることを確認します。
こんな風な表示になったのでOK・・いったん、Ctrl-Cで終わらせておきます。
サーバー証明書の作成
準備として、保存するフォルダを作り、そこをカレントフォルダにします。
cmdを管理者権限で立ち上げます。
（管理者権限以外で試したことがないので、必須かどうかはわかりませんが）
順序としては。
- 秘密キーの生成
- ルート認証局の証明書の作成
- 中間認証協の証明書の作成
- クライアント用証明書の作成
です。
手順1：秘密キーの生成
コマンドです。
openssl genrsa -out ca-key.key
デフォルトで鍵長は自動で 2048bit です。
できあがるファイルの内容はこんな感じのテキストです。
-----BEGIN RSA PRIVATE KEY-----
MIIEpAIBAAKCAQEAx3LXdIbmG6wU7AIELwM1vS3RUSxdK+4YTJKgS6EngV0BZ+Mc
LCNK7hol～ ・・・～ 7NTt83rKgEuG8FWciiaqitTq4VD+5WCMgacJMyzY5XFUEUnG
+..............................................................
-----END RSA PRIVATE KEY-----
手順２：ルート認証局の証明書の作成
秘密キーファイル（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」にありましたので、それで環境変数を設定しています。
うまく実行できたら、いろいろ聞いてきます。
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-----
手順3：中間認証局の証明書の作成
順番にコマンドを発行していきます。
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 :bokuPlease 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化しないといけないので、そのクライアント用の証明書とキーも作っておく必要があります。
手順4：クライアント証明書の作成
順番にコマンドを実行していきます。
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 :bokuPlease 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
これが正常終了したら、すべての証明書がそろったことになります。
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"
確認用にSSL接続強制のユーザを作る
ユーザ作成と権限付与時にSSL接続を強制するとしていない場合は、SSL接続を回避できてしまうので、確認用にSSL接続必須のユーザを作ります。
実運用するときは、ほぼSSL接続必須にするはずですから。
create user 'sslonly'@'localhost' identified by 'sslpass';
このユーザにALLの権限を与えるわけですが、その時にSSLオプションを指定するとSSL接続専用ユーザになります。
DB名は適当に「bltdb」とかにしてます。
では、このユーザでSSLオプションをつけないで接続してみます。
mysql -u sslonly -p bltdb
きちんとはじかれるのを確認します。
Access denied for user 'sslonly'@'localhost' (using password: YES)
SSLで接続してみる
「mysqlコマンド」で、SSLオプションをつけて接続します。
mysql -u sslonly -p bltdb --ssl-ca=C:\xampp\mysql\data\ca-cert.pem
上記の「ssl-ca」で指定したパスとファイルを指定して接続します。
接続できたら「status」コマンドで状況をみます。
おお。ちゃんと、SSLのところが「Cipher in use is」となっています。
念のため、SSL対応状況も確認します。
show variables like '%ssl%';
have_sslがYESになってます・・とりあえず、OKとしときます。
HeidiSQLでSSL接続してみる
今度は以下のクライアント配布用のファイルを使います。
client-cert.pem
client-key.pem
これらのファイルを適当なところにコピーします。
今回は「cert」というフォルダにおきました。
HeidiSQLの接続設定をする際に、SSLタグも設定します。
SSLを使用するにチェックをつけます。
SSL秘密鍵に「client-key.pem」を指定します。
SSL証明書に「client-cert.pem」を指定します。
もちろん、どちらもフルパスで指定が必要です。
これで接続してみます。
いけました。
同じ手順でもWindows Server 2019で実行すると結果が違う？？
Windows10で実行すると上記の通りでうまくいきました。
ところがAmazonのEC2（Windows Server 2019）上で同じようにやると、MariaDBでSSLを有効にすることができませんでした。
同じコマンドを実行しても、キーファイルが違う形式でできてしまいます。
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形式のキーファイルを出力する方法はわかっていません。
とりあえず、情報として追記しておきます。
ではでは。
