こんにちは、ハンディです。
今までEmbulkでSQL Serverからデータを取得して別のSQL Serverに入れてきましたが、その通信って平文で行われてるの？というのがふと気になりましたので、確認してみました。
もし確認方法が間違っていたりしたらご指摘ください。(この辺りはあまり詳しくないので。。)
実施作業
事前情報
通信が平文か同課の判断は、Wiresharkで実際に実行されているSQL文が確認できるかどうかで判断したいと思います。
EmbulkとSQL Serverの詳細は以前の記事と同じものを使用するので、ここでは省略します。
※Wiresharkって何ぞや？って人は、さくらのナレッジさんの記事でインストール方法含めて書いてあるので、こちらをご確認ください。
knowledge.sakura.ad.jp
※Wiresharkはここからダウンロードしました。
www.wireshark.org
Embulk実施内容
取得元のSQL Serverは以下になります。
このNo.31~40のデータが連携対象です。
増分データの連携先のSQL Serverが以下になります。
まだ連携されていないので、No.30までのデータしか入っていません。
インストールしたWiresharkは起動しておき、表示するキャプチャーをSQL ServerをインストールしているIPでFilterしておきます。
※IPアドレス
取得元：10.10.1.231
連携先：10.10.1.232
で、諸々省略してEmbulkを実行した結果が以下です。
今回はこの処理の通信についてみていきます。
一応テキストでも貼っておきます。
2020-03-25 16:00:49.449 +0900: Embulk v0.9.23
2020-03-25 16:00:53.542 +0900 [WARN] (main): DEPRECATION: JRuby org.jruby.embed.ScriptingContainer is directly injected.
2020-03-25 16:01:13.157 +0900 [INFO] (main): Gem's home and path are set by default: "C:\Users\Administrator\.embulk\lib\gems"
2020-03-25 16:01:20.329 +0900 [INFO] (main): Started Embulk v0.9.23
2020-03-25 16:01:21.154 +0900 [INFO] (0001:transaction): Loaded plugin embulk-input-sqlserver (0.10.1)
2020-03-25 16:01:21.404 +0900 [INFO] (0001:transaction): Loaded plugin embulk-output-sqlserver (0.8.7)
2020-03-25 16:01:21.938 +0900 [INFO] (0001:transaction): Connecting to jdbc:sqlserver://EC2AMAZ-JGN0VFT:1433 options {user=dbuser, password=***, applicationName=embulk-input-sqlserver, databaseN
ame=TESTDB, loginTimeout=300, socketTimeout=1800000}
2020-03-25 16:01:23.676 +0900 [INFO] (0001:transaction): Using JDBC Driver 7.2.2.0
2020-03-25 16:01:24.313 +0900 [INFO] (0001:transaction): Using local thread executor with max_threads=2 / tasks=1
2020-03-25 16:01:24.669 +0900 [INFO] (0001:transaction): Connecting to jdbc:sqlserver://EC2AMAZ-L2BKFDH:1433;databaseName=TESTDB options {user=dbuser, password=***}
2020-03-25 16:01:25.305 +0900 [INFO] (0001:transaction): TransactionIsolation=read_committed
2020-03-25 16:01:25.310 +0900 [INFO] (0001:transaction): Using JDBC Driver 7.2.2.0
2020-03-25 16:01:25.310 +0900 [INFO] (0001:transaction): Using insert_direct mode
2020-03-25 16:01:26.314 +0900 [INFO] (0001:transaction): {done: 0 / 1, running: 0}
2020-03-25 16:01:26.497 +0900 [INFO] (0014:task-0000): Connecting to jdbc:sqlserver://EC2AMAZ-L2BKFDH:1433;databaseName=TESTDB options {user=dbuser, password=***}
2020-03-25 16:01:26.723 +0900 [INFO] (0014:task-0000): TransactionIsolation=read_committed
2020-03-25 16:01:26.732 +0900 [INFO] (0014:task-0000): Prepared SQL: INSERT INTO "dbo"."Employee" ("No", "Name", "RegDate") VALUES (?, ?, ?)
2020-03-25 16:01:27.222 +0900 [INFO] (0014:task-0000): Connecting to jdbc:sqlserver://EC2AMAZ-JGN0VFT:1433 options {user=dbuser, password=***, applicationName=embulk-input-sqlserver, databaseNam
e=TESTDB, loginTimeout=300, socketTimeout=1800000}
2020-03-25 16:01:27.857 +0900 [INFO] (0014:task-0000): SQL: SELECT * FROM "dbo"."Employee" WHERE (("No" > ?) OR ("No" = ? AND "RegDate" > ?)) ORDER BY "No", "RegDate"
2020-03-25 16:01:27.857 +0900 [INFO] (0014:task-0000): Parameters: [30, 30, "2020-03-25 15:34:42.4166667"]
2020-03-25 16:01:28.029 +0900 [INFO] (0014:task-0000): > 0.01 seconds
2020-03-25 16:01:28.372 +0900 [INFO] (0014:task-0000): Loading 10 rows
2020-03-25 16:01:28.576 +0900 [INFO] (0014:task-0000): > 0.20 seconds (loaded 10 rows in total)
2020-03-25 16:01:28.622 +0900 [INFO] (0001:transaction): {done: 1 / 1, running: 0}
2020-03-25 16:01:28.705 +0900 [INFO] (main): Committed.
2020-03-25 16:01:28.705 +0900 [INFO] (main): Next config diff: {"in":{"last_record":[40,"2020-03-25 15:58:58.0933333"]},"out":{}}
Select文実行箇所の通信確認
今回確認したいSelect文が実行しているコンソールログは以下です。
2020-03-25 16:01:27.857 +0900 [INFO] (0014:task-0000): SQL: SELECT * FROM "dbo"."Employee" WHERE (("No" > ?) OR ("No" = ? AND "RegDate" > ?)) ORDER BY "No", "RegDate"
これをWiresharkで確認すると以下のように出力されていました。
まず上部の赤枠から説明すると、「Remote Procedure Call」というのはリモート先のプロシージャを呼び出していることを表しており、ここで取得元のIPに対して何かしらの処理が実行されていることがわかります。
※「Remote Procedure Call」の参考リンク
RPC - The Wireshark Wiki
指定のパケットを選択すると、下画面にそのパケットの詳細が出力されます。
そのうち「Tabular Data Stream」の中に送信するメッセージについての詳細が保持されています。
※「Tabular Data Stream」の参考リンク
Protocols/tds - The Wireshark Wiki
「Tabular Data Stream」内の「Remote Procedure Call」を展開してみると、Parameterがいくつか存在しているのがわかります。
この中のValueを確認すると、取得元SQL Serverに送信されるSQLが設定されていました。
パラメータの設定順としては、SQL本文→SQL本文内の変数の定義→各変数に当てはまる値、という順に設定されているようですね。(@P0→@P1→@P2の順ですね)
以上のことから、EmbulkのSQL Serverプラグインでは、特に通信自体の暗号化は実施されていないことがわかりました。
(暗号化というか完全にSQLをリモートのDBに飛ばしているだけっぽい)
感想及び所感
Wiresharkをつかってパケットを見るのはほとんどやったことない＆ネットワークは概要レベルしか知識持っていない、ということもあり、1つ1つ見ていくのが思ったよりも大変でした。
ただ、やっていて思ったのが、そもそも外部にDBへの通信が漏れるような構成となっているなら、暗号化うんぬんよりもそっちをどうにかしたほうがいいんじゃないか、と気づきました。
どうしてもプライベートなネットワークにDBに置けない時などは、以下のように別途通信の暗号化を行うための設定を入れたほうが良いですね。
docs.microsoft.com
Wiresharkについては、もっと時間をかけてみていけたら理解もより進むような気がしましたが、他にもやりたいことがあるので今回は最低限の確認にしました。
色々調べている中で、wiresharkでhttpsのパケットを平文で見る、というようなブログもあったので、そういうのをやってみたほうが面白さ的にも学びになりそう(それが良いかは別として)
