DataBase

【EXCEL】VBAでSSHポート転送を使用しクラウドサーバーのデータベースにアクセス

 

クラウドサーバーのデータベースにアクセスする際、セキュリティを確保するためにSSH（Secure Shell）ポート転送を使用することが一般的です。この記事では、VBA（Visual Basic for Applications）を使用してSSHポート転送を設定し、クラウドサーバーのデータベースにアクセスする方法を説明します。

クラウドサーバーには、レンタルサーバー（XSERVERなど）なども含みます。

必要なツールとライブラリ

まず、SSHポート転送とデータベース接続のために必要なツールとライブラリを紹介します。

  1. PuTTY（特にplinkツール）
  2. ADODBライブラリ（VBAでデータベースに接続するため）

PuTTYはSSH接続のためのフリーソフトウェアで、特にplinkはコマンドラインからSSH接続を確立するためのツールです。ADODBライブラリは、VBAを使用してデータベース接続を行うための標準ライブラリです。

手順

以下の手順で、VBAからSSHポート転送を使用してクラウドサーバーのデータベースにアクセスします。

1. PuTTYのインストール

まず、PuTTYをインストールし、plink.exeのパスを確認します。PuTTYの公式サイトからダウンロードできます。

2. SSHトンネルの設定

plinkコマンドを使用してSSHトンネルを作成します。以下のVBAコードは、プライベートキーを使用してSSHトンネルを確立し、そのトンネルを通じてローカルホストのポートをリモートのデータベースサーバーに転送します。

Sub SSHPortForwardingAndDBAccess()
    Dim plinkPath As String
    Dim privateKeyPath As String
    Dim sshUser As String
    Dim sshHost As String
    Dim localPort As String
    Dim remoteHost As String
    Dim remotePort As String
    Dim plinkCommand As String
    
    ' PuTTY plinkのパス
    plinkPath = "C:\path\to\plink.exe"
    
    ' SSH接続情報
    privateKeyPath = "C:\path\to\private_key.ppk"
    sshUser = "your_ssh_username"
    sshHost = "your_ssh_host"
    
    ' ポート転送設定
    localPort = "3307"  ' ローカルポート
    remoteHost = "127.0.0.1"  ' リモートホスト（データベースサーバー）
    remotePort = "3306"  ' リモートポート（データベースサーバー）
    
    ' plinkコマンドの生成
    plinkCommand = plinkPath & " -i " & privateKeyPath & " -L " & localPort & ":" & remoteHost & ":" & remotePort & " " & sshUser & "@" & sshHost
    
    ' SSHトンネルを確立
    Shell plinkCommand, vbMinimizedFocus
    
    ' 一時的な待機時間を設定（SSHトンネルが確立されるまで）
    Application.Wait (Now + TimeValue("0:00:05"))
    
    ' データベース接続と操作
    Dim conn As Object
    Dim rs As Object
    Dim sql As String
    
    ' データベース接続の設定
    Set conn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    conn.Open "Driver={MySQL ODBC 8.0 Driver};Server=127.0.0.1;Port=" & localPort & ";Database=your_database_name;User=your_db_username;Password=your_db_password;Option=3;"
    
    ' SQLクエリの実行
    sql = "SELECT * FROM your_table_name"
    rs.Open sql, conn
    
    ' データの処理
    Do While Not rs.EOF
        Debug.Print rs.Fields("your_field_name").Value
        rs.MoveNext
    Loop
    
    ' リソースの解放
    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing
End Sub

コードの詳細な解説

  1. PuTTYのパスとSSH接続情報の設定
    • plinkPathにはplink.exeのパスを指定します。
    • privateKeyPathにはSSHのプライベートキーのパスを指定します。
    • sshUserとsshHostにはSSHのユーザー名とホストを設定します。
  2. ポート転送設定
    • localPortにはローカルで使用するポート番号を指定します。
    • remoteHostにはリモートのデータベースサーバーのホスト名（通常は127.0.0.1）を設定します。
    • remotePortにはリモートのデータベースサーバーのポート番号を指定します。
  3. SSHトンネルの確立
    • plinkCommand変数にplinkコマンドを構築し、Shell関数で実行します。これにより、ローカルポートとリモートポートがトンネルで接続されます。
    • トンネルが確立されるまでの待機時間を設定します（5秒）。
  4. データベース接続と操作
    • ADODB.Connectionを使用して、ローカルホストの指定ポートを介してデータベースに接続します。
    • SQLクエリを実行し、結果を処理します。
  5. リソースの解放
    • データベース接続とレコードセットをクローズし、リソースを解放します。

注意点

  1. plinkのパスとプライベートキーのパスを適切に設定してください。
  2. データベース接続情報を正確に入力してください。
  3. SSHトンネルが確立されるまでの待機時間を環境に応じて調整してください。

応用・実践例

VBAを使用したSSHポート転送を応用した高度なテクニックや応用例について説明します。これにより、クラウドサーバーのデータベースに安全かつ効率的にアクセスする方法をさらに広げることができます。

複数のデータベースへの同時接続

企業のデータ処理や分析業務において、複数のデータベースに同時に接続し、データを集約する必要がある場合があります。SSHポート転送を利用して複数のリモートデータベースに接続する方法を紹介します。

Sub ConnectMultipleDatabases()
    Dim plinkPath As String
    Dim privateKeyPath As String
    Dim sshUser As String
    Dim sshHost As String
    Dim localPort1 As String, localPort2 As String
    Dim remoteHost As String
    Dim remotePort1 As String, remotePort2 As String
    Dim plinkCommand1 As String, plinkCommand2 As String
    
    ' PuTTY plinkのパス
    plinkPath = "C:\path\to\plink.exe"
    
    ' SSH接続情報
    privateKeyPath = "C:\path\to\private_key.ppk"
    sshUser = "your_ssh_username"
    sshHost = "your_ssh_host"
    
    ' ポート転送設定
    localPort1 = "3307"  ' ローカルポート1
    remoteHost = "127.0.0.1"  ' リモートホスト（データベースサーバー）
    remotePort1 = "3306"  ' リモートポート1（データベースサーバー1）
    
    localPort2 = "3308"  ' ローカルポート2
    remotePort2 = "3307"  ' リモートポート2（データベースサーバー2）
    
    ' plinkコマンドの生成
    plinkCommand1 = plinkPath & " -i " & privateKeyPath & " -L " & localPort1 & ":" & remoteHost & ":" & remotePort1 & " " & sshUser & "@" & sshHost
    plinkCommand2 = plinkPath & " -i " & privateKeyPath & " -L " & localPort2 & ":" & remoteHost & ":" & remotePort2 & " " & sshUser & "@" & sshHost
    
    ' SSHトンネルを確立
    Shell plinkCommand1, vbMinimizedFocus
    Shell plinkCommand2, vbMinimizedFocus
    
    ' 一時的な待機時間を設定（SSHトンネルが確立されるまで）
    Application.Wait (Now + TimeValue("0:00:05"))
    
    ' データベース接続と操作
    Dim conn1 As Object, conn2 As Object
    Dim rs1 As Object, rs2 As Object
    Dim sql1 As String, sql2 As String
    
    ' データベース接続の設定
    Set conn1 = CreateObject("ADODB.Connection")
    Set conn2 = CreateObject("ADODB.Connection")
    
    conn1.Open "Driver={MySQL ODBC 8.0 Driver};Server=127.0.0.1;Port=" & localPort1 & ";Database=database1;User=db_user;Password=db_password;Option=3;"
    conn2.Open "Driver={MySQL ODBC 8.0 Driver};Server=127.0.0.1;Port=" & localPort2 & ";Database=database2;User=db_user;Password=db_password;Option=3;"
    
    ' SQLクエリの実行
    sql1 = "SELECT * FROM table1"
    sql2 = "SELECT * FROM table2"
    
    Set rs1 = CreateObject("ADODB.Recordset")
    Set rs2 = CreateObject("ADODB.Recordset")
    
    rs1.Open sql1, conn1
    rs2.Open sql2, conn2
    
    ' データの処理
    Do While Not rs1.EOF
        Debug.Print rs1.Fields("field_name").Value
        rs1.MoveNext
    Loop
    
    Do While Not rs2.EOF
        Debug.Print rs2.Fields("field_name").Value
        rs2.MoveNext
    Loop
    
    ' リソースの解放
    rs1.Close
    rs2.Close
    conn1.Close
    conn2.Close
    Set rs1 = Nothing
    Set rs2 = Nothing
    Set conn1 = Nothing
    Set conn2 = Nothing
End Sub

説明

上記のコードでは、plinkを2回使用して2つのSSHトンネルを確立し、それぞれのトンネルを介して異なるデータベースに接続しています。これにより、複数のデータベースからデータを同時に取得できます。

スケジュールされた自動データ収集

特定の時間に自動でデータを収集し、定期的に更新するためのスケジュールされたタスクを作成することができます。以下のコードは、Windowsタスクスケジューラを使用して、毎日指定した時刻にデータベースからデータを取得するVBAマクロを実行する例です。

手順

  1. VBAマクロの作成

以下のVBAコードを新しいモジュールに追加します。

Sub ScheduledDataCollection()
    ' SSHトンネルとデータベース接続のコード（前述のコードを再利用）
    Call SSHPortForwardingAndDBAccess
End Sub
  1. バッチファイルの作成

次に、VBAマクロを実行するためのバッチファイルを作成します。例えば、以下の内容をRunMacro.batとして保存します。

cd "C:\path\to\your\excel\file"
start excel.exe /r "YourExcelFile.xlsm" /e "ScheduledDataCollection"

このバッチファイルは、指定したExcelファイルを開き、ScheduledDataCollectionというマクロを実行します。

  1. タスクスケジューラの設定

Windowsのタスクスケジューラを開き、新しいタスクを作成します。以下の設定を行います。

  • トリガー: 毎日指定した時刻に設定
  • 操作: バッチファイルを実行するように設定

データの自動バックアップ

データベースのデータを定期的にローカルファイルにバックアップするための方法です。

Sub DatabaseBackup()
    Dim plinkPath As String
    Dim privateKeyPath As String
    Dim sshUser As String
    Dim sshHost As String
    Dim localPort As String
    Dim remoteHost As String
    Dim remotePort As String
    Dim plinkCommand As String
    
    ' PuTTY plinkのパス
    plinkPath = "C:\path\to\plink.exe"
    
    ' SSH接続情報
    privateKeyPath = "C:\path\to\private_key.ppk"
    sshUser = "your_ssh_username"
    sshHost = "your_ssh_host"
    
    ' ポート転送設定
    localPort = "3307"  ' ローカルポート
    remoteHost = "127.0.0.1"  ' リモートホスト（データベースサーバー）
    remotePort = "3306"  ' リモートポート（データベースサーバー）
    
    ' plinkコマンドの生成
    plinkCommand = plinkPath & " -i " & privateKeyPath & " -L " & localPort & ":" & remoteHost & ":" & remotePort & " " & sshUser & "@" & sshHost
    
    ' SSHトンネルを確立
    Shell plinkCommand, vbMinimizedFocus
    
    ' 一時的な待機時間を設定（SSHトンネルが確立されるまで）
    Application.Wait (Now + TimeValue("0:00:05"))
    
    ' データベース接続と操作
    Dim conn As Object
    Dim rs As Object
    Dim sql As String
    Dim fso As Object
    Dim textFile As Object
    
    ' データベース接続の設定
    Set conn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    conn.Open "Driver={MySQL ODBC 8.0 Driver};Server=127.0.0.1;Port=" & localPort & ";Database=your_database_name;User=your_db_username;Password=your_db_password;Option=3;"
    
    ' SQLクエリの実行
    sql = "SELECT * FROM your_table_name"
    rs.Open sql, conn
    
    ' ファイルシステムオブジェクトの作成
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set textFile = fso.CreateTextFile("C:\path\to\backup.txt", True)
    
    ' データの処理と書き込み
    Do While Not rs.EOF
        textFile.WriteLine rs.Fields("your_field_name").Value
        rs.MoveNext
    Loop
    
    ' リソースの解放
    textFile.Close
    rs.Close
    conn.Close
    Set textFile = Nothing
    Set rs = Nothing
    Set conn = Nothing
End Sub

説明

このコードは、指定したデータベースからデータを取得し、テキストファイルに書き込むことでバックアップを作成します。ファイルパスやフィールド名は適宜調整してください。

ここで紹介させていただいた応用例では、VBAを使用したSSHポート転送を利用して、複数のデータベースへの同時接続、スケジュールされた自動データ収集、データの自動バックアップの方法を紹介しました。これらのテクニックを組み合わせることで、より効率的でセキュアなデータ処理を実現できます。業務の要件に応じてこれらの技術を活用し、データ管理の精度と効率を向上させましょう。

まとめ

一昔前までは、自社にサーバーを置き、データーベースもローカルで運用することが当たり前でしたが、現在では、クラウドにデーターベースを設置することが一般的になっています。

VBAを使用してSSHポート転送を介してクラウドサーバーのデータベースにアクセスする方法を説明しました。PuTTYのplinkツールを使用してSSHトンネルを確立し、ADODBライブラリを使用してデータベースに接続する手順を示しました。これにより、セキュリティを確保しながらリモートのデータベースにアクセスできます。セキュアなデータベースアクセスを実現するために、ぜひこの方法を試してみてください。

DataBase EXCEL SQL VBA WEB
こちらもおすすめ！！