Powershellでやる、ショートカット (.lnk)を使ってMS Accessから踏み台サーバ経由でDBにODBC接続

15791 ワード
ODBC access OpenSSH PowerShell OpenSSH テキストリンク

目的、背景など

以前投稿した記事でも触れましたが、現職場ではAccessをメインにデータの集計を行っています。
Accessから踏み台サーバ経由でODBC接続してデータを取り扱うのですが、これがとにかく面倒くさい。
まず事前に管理ツールのODBCデータソースでODBC接続設定を行い、PuTTYでSSH接続し、ポートフォワーディングにて踏み台サーバに接続して、DBを参照する。これだけの作業を行うのにODBC接続・SSH接続の各設定やパスワードやらssh keyやらを管理しなくてはならないのが実にストレスです。個人的に、ODBCデータソースによる接続設定はローカルに設定情報が残る為、神経質な私は落ち着きません。
そこで、ある時からWindows10に標準で搭載されるようになったOpenSSHをうまく活用できないかと、思い立った次第です。

この記事は、
Windows10 64bitで、
ショートカット(.lnk)から、
Powershellスクリプト(.ps1)を実行し、
OpenSSHで踏み台サーバ経由でDBサーバにssh接続を行い、
ODBC接続を行い、
MS Accessを起動し、
MS Accessが終了したら、
諸々の切断処理を行う、
というとてもピンポイントな記事になります。

作業環境

・データベースエンジン：Aurora PostgreSQL（エンジンバージョン12.7）
・Microsoft Access2003、Microsoft Access2016
・OS：Windows10 64bit
・Powershellバージョン：PSVersion 5.1.19041.1320
・ODBCドライバー（※1）：PostgreSQL Unicode （バージョン13.02.00.00）

※1　Microsoft Access2003も使っているので、32bit・64bitの両方を使っています。

参考記事

sshオプションなど
http://www.itsenka.com/contents/development/unix-linux/ssh.html
OpenSSH for Windows の使用方法
https://qiita.com/akiakishitai/items/9e661a126b9c6ae24a56
コマンドを使用したODBC接続
https://software.fujitsu.com/jp/manual/manualfiles/m180006/j2ul2351/01z200/j2351-b-02-02.html
Powershellの実行ポリシーなど
https://qiita.com/tomoko523/items/df8e384d32a377381ef9

Powershellスクリプト(.ps1)の作成

とりあえず作成した.ps1が以下のものになります。私は鍵などを共有サーバに保管してあり、その際のパスの指定に日本語を挟むため、冒頭でUTF-8指定を行っています。使用するAccessのバージョンや.accdbファイルもここで指定します。
OpenSSHは標準としてインストールはされているものの、それに関連するサービスは停止された状態になっています。したがって、必要なプロセスやサービスの起動・終了も.ps1内に含めております。
ODBC接続設定ですが、必要なオプションを$Str～で必要な分だけ宣言します。また、Access2003も通常利用しているので、32ビット版の設定も記載しています。

Access_ssh_odbc.ps1
### UTF-8
chcp 65001

### Access設定値
$ACCESS2016  = "C:\Program Files\Microsoft Office\root\Office16\MSACCESS.EXE"
$ACCESSFile  = "C:\test\test.accdb"
### ssh設定値
New-Item "${HOME}\.ssh" -type directory -Force # c:\user\ユーザー名\.sshがない場合があるっぽいため、この一文があればなければ作成されます
$KeyPath     = "\\共有サーバー\EC2キーペア\hoge.pem" # 秘密鍵
$TmpKeyPath  = $HOME + "\.ssh\tmp_id_rsa"  # 秘密鍵コピー先
$SV01        = "12.34.56.78"   # 踏み台サーバ
$DB01        = "23.45.67.89"   # 接続先DBサーバ
$DB01port    = "9999"          # 接続先DBサーバポート番号
$localport   = "99999"         # 割り当てポート番号
$sshUser     = "ec2-user@${SV01}"
$sshOption01 = "-o StrictHostKeyChecking=no"  # 確認メッセージ省略
$sshOption02 = "-o ServerAliveInterval=60"    # タイムアウト防止
$sshOption03 = "-f -N"                        # -f バックグラウンドで実行 -N リモートコマンドを無効
$sshString   = "${sshUser} -p 22 -i ${TmpKeyPath} -L ${localport}:${DB01}:${DB01port} ${sshOption01} ${sshOption02} ${sshOption03}"
### ODBC設定値
$OdbcDsn     = "hoge_db"
$Driver32    = "PostgreSQL Unicode"
$Driver64    = "PostgreSQL Unicode(x64)"
$Str01       = "Servername=localhost"
$Str02       = "Port=99999"     # 上記の$localportとポート番号
$Str03       = "Database=hoge_db"
$Str04       = "SSLMode=Disable"
$Str05       = "CommLog=0"
$Str06       = "Debug=0"
$Str07       = "Username=hoge_name"
$Str08       = "Password=hoge_pass"

### ssh接続
Set-Service -Name ssh-agent -StartupType Manual
Start-Service ssh-agent
ssh-keygen -R $SV01 # 指定したホストに属する鍵を全て取り除く
Copy-Item -Path $KeyPath -Destination $TmpKeyPath
ssh-add $TmpKeyPath
Start-Process ssh -ArgumentList $sshString

### ODBC接続　32ビット
Add-OdbcDsn $OdbcDsn -DriverName $Driver32 -DsnType System -Platform 32-bit -SetPropertyValue @($Str01,$Str02,$Str03,$Str04,$Str05,$Str06,$Str07,$Str08)
### ODBC接続　64ビット
Add-OdbcDsn $OdbcDsn -DriverName $Driver64 -DsnType System -Platform 64-bit -SetPropertyValue @($Str01,$Str02,$Str03,$Str04,$Str05,$Str06,$Str07,$Str08)

### Access起動
Start-Process -FilePath $ACCESS2016 -ArgumentList $ACCESSFile -Wait # -Wait プロセス終了まで待機

### ssh切断、ODBC切断、終了処理
ssh-add -D
Get-Process ssh | Stop-Process
Get-Service ssh-agent | Stop-Service
Get-OdbcDsn $OdbcDsn | Remove-OdbcDsn
Remove-Item $TmpKeyPath

exit

ショートカット(.lnk)の作成

リンク先

リンク先はexe指定にて行います。.ps1の実行のオプションを以下のオプションをつけて記述します。
-windowstyle hidden　Powershellのウィンドウを非表示にする
-ExecutionPolicy RemoteSigned　実行ポリシーの変更(こちらを参考まで)
最終的に、以下のワンライナーをリンク先として指定します。

C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -windowstyle hidden -ExecutionPolicy RemoteSigned -File Access_ssh_odbc.ps1

詳細設定

さて、起動用の.ps1ですが、この.ps1はサービスの起動・終了や諸々の情報の操作を行うため、管理者権限で実行する必要があります。
このままだとショートカットを作っても、直に.ps1を実行するにしても右クリック＞管理者として実行の操作が必要になります。
このワンアクションをちょっとだけ省略するために、作成するショートカットにひと手間加えます。
作成した.lnkのプロパティを開き、ショートカットタブの詳細設定を開きます。ここの「管理者として実行」にチェックを入れておけば、右クリック＞管理者として実行の操作が不要になります。（ユーザーアカウント制御のはい/いいえの操作は必要です）
さらにAccess自体を管理者として実行したい場合は、Shiftキーを押しっぱなしの状態でショートカットを起動すれば、Accessも管理者として実行された状態で起動します。

こんなオプションあったんですね。

末筆

記事としては以上になります。
上記スクリプトを作るまでは、以下のようなワンライナーをショートカットのリンク先として登録して、PuTTYを使って接続していました。（これはこれで非常に助かってました）

C:\Windows\System32\cmd.exe /C pushd \\共有サーバー\ & ".\64bit\PuTTY-ranvis\putty.exe" [email protected] -P 22 -i .\EC2キーペア\hoge.ppk -L 99999:23.45.67.89:9999 & popd & exit

Windows10になるとコンパネから管理ツールへのアクセスがうざくて、ODBCの設定の見直しも32ビット・64ビットで神経使うんですよね。
同様の環境の方がいらっしゃって、同じようなわだかまりをお持ちであれば、
あるいはpowershellでのssh接続やODBC接続の参考の一つとして、他の方の助けになればと思います。