Excel VBAからMySQLへ安全に接続！SSHトンネルを自動起動するクラスモジュールを作ったのでメモ
前回、Excel VBAからMySQLへ直接接続する方法を紹介しましたが、実はあの構成には大きなセキュリティ上の懸念がありました。それは、データベースのポート（3306番）を全世界に向けて開放しなければならないという点です。
固定IPを持たない一般的なネット環境から接続する場合、パケットフィルタを全開放にする必要がありますが、これは悪意ある攻撃者に常に門を開放している状態と言えます。
そこで今回は、SSHトンネルを利用した解決策を紹介します。サーバーの不要なポートはすべて閉じ、安全な暗号化通路（22番ポート）のみを使用してDBへアクセスする方法です。これをVBAのクラスモジュールとして実装し、マクロ実行時に「自動でトンネルを掘り、終われば自動で片付ける」仕組みを構築したので書き留めておきます。
秘密鍵を用いたSSHトンネル概要
SSHトンネルとは、手元のPCからサーバーまで「暗号化された土管」を通し、その中をデータベースの通信（3306ポート）が通るようにする仕組みです。
- 秘密鍵（.pem）の利用: パスワード認証ではなく、より強固な鍵認証を使用します。
- ポートフィルタの最小化: Xserver VPS側の設定で、3306番ポートは削除（拒否）し、22番（SSH）のみを許可します。
- 127.0.0.1への接続: SSHトンネルが確立されると、自分のPCの3306番がサーバーの3306番に直結されます。そのため、VBAからは Server=127.0.0.1 として接続が可能になります。
クラスモジュール：DBManagerクラス
このクラスの最大の特徴は、「オブジェクトの寿命（生成から消滅まで）」と「SSHトンネル・DB接続の寿命」を完全に一致させている点です。これにより、開発者は接続や切断のタイミングを細かく管理する必要がなくなります。
VBAエディタでクラスモジュールを挿入し、オブジェクト名を DBManager とした上で以下のコードを記述します。
''''' クラスモジュール: DBManager '''''
Option Explicit
' --- 接続パラメータの設定（ここを書き換えるだけで他環境でも使い回せます） ---
' SSH接続情報
Private Const SSH_KEY_PATH As String = "C:\Users\...\.ssh\satoshiu71.pem" ' 秘密鍵のフルパス
Private Const SSH_USER As String = "root" ' SSHユーザー（Xserver VPSなら通常root）
Private Const SSH_HOST As String = "satoshi71.com" ' サーバーのドメインまたはIP
' MySQL接続情報
Private Const DB_HOST As String = "127.0.0.1" ' SSHトンネル経由のためローカルを指定
Private Const DB_USER As String = "user_name" ' MySQLのユーザー名
Private Const DB_PASS As String = "********" ' MySQLのパスワード
Private Const DB_NAME As String = "your_database" ' 接続先データベース名
' インストールされているドライバー名を正確に指定（ANSI版かUnicode版か、バージョンに注意）
Private Const DB_DRIVER As String = "MySQL ODBC 9.6 ANSI Driver"
' 公開プロパティ：標準モジュールからこのConnectionを通じてSQLを実行します
Public Connection As Object ' ADODB.Connection
' --- クラス初期化：New DBManager した瞬間に実行 ---
Private Sub Class_Initialize()
Dim sshCmd As String
' 1. SSHトンネルコマンドの構築
' -L [ローカルポート]:[リモートホスト]:[リモートポート] の書式でトンネルを定義
' -o StrictHostKeyChecking=no：初回接続時の確認をスキップし、マクロ停止を防ぐ
' -N：リモートコマンドを実行しない（トンネル専用）
' -f：バックグラウンド実行（実行後に制御をVBAに戻す）
sshCmd = "ssh -i """ & SSH_KEY_PATH & """ " & _
"-L 3306:127.0.0.1:3306 " & SSH_USER & "@" & SSH_HOST & " " & _
"-o StrictHostKeyChecking=no " & _
"-o ExitOnForwardFailure=yes " & _
"-N -f"
' トンネル起動（vbHideで黒い画面を出さない）
Shell sshCmd, vbHide
' 2. トンネルが確立されるまで待機
' ネットワーク環境に合わせて2〜5秒程度に調整
Application.Wait [Now() + "00:00:02"]
' 3. ADOを使用してMySQLに接続
Set Connection = CreateObject("ADODB.Connection")
Dim connStr As String
connStr = "Driver={" & DB_DRIVER & "};" & _
"Server=" & DB_HOST & ";" & _
"Port=3306;" & _
"Database=" & DB_NAME & ";" & _
"User=" & DB_USER & ";" & _
"Password=" & DB_PASS & ";" & _
"Option=3;"
On Error Resume Next
Connection.Open connStr
If Connection.State = 0 Then
MsgBox "データベース接続に失敗しました。ドライバ名やパスワードを確認してください。", vbCritical
End If
On Error GoTo 0
End Sub
' --- クラス終了時：Set db = Nothing または プログラム終了時に実行 ---
Private Sub Class_Terminate()
' 1. DB接続を安全に閉じる
If Not Connection Is Nothing Then
If Connection.State <> 0 Then Connection.Close
Set Connection = Nothing
End If
' 2. SSHトンネルプロセスを終了
' プロセス名を指定して強制終了(taskkill)することで、裏側にssh.exeを残さない
On Error Resume Next
Shell "taskkill /F /IM ssh.exe /T", vbHide
On Error GoTo 0
End Sub
簡単な利用方法
実装したクラスを使えば、メインのプログラムは驚くほどスッキリします。
Sub SampleUsage()
Dim db As New DBManager ' これだけでSSHトンネルが掘られ、DB接続まで完了
' データの取得
Dim rs As Object
Set rs = db.Connection.Execute("SELECT * FROM products")
' ... 処理 ...
Set db = Nothing ' オブジェクトを破棄すれば、SSHトンネルも自動で終了
End Sub
このように、呼び出し側は「SSHの存在」を一切意識することなく、あたかもローカルのデータベースを触っているかのような感覚で安全にリモートDBを操作できるようになります。
スポンサーリンク