オンプレOracle DB にSSHサーバ経由でGoogleAppsScript からSQLでアクセスしてみた：SQLGateway

こんにちは、CData の宮本です。

今回は オンプレにあるOracle を CData の製品である SQLGateway を通してGoogleAppScript からデータ抽出や更新を行う方法をご紹介します。

SQLGateway とは？

CData ODBC Driver からアクセスできるデータソースであれば、SQLGateway を通すことにより MySQL や SQLServer のI/F として扱うことができる画期的な製品となります。例えば、あるツールではデータソースに MySQL しか選べないというような場合であっても、SQLGateway によって CData が対応しているさまざまなデータソースを MySQL という扱いでアクセスすることができます。
https://www.cdata.com/jp/sqlgateway/

全てがイントラネット内などであれば特に必要はないのですが、やはり外部からオンプレにある DB にアクセスしたいという要望は多いです。
そこで、SQLGateway には SSH サーバーを介して接続をトンネリングすることができます。

SSH サーバ経由でのオンプレDBへのアクセス

SQL Gateway はクラウド上のSSHサーバーのリバースSSHポートフォワーディングを利用し、オンプレにあるDBをインターネット上に公開する機能です。

SQLGateway が SSH Server と接続を確立した状態で、SSH Server がHTTP/Sリクエストをポートフォワーディングすることにより、SQLGateway をインターネットアクセス可能にします。

これにより、ファイアウォールの変更、DMZへの配置、固定IPアドレス・ドメインといった要素をGCPなどのクラウドサービス側に寄せることができ、セキュアにオンプレミスに存在するRDBや基幹システムをクラウド上で扱えます。

今回はこの機能を Google Compute Engine の CentOS で構成する方法を紹介したいと思います。なお、SSHサーバーはお好みのマシンで構いません。

必要なもの

• CData Oracle ODBC Driver （SQLGateway も一緒にインストールされます）
• Oracle （GASからアクセスするDBとして）
• GCE（SSHサーバーとして使用）
• Windows マシン（Oracle ODBC Driver と、SQLGateway を配置するオンプレミス環境です）

SSHサーバーとなるGCE インスタンスを構成する

それでは GCE インスタンスを作成します。OSはCentOSを今回は選択しています。ファイアウォールの設定では、HTTPとHTTPS にチェックを入れてください。

IPアドレスは固定になるようここで登録しています。

以上の設定をした後、GCE を起動してSSHで接続します。
今回は SSH での接続を、サーバへのファイルアップロードもあることから「ブラウザウィンドウで開く」から行っていきたいと思います。

「sshd_config」に「GatewayPorts yes」を追加してください。デフォルトではコメントアウト状態で「#GatewayPorts no」になっています。

sudo vi /etc/ssh/sshd_config

次に、SQLGateway から GCE に接続できるように pem 形式の秘密鍵を生成します。まずは、ssh-keygen の以下のコマンドで認証鍵を生成します。

ssh-keygen -t rsa -f ~/.ssh/認証鍵ファイル名 -C ユーザ名

コマンドを実行すると、ユーザーのホームディレクトリ/.ssh に以下の認証鍵ができあがります。

[miyamotok@sqlgateway-sshsrv ~]$ ll .ssh/
total 12
-rw-------. 1 miyamotok miyamotok  409 Jun 11 02:27 authorized_keys
-rw-------. 1 miyamotok miyamotok 1675 Jun 11 07:14 sqlgateway-ssh-key
-rw-r--r--. 1 miyamotok miyamotok  391 Jun 11 07:14 sqlgateway-ssh-key.pub

今度はsqlgateway-ssh-key を使って、SQLGateway で使用するpem 形式の秘密鍵を生成します。

openssl rsa -in ~/.ssh/sqlgateway-ssh-key -outform pem > sqlgateway-ssh-key.pem

コマンド実行後、pem形式の秘密鍵が追加されます。

あとは出来上がったpem ファイルをダウンロードします。ダウンロードは先ほどご紹介した「ブラウザウィンドウで開く」をお使いであれば、以下のように簡単にファイルをダウンロードできます。

一旦、GCE 側の設定は以上となります。

オンプレミス側 CData Oracle ODBC Driver のセットアップ

続いて、オンプレミス側にあるOracle へアクセスする CData ODBC Driver をセットアップしていきます。

https://www.cdata.com/jp/drivers/oracledb/odbc/

ダウンロードボタンを押して Oracle ODBC Driver のインストーラーをダウンロードします。ダウンロード後、中にある setup.exe を実行しますと以下の画面が表示されますので、次へ次へと最後まで進んで頂いて構いません。
※有効期限が切れているというメッセージが表示されてインストール画面を開けない場合は、以下のリンクからインストーラーをダウンロードして実行してください。

https://cdatajwrk.s3-ap-northeast-1.amazonaws.com/OracleODBCConnector/SODE-A.zip

インストールが完了すると、ODBC DSN の設定画面が表示されますので、Oracleへの接続情報をここで設定します。なお、必須を表示タブをクリックすることで、以下のように必須入力項目のみが表示されます。

また、他のスキーマのテーブルなどが表示されないよう、「すべて表示」タブから Browsable Schemas に対象のスキーマ名を入力します。

設定項目への入力が完了しましたら、「接続のテスト」から実際に接続を行い、接続できれば OK ボタンでDSNの設定を保存します。

CData Oracle Driver を使用する際の必須作業

ODBC や JDBC に限らず、CData の Oracle Driver を使用する場合は、以下の作業が必須となります。（Oracle以外で構成される場合はこの作業は必要ありません）

1. Oracle Instant Clientをインストール

2. CData Oracle DLLをOracle Instant Client のホームディレクトリに配置

3. 環境変数に Oracle Instant Clientのホームディレクトリをセット

4. Microsoft Visual Studio 2013ランタイムライブラリをインストール

詳細な手順は以下記事の「(Java版Sync)DLL参照エラーが表示される場合」の章をご参照ください。

www.cdatablog.jp

これで CData Oracle ODBC Driver から Oracle への接続設定が完了しました。

オンプレミス側 SQLGateway のセットアップ

SQLGateway － Oracle 間の設定

次に、SQLGateway の設定を行っていきます。 インストール自体は、ODBC Driver のインストール時にデフォルトでインストールされておりますので、メニューにある CData SQL Gateway をクリックして実行します。

サービスタブから右側にある追加ボタンをクリックする。

サービス名を任意の名前で入力後、今回は MySQL I/F として公開するようにしますので MySQL を選択し、SSHサーバとのSSH接続を行うために TLS/SSL をチェックします。
データソースは、先ほど作成したOracle への接続設定を行った ODBC DSN を指定します。DSN がリストとして表示されますので、該当するDSN を選択してください。
ポートについては、SQLGateway が起動しているマシン内で空いているポートを指定します。最後に Expose through SSH Tunnel をチェックして画面下部のOK ボタンをクリックします。

以上で SQLGateway から Oracle へのアクセス設定が完了です。 続いて、SQLGateway から SSHサーバへの接続設定を行っていきます。

SQLGateway － SSHサーバ(GCE) 間の設定
SSH Tunnel タブをクリックし、以下の項目を入力し終わったら、右側にある「...」ボタンをクリックします。

• ホスト → GCE のIPアドレス
• ポート → 22
• 認証モード → PublicKey
• ユーザー → GCE で認証鍵を作成したときのユーザー

先ほどダウンロードした PEM ファイルを設定後、Open ボタン → Export Public の順でボタンをクリックします。

Copy to File ボタンをクリックし、公開鍵を生成します。

生成した公開鍵を GCE にアップロードします。アップロードはダウンロードしたときと同じように、「ブラウザウィンドウで開く」で繋いでいれば簡単にアップロードすることができます。

アップロードした公開鍵をauthorized_keys がある ~/.ssh/ で以下のコマンドにて GCE に追加登録します。

cat sqlgateway-sshsrv.pub >> authorized_keys

公開鍵をサーバーに登録したら再度 SQLGateway に戻り、テスト接続ボタンをクリックします。
GCE への接続が確立されたならば、変更を保存ボタンをクリックし、これまでの設定を保存します。

続いて、SQLGateway 上で接続できるユーザ名を定義することができますので、接続ユーザーを作成します。
ユーザータブから追加ボタンをクリックします。

今回は「sqlgateway-user」というユーザーを作成しました。また、このユーザーは管理者指定はせず、他のデータソースへは接続できないように設定しています。設定が終わったらOK ボタンをクリックします。

これでOracle にのみアクセスできるユーザーを作成できました。

色々設定してきましたが、SQLGateway 側の細かい設定はこれが最後となります。
SSH Tunnel タブより SSH接続の動作を設定します。一定時間が過ぎても自動的に再接続するよう、以下の赤枠部分にチェックを入れ保存します。

SQLGateway 側の設定は以上で完了となりますので、Oracleを選択してから右側の開始ボタンをクリックします。少し待つと、左側に緑色のランプがつきますのでこうなると正常に起動（公開）していることになります。
外部からアクセスする場合は、リモートポートという列に表示されている IPアドレス＋ポートで接続します。

SSHサーバー（GCE）で SQLGateway のポートを開放

これで最後の設定となります。
SQLGateway では3311 というポートを設定しました。これは外部からアクセスした場合に、SSH サーバーのGCE を通ってアクセスするようになるため、GCE で3311 ポートが使えるようにする必要があります。

ではさっそくポートの開放をしていきます。

GCP にメニューから VPCネットワーク → ファイアウォール の順にクリックします。

ファイアウォール ルールを作成をクリックします。

このファイアウォールの名前とターゲットタグを任意の名前で設定し、送信元のIP アドレスは今回は全て対象にするために 0.0.0.0/0 を設定しています。
次に、「指定したプロトコルとポート」とtcp を選択し、 SQLGateway で指定したポート番号をここに入力したら、作成ボタンをクリックします。

ファイアウォールが出来上がったら、そのファイアウォールの作成で設定したタグ名を GCE に設定します。

次に GCE にSSH でログインし、以下のコマンドと設定ファイルの更新で SELinux と iptables を無効化します。

sudo systemctl disable firewalld
sudo vi /etc/selinux/config

長くなりましたがこれで設定が完了いたしました。このタイミングで一度GCE を再起動してください。

もし接続を確認したい場合などは、フリーのDBツールなどから先ほどの IPアドレス＋ポート で接続することができますので、適宜確認してみてください。
以下は フリーツールのA5MK2 から接続した際のキャプチャになります。

接続に失敗する場合

接続に失敗する場合は以下の対応で解消できるか確認してみてください。

SQLGateway の再接続
SQLGateway で再度 SSHサーバ（GCE）にテスト接続を行い、SQLGatewayのOracle サービスを再起動してみる。

対象のサービスを選択して、起動中であれば停止 → 起動を行う。

GCE 上で対象ポートが開放しているか確認
以下のコマンドを実行し、対象のポート（今回は3311）が表示されているかどうか確認

sudo netstat -ltunp4

[miyamotok@sqlgateway-sshsrv ~]$ sudo netstat -ltunp4
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN      904/master
tcp        0      0 0.0.0.0:3311            0.0.0.0:*               LISTEN      1092/sshd: miyamoto
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      874/sshd
udp        0      0 0.0.0.0:68              0.0.0.0:*                           510/dhclient
udp        0      0 127.0.0.1:323           0.0.0.0:*                           426/chronyd
[miyamotok@sqlgateway-sshsrv ~]$

表示されてない場合は、SQLGatewayで使用するポートが空いていないことになるので、再度GCP のファイアウォールか、SELinux の無効化などを行い確認してみる。

Google Apps Script からOracle にアクセスしてみる

やっと、本題の Google Apps Script から SQLGateway 経由で オンプレの Oracle にアクセスするところに入ります。

まずは、適当にスプレッドシートを開いたら、スクリプトエディタをクリックして開きます。

Oracle のデータをGASから取得

オンプレにあるOracle のDEALという案件データが入ったテーブルに対して、SQL を実行して取得します。
今回は以下のコードを実行します。

//SQLGatewayのリモートポートにあるIP＋ポートを指定
var connectionName = 'XX.XX.XX.XX:XXXX/';
//SQLGatewayで作成したユーザー
var user = 'sqlgateway-user';
//SQLGatewayで作成したユーザーのパスワード
var userPwd = 'password';
//接続先DB名（SQLGatewayのデータソース名）
var db = 'OracleOciSys';

var instanceUrl = 'jdbc:mysql://' + connectionName + db;


function selectOracleData(){
  var conn = Jdbc.getConnection(instanceUrl, user, userPwd);
  var stmt = conn.createStatement();
  
  var results = stmt.executeQuery('select RECORDID AS Id, 部署名,受注確度,ご担当者名,案件名,受注金額,顧客名 from OracleOciSys.DEAL\
                                  where 案件名 IS NOT NULL and 顧客名 IS NOT NULL order by RECORDID desc;');
  
  var numCols = results.getMetaData();

  const sheet = SpreadsheetApp.getActiveSheet(); 
  const lastRow = sheet.getLastRow();

  let i = 1;
  while (results.next()) {
      
    var clmString = '';
    for (var col = 0; col < numCols.getColumnCount(); col++) {
      if (col==0){
        for(var j=1; j<=numCols.getColumnCount(); j++) {
          sheet.getRange(1, j).setValue(numCols.getColumnName(j))
        }
      }

      clmString = results.getString(col + 1);
      Logger.log(clmString);
      sheet.getRange(i+1, col+1).setValue(clmString);
    }
    i++;
  }

  results.close();
  stmt.close();  
}

上記コードを実行しますと、初回は以下のようなポップアップが表示されるかもしれませんが、その場合は「許可を確認」ボタンをクリックします。

GAS から SpreadSheet へのアクセス許可を行います。

以下のように実行結果（Oracleのデータ）が SpreadSheet に書き込まれます。

Oracle のデータをGASから登録

では続いてデータの登録、更新、削除を行います。
まずは データの登録を行います。登録するデータは今回はベタでGASにハードコードして実行します。

function insertToOracle(){
  try{
    var conn = Jdbc.getConnection(instanceUrl, user, userPwd);
    var stmt = conn.createStatement();
    var stmt = conn.prepareStatement('insert into OracleOciSys.DEAL (RECORDID, 部署名, ご担当者名, 案件名, 顧客名) values(?, ?, ?, ?, ?);');

    stmt.setString(1, 100);
    stmt.setString(2, '調達部');
    stmt.setString(3, '佐藤太郎');
    stmt.setString(4, 'CData SQLGateway案件');
    stmt.setString(5, '/n Software.Inc');
    stmt.execute();  
  }
  catch(e){
    Logger.log(e); 
  }
  finally{
      stmt.close();  
  }
}

データ登録が完了しましたら、GAS で再度 Select を行い、追加されたことを確認してみます。

先頭に追加データが表示されました。

Oracle のデータをGASから更新

続けて、先ほど追加したデータの受注金額が null になっているので、金額を登録します。受注金額のエリアに130000 円と入力してから、今度は Update文でデータの更新を行います。

function updateToOracle(){
  try{
    var conn = Jdbc.getConnection(instanceUrl, user, userPwd);
    var stmt = conn.createStatement();
    
    var stmt = conn.prepareStatement('UPDATE OracleOciSys.DEAL SET 受注金額=? WHERE RECORDID=?;');
    
    //現在のシートを取得
    var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = spreadsheet.getActiveSheet();
    //値を取得
    var range1 = sheet.getRange("A2");
    var range2 = sheet.getRange("F2");
    var id = range1.getValue();
    var amount = range2.getValue();
    
    stmt.setString(1, Number(amount));
    stmt.setString(2, Number(id));
    stmt.executeUpdate();
  }
  catch(e){
    Logger.log(e); 
  }
  finally{
    stmt.close();  
  }
}

ここで、再度データ取得のコードを実行してみますと、受注金額に入れたデータが消えずに表示されていることが確認できます。

Oracle のデータをGASから削除

では以下のコードを実行します。
※Id=100 で追加したレコードを直接指定しています。

function deleteToOracle(){
  try{
    var conn = Jdbc.getConnection(instanceUrl, user, userPwd);
    var stmt = conn.createStatement();    
    var stmt = conn.prepareStatement('DELETE FROM OracleOciSysRDS.DEAL WHERE RECORDID=?;');   
    stmt.setString(1, 100);
    stmt.executeUpdate();
  }
  catch(e){
    Logger.log(e); 
  }
  finally{
    stmt.close();  
  }
}

再度データ取得のコードを実行すると、先頭行から先ほど追加したレコードが消えているのが確認できました。

おわりに

いかがでしたでしょうか。一度設定してしまえば、ODBC Driverを追加し、SQLGateway で参照するように設定すれば、他のデータソース、データベースもすぐにGoogle Apps Script からアクセスできるようになります。
今回は Oracle でしたが、例えばオンプレにある SQLServer なども同じ手法で設定できます。
CData で提供している ODBC Driver は以下のリンクよりご確認ください。

https://www.cdata.com/jp/odbc/

全製品 30日間無料で使用できますので、是非ダウンロードいただき、SQLGateway のSSH公開をお試しください！

ちなみに、オンプレデータベースからREST API を自動で生成してくれる CData APIServer という製品があり、この製品を使用してGoogle Apps Script から API でオンプレDBとのデータ取得、更新、削除を行う方法もご紹介しておりますので、よろしければこちらもどうぞ。

www.cdatablog.jp

SQLGatewayの関連記事 www.cdatablog.jp