こんにちは、管理人の@Salesforce.Zです。
DataspideのMySQLアダプターやJDBCアダプターで、GCPのSQLにSSLで接続したい、しかしできない
Access defined for user 'xxxxx'@'xx.xx.xx.xxx'(using password: YES)
java.sql.SQLException: Access denied for user 'mysqluser'@'x.x.x.x' (using password: YES)
しか表示できない
ユーザ名とパスワードが正しいだ。なぜなら、セキュア通信ではない場合、接続できるか
ということで、今回、こんな悩みを解消できるソリューションを徹底的に共有する。
★ Google Cloud PlatformのSQLの接続エラー「Access defined for user 'xxxxx'@'xx.xx.xx.xxx'(using password: YES)」の対策/
目次
問題点
DataSpiderや、Railsなど、HeidiSQLで、GCPのSQLに接続したい、そしてセキュア通信(SSL)でやり取りしたいだができない
なにができないかというと、証明書をキーストアにインポートしても、「Access defined for user 'xxxxx'@'xx.xx.xx.xxx'(using password: YES)」が出続ける。
ログイン情報が正しい前提で、対策に進む
GCPのSQLから発行される証明書
・server-ca.pem という名前で保存されたサーバー証明書
・client-cert.pem という名前で保存されたクライアントの公開鍵証明書。
・client-key.pem という名前で保存されたクライアント秘密鍵
証明書の使い方
//SSL を使用してインスタンスに接続するには: //MySQL クライアントを起動します。 mysql --ssl-ca=server-ca.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem \ --host=[INSTANCE_IP] --user=root --password
3つ証明書を全部セットしないと行けないということになる
サーバー証明書とクライアント証明書(PKCS12にしても)をインポートしたら、エラーが変わらない ⬇⬇⬇ この問題なら、この記事で解消できる
対策
DBeaverツールなら、3つ証明書を設定できるので、簡単にSSL通信できる。
DataSpiderなら、3つ証明書をアダプターから直接設定できない問題点がある。 それに対して、キーストアにサーバー証明書server-ca.pemをインポートできる
しかし、client-key.pem秘密鍵をキーストアに入れられない。
そこで、client-cert.pem(クライアントの公開鍵証明書)とclient-key.pem(クライアント秘密鍵)を合わせてPKCS12形式にすれば、インポートできる
//server-ca.pem (サーバー証明書、名前通り:サーバーが提示する証明書) $ keytool -import -file ./server-ca.pem -alias <なにか識別名> -keystore <入れ物のファイル名> //クライアント証明書を1つファイルにする(クライアント側が提示する証明書) $ openssl pkcs12 -export -inkey ./client-key.pem -in ./client-cert.pem -name <なにか識別名> -out ./temp.p12 $ keytool -importkeystore -srckeystore ./temp.p12 -srcstoretype pkcs12 -destkeystore <入れ物のファイル名>
こんなコマンドを見たら、簡単やんと思われるかもしれないが、実はファイルパスにハマってしまうこともあるぞ。
ここまで、DataSpiderでの証明書インポートを解決に過ぎない。
次は、キーストアとトラストストアのパス指定がポイントだ。
なぜなら、証明書をキーストアにインポートしても、keytoolはJDKなどに付属してくるツールで、パスが通ってないことで接続エラーになるだ
「最後に」のセクションでMySQLの公式ドキュメントリンクを貼り付けている、目を通しら、分かるはず(英語なので、抵抗感があると思われ、ここにも抜粋して掲載する)
公式ドキュメントの記載だと下記になる
1.サーバー証明書をキーストアにインポート(truststore あるいは cacertsファイルに)
MySQLCACert :証明書の別名 任意文字設定すれば良い
ca.pem:GCPのSQLのサーバー証明書名(server-ca.pem)
truststore:トラストストアのファイル名(Dataspiderなら、${dataspider.home}\jre\lib\security\cacertsファイルと同じのこと)
mypassword:普通はchangeitとする(デフォルト)
$ keytool -importcert -alias MySQLCACert -file ca.pem -keystore truststore -storepass mypassword
インポート成功したら、下記になる
Owner: CN=MySQL_Server_5.7.17_Auto_Generated_CA_Certificate Issuer: CN=MySQL_Server_5.7.17_Auto_Generated_CA_Certificate Serial number: 1 Valid from: Thu Feb 16 11:42:43 EST 2017 until: Sun Feb 14 11:42:43 EST 2027 Certificate fingerprints: MD5: 18:87:97:37:EA:CB:0B:5A:24:AB:27:76:45:A4:78:C1 SHA1: 2B:0D:D9:69:2C:99:BF:1E:2A:25:4E:8D:2D:38:B8:70:66:47:FA:ED SHA256: C3:29:67:1B:E5:37:06:F7:A9:93:DF:C7:B3:27:5E:09:C7:FD:EE:2D:18:86:F4:9C:40:D8:26:CB:DA:95:A0:24 Signature algorithm name: SHA256withRSA Subject Public Key Algorithm: 2048-bit RSA key Version: 1 Trust this certificate? [no]: yes Certificate was added to keystore
ここまで、truststoreファイルにサーバー証明書をインポートした。 あるいはcacertsにインポートした。
※自分で、truststoreファイルを任意場所に作成しても良い(拡張子がなし)
サーバー証明書を入れたtruststore/cacertsファイルのパス変更
1. Java command lineで指定しパス変更
私、windowsでうまくできなかった。
-Djavax.net.ssl.trustStore=path_to_truststore_file -Djavax.net.ssl.trustStorePassword=mypassword
2.コードで直接システムプロパティを指定しパス変更 私、javaアプリを開発ではないため、これを使っていない
System.setProperty("javax.net.ssl.trustStore","path_to_truststore_file"); System.setProperty("javax.net.ssl.trustStorePassword","mypassword");
3.Connector/J コネクタープロパティを指定しパス変更 これを使用し、truststoreファイルのパスを変更した。
trustCertificateKeyStoreUrl=file:path_to_truststore_file trustCertificateKeyStorePassword=mypassword
2.GCPのクライアント秘密鍵とクライアント公開鍵をPKCS #12形式に変換しキーストアにインポート(keystroreファイルに)
GCPのクライアント秘密鍵とクライアント公開鍵をPKCS #12形式に変換
client-cert.pem:GCPクライアント公開鍵証明書(client-cert.pem)
client-key.pem:GCPクライアント秘密鍵(client-key.pem)
mysqlclient:任意の文字列
client-keystore.p12:出力したファイル名(任意指定、p12は決まり)
$ openssl pkcs12 -export -inkey ./client-key.pem -in ./client-cert.pem -name <なにか識別名> -out ./temp.p12 ⬇⬇⬇ $openssl pkcs12 -export -in client-cert.pem -inkey client-key.pem -name "mysqlclient" -passout pass:mypassword -out client-keystore.p12
PKCS #12形式ファイルをキーストアにインポート(keystroreファイルに)
$ keytool -importkeystore -srckeystore ./temp.p12 -srcstoretype pkcs12 -destkeystore <入れ物のファイル名> ⬇⬇⬇ $keytool -importkeystore -srckeystore client-keystore.p12 -srcstoretype pkcs12 -srcstorepass mypassword -destkeystore keystore -deststoretype JKS -deststorepass mypassword
keystroreファイルのパス変更
1. Java command lineで指定しパス変更
私、windowsでうまくできなかった。
-Djavax.net.ssl.keyStore=path_to_keystore_file -Djavax.net.ssl.keyStorePassword=mypassword
2.コードで直接システムプロパティを指定しパス変更 私、javaアプリを開発ではないため、これを使っていない
System.setProperty("javax.net.ssl.keyStore","path_to_keystore_file"); System.setProperty("javax.net.ssl.keyStorePassword","mypassword");
3.Connector/J コネクタープロパティを指定しパス変更 これを使用し、truststoreファイルのパスを変更した。
clientCertificateKeyStoreUrl=file:path_to_truststore_file clientCertificateKeyStorePassword=mypassword
よし、これで行けるはず
まとめると、
サーバー証明書をtruststoreファイルにインポート ⬇⬇⬇ truststoreファイルのパス変更
クライアント証明書2つをp12ファイルにマージする
作ったp12をkeystoreファイルにインポート ⬇⬇⬇ keystoreファイルのパス変更
パスがちゃんと指定できないで、ずっと解消できない原因になる
最後に
5.7 Connecting Securely Using SSL
JDBCでSSLを有効にした複数のMySQLインスタンスに接続する
MySQL(Google Cloud SQL): User Access denied only from Java
おまけな参考先