読者です 読者をやめる 読者になる 読者になる

プログラマーのメモ書き

伊勢在住のプログラマーが気になることを気ままにメモったブログです

オープンリゾルバ対策

ネットワーク周りでちょっと調べ物をしていた時、オープンリゾルバ というキーワードが出てきました。 気になったので、調べてみると外部からアクセス可能な(再帰)DNSサーバーのことで、これを踏み台にして DDoS 攻撃を行うことがあるとのことです。

オープンリゾルバ(Open Resolver)に対する注意喚起 - JPNIC

で、この記事を読むと、ブロードバンドルータもオープンリゾルバになっている可能性がある、とのことでしたので、今更ですが対策を取りました。

まずは現状把握ということで、オープンリゾルバ確認サイトというのがあるので、それで状況をチェックします。

オープンリゾルバ確認サイト

オープンリゾルバ確認サイト公開のお知らせ

何も対策してないけど、大丈夫そうでした。

とはいえ気になったので、一応、ルータ (マイクロリサーチ NetGenesis MR-GL1000) のサポートページを見ると、オープンリゾルバ対策するにはファイアウォールのフィルタ設定を追加しろ、とあります。

DNSオープンリゾルバ対策につきまして|株式会社マイクロリサーチ

http://www.mrl.co.jp/support/nwginfo/firewall/doc/dns.html

あれ?大丈夫なんじゃないの? 気になったので、他のオープンリゾルバ確認サイトでもチェックしましたが、大丈夫そうです。

どういうことだろうか?

いろいろとネットを見ると、(他の機種ですが)ルータの設定によって、オープンリゾルバになる場合があるという記述がありました。 ひょっとしたら、これに該当するのかも?と思いましたが、残念ながら、マイクロリサーチのWebサイトでは情報を見つけられませんでした。

埒が明かないので、最終手段としてメーカーさんのサポートに電話してみたところ、現在の設定内容だと、オープンリゾルバにはなっていないとのことでした(たぶん、設定内容によっていろいろと条件があるのでしょうから、中途半端に書いて混乱するのは避けたいので、詳しくは割愛しておきます)。なるほど、それで確認サイトだと大丈夫と出てたのか、一安心。確認サイトを信頼して良かったようです。

将来、設定内容を変更した時に、この内容を忘れてるかもしれないので、(現時点では不要ですが)フィルタだけ設定しておきました。

QNAP TS-251+ メモリ増設しました

現在使用中の QNAP TS-251+ ですが、メモリ 2GB のモデルでした。 ちょっといろいろありまして、dockerを使おうと思い、それに先立ちメモリを増設することにしました。

基本的には、マニュアルを見て増設すればいいだけですが、ちょっとわかりにくかったので、メモ書きまとめておきます。

マニュアルは、下記のページより該当機種を選択してダウンロードします。

www.qnap.com

2ベイ、TS-251+を選択すると、

f:id:junichim:20161220184548p:plain

のような画面が表示されるので、『技術文書』を選択します。

f:id:junichim:20161220184800p:plain

日本語のマニュアルをダウンロードして、表示します。 マニュアルには、機種別のメモリの仕様、増設方法が詳しく載っているので、それに従います。

f:id:junichim:20161220185042p:plain

ということで、DDR3Lのメモリを購入しておきます。 今回は、これを買いました。

f:id:junichim:20161221104951j:plain

https://www.amazon.co.jp/%E3%83%8E%E3%83%BC%E3%83%88PC%E7%94%A8%E3%83%A1%E3%83%A2%E3%83%AA-PC3L-12800-DDR3L-1600-1-5V%E4%B8%A1%E5%AF%BE%E5%BF%9C-W3N1600CM-4G/dp/B01L6OCIRG/ref=sr_1_2?s=computers&ie=UTF8&qid=1482285499&sr=1-2

4GB2枚で4000円台。安くなったもんですね。 ちなみに、TS-251+は仕様としてはメモリは最大8GBとなっているのですが、16GB(8GBx2)で認識したというネットの記事もチラホラ見かけました。でも、今回は勇気が無くてやめました。

さて、実際の作業です。 まずは、電源を抜いて、筐体裏面の2本のネジを外します。

f:id:junichim:20161221103016j:plain

次に、カバーをスライドさせて取るのですが、これが取れない。どうやっても、スライドできなかったので、もしやと思い、HDDを取り出そうとしたら、いとも簡単にスライドしました。最初から外しておくべきだったのかな?うまくいかないときは、試してみてください。

f:id:junichim:20161221101606j:plain

これが、スライドできたときの写真。電源ボタンがあるほうに基盤があり、HDDの入る側がカバーとなっており、これがスライドします。 スライドできたら、簡単にカバーが取り外せます。

カバーを取り外したら、ドライブベイを外します。まず、写真のように、ドライブベイを止めている4本のネジを外します。

f:id:junichim:20161221102051j:plain f:id:junichim:20161221102108j:plain

実はこれだけではドライブベイを外せません。この外し方がマニュアルと異なっているところです。 この筐体の場合、ドライブベイが、この写真のようにステーで背面とつながっています。

f:id:junichim:20161221103110j:plain

なので、背面中央のネジも外す必要があります。

f:id:junichim:20161221103048j:plain

横から見た写真でわかるように、ネジを外した後、コネクタを抜く必要があるので、少し力が必要です。

無事に外せるとこんな感じです。丸で囲ってあるところが、メモリスロットです。

f:id:junichim:20161221103519j:plain

入っていたのは、Transcend のメモリでした。 既に入っているメモリを抜いて、新しいメモリを指します。

あと、写真上部の2つ目のメモリスロットにメモリを指すのですが、こちらは、1枚目(最初からメモリが入っていたスロット)とは裏表反対にして入れないといけません(メモリの切り欠きでわかると思います)。 メモリスロットの上に、金具があるので、少し作業がしにくいですが、慎重に作業すればまあなんとかなりました。

f:id:junichim:20161221104015j:plain

無事に入るとこんな感じになります。2枚目のメモリスロットを拡大するとこんな感じです。

f:id:junichim:20161221104034j:plain

これで、作業は完了です。 あとは、逆の手順で元通り組み立てれば終わりです。一応、HDDは戻すときに、スロットを間違わないように注意しました。もし、間違ったらどうなるんだろうか?ひょっとしてRAIDの再構築が始まるのかな?恐ろしくて試せませんので、勇気のある方は(バックアップを取ってから)お試しください。

組み立て終わったら、電源を投入します。NASにログインしてステータスを表示すると、増設前が、

f:id:junichim:20161221105648j:plain

だったのが、増設後は、

f:id:junichim:20161221105657j:plain

となり、無事に8GB認識されました。 めでたしめでたし。

(参考) 増設時の手順で、マニュアルと違うところがありましたが、下記のサイトを参考にさせていただき、なんとか乗り切りました。

QNAP TS-251+にメモリを増設しよう – ブログ、くまさい。

Access -> SQL Server への移行 (2/2)

さて、前記事でバックエンド側のテーブルを SQL Server に移行しました。SQL Server Management Studio で接続してテーブルを見てみると、特に問題なく移行できているように思えます。

次は、フロントエンド側のAccessから、この SQL Server に接続して今までと同じ操作ができるように整備します。

リンクテーブルの作成

まず初めに、今までバックエンドAccessファイルにあるテーブルに対してリンクテーブルを張っていたのを、 SQL Server 上のテーブルに張るように変更します。

フロントエンド側のAccessファイルを開き、既存のリンクテーブルを削除します。 次に、 SQL Server へのリンクテーブルを作成するのですが、Access の機能(メニューの『外部データ』→『ODBCデータベース』)を使うと、DSNを作らないといけません。今回はこのフロントエンドAccessファイルを複数人に配布して使うので、DSNを作成するのは(設定作業が増えるので)避けたいと思います。

ということで、今回は DSN-less 接続 というのを試しました。

DSN-less 接続

DSNを作成しなくても、リンクテーブルを作成する方法です。VBAを使い、接続文字列を自前で用意し、CreateTableDef メソッドでリンクテーブルを作成するという方法です。

https://support.microsoft.com/ja-jp/kb/892490

http://vba-geek.jp/blog-entry-83.html

https://foolexp.wordpress.com/2012/06/14/access%E3%81%A7dns%E3%81%AA%E3%81%97%E3%81%A7odbc%E6%8E%A5%E7%B6%9A/

マイクロソフトさんのサイトにあるサンプルを改良して設定できるようにしました。ちなみに、下記の様な接続文字列を定義しました。

ODBC;DRIVER=SQL Server Native Client 11.0;SERVER=サーバー名;DATABASE=データベース名;UID=ユーザー名;PWD=パスワード

ドライバーとして SQL Server ではなく SQL Server native Client 11.0 を使っていますが、これは後述する理由のためです。

リンクテーブルが作成されて、移行時のデータが表示できれば問題なしです。

ドライバによる不具合

実は、最初はリンクテーブルを

ODBC;DRIVER=SQL Server;SERVER=サーバー名;DATABASE=データベース名;UID=ユーザー名;PWD=パスワード

としてドライバに SQL Server を使って設定していました。

当初はこれで問題なかったのですが、作業途中でサーバー名を変更することがあり、同じやり方でリンクテーブルを張りなおしたら、なぜか日付型のフィールドが文字型として認識されるという状態になってしまいました。何度リンクテーブルを張りなおしても改善されず、リンクテーブルなのでフィールドの型を変更することもできず困ってしまいました。

調べてみると、 SQL Server 側で新しい日付型 (datetime2)を使っているとき、ドライバが SQL Server だとこのような現象が発生するとのことでした。SSMAで移行する場合、Accessの日付型のフィールドが SQL Server 側で datetime2 にマッピングされているため発生した次第です。

解決方法は、 SQL Server Native Client 11.0 を使えばよいとのことです。

MS-Access sees SQL server's datetime2 fields as TEXT - Stack Overflow

SQL Server Native Client のインストール

で、実際に試してみると問題なく日付型として認識できました。

ただ、一点疑問が残るのは、なぜ、最初にリンクテーブルを張った際は正しく日付型を認識できていたのか?という点です。まあ、頑張って再現させても得るものはなさそうですし、とりあえず問題が解決したのでこれで良しとします。

調整

さて、リンクテーブルのリンク先が切り替わっただけなので、修正なしで使えるかと思いきや、あれこれエラーが発生します。 今回対応したもので覚えているのを書いておきます。

3622 エラーが表示される

フロントエンドAccessを開いて、適当なテーブルの中身を表示させると下記の様なエラーが表示されました。

f:id:junichim:20161111164045p:plain

これへの対応としては、読み取り専用の場合は dbOpenSnapshot を指定し、書き込みを行う場合は dbOpenDynaset とオプションに dbSeeChanges を指定しました。

sql - Errors with linked tables and Ms Access ( Run-time error '3622' : dbSeeChanges/Identity column ) - Stack Overflow

DAOプロパティについて --DAO、ADO、SQL & Access フォーラム--

SQL Server の check制約 の内容が不正

SSMA による移行では、Access テーブルに設定してあった入力規則が SQL Server 上のテーブルに対する Check 制約に変換されました。このとき、Access側の記述をそのまま SQL Server 側に当てはめているようで書式が一致しないため、エラーになる場合があります。

具体的には、

like "####"

という4桁の数値という入力規則ですが、 SQL Server 側はこれを認識してくれません。

ということで、これを

like N'[0-9][0-9][0-9][0-9]'

と書き直しました。

DAO 経由でレコードを AddNew で追加しようとする場合、ID(主キー、オートインクリメント)が正しく取得できない

リンクテーブルの接続先がAccessファイルの場合は、AddNewを実行後、update前でも、オートインクリメントのフィールドの値を取得することができました。 でも、SQL Server のリンクテーブルの場合、updateを呼ぶ前だと取得できませんでした(まあ、考えてみれば当たり前ですよね)。

ということで、下記サイトを参考に、いったんupdate実行後、追加したレコードのオートインクリメントフィールドの値を取得するようにしました。

Autonumber value of last inserted row - MS Access / VBA - Stack Overflow

日本語で検索するSQL

フロントエンド側でSQLを組み立ててそのまま実行すると、なぜかうまく結果を取得することができませんでした。 例えば、下記の様な場合です。

Set rs = db.OpenRecordset("select ID from テーブル名 where 名前 = '" & person & "'", dbOpenSnapshot)

SQL Server では文字はUnicodeで扱っているようですが、Access(というよりVBAの開発環境のVBE)がUnicodeに対応していないようです。 幸い、このような処理を行っている箇所は少なかったので、パススルークエリを利用して、SQL Server 側で処理するようにしました。

    Dim query As String
    query = "select id from テーブル名 where カラム名 = N'検索文字列'"

    Set db = CurrentDb
    Set qdf = db.CreateQueryDef("")
    
    qdf.Connect = DbLinkUtil.getConnStringToSQLServer
    qdf.ReturnsRecords = True
    qdf.sql = query
    
    Set rs = qdf.OpenRecordset(, dbOpenSnapshot)
    qdf.Close
    Set qdf = Nothing

これで、得られたレコードセットに対して処理を行いました。

(参考)

当初は、下記リンク先を参考にして、strConvをかますように変更していました。

Set rs = db.OpenRecordset("select ID from テーブル名 where strconv([名前], 64) = '" & StrConv(worker, vbUnicode) & "'", dbOpenSnapshot)

※ 名前フィールドに対するstrconvの64はvbUnicodeのことです

Finding unicode characters in SQL Search

しかし、この方法の場合、場合によっては変換後の文字列に『(』が含まれ、正しいクエリ文字として解釈されないケースがあったため、採用しませんでした。

フォームで新規レコード作成時、規定値が反映されない

Accessファイルへのリンクテーブルを使って、リンク先のテーブルに規定値が設定されている場合は、フォームで新規レコードを作成した際に、規定値が反映されていました。 SSMA により SQL Server へ移行すると、規定値は、default制約の形で反映されます。 しかし、SQL Server へのリンクテーブルだと、フォームで新規レコードを作成した際に、この default制約が反映されません。

これも仕方ないと思うので、フォーム側を修正して、規定値を反映できるようにしました。

最後に

以外と修正箇所が多かったのですが、ここまで修正したところ、エラーをはかずに動くようになりました。

あとは、バックエンドとしてAccessファイルを使っている場合は処理速度的に問題がなかったところが、SQL Server へのリンクテーブルだと問題になるところがいくつかありました。 まあ、構造が変わっているので、仕方ないですね。

これらは SQL server へのアクセスを考慮して、効率的な処理に書き直したり、 SQL Server 側でのユーザー定義関数やストアドプロシージャを使った処理に順次書き直していきました。

一応これで動くようになりました。ふー。 しばらく運用してみて、問題があれば、随時対応していきたいと思います。

Access -> SQL Server への移行 (1/2)

とあるDBを Accessで運用しています。Access での構成は、

フロントエンド(フォーム、レポート、クエリなど)+バックエンド(テーブル)

となっています。バックエンドのaccessファイルに対して、フロントエンドからリンクテーブルを貼る形式です。基本は一人だけが使う構成ですね。

今回、このDBを多人数で使いたい、かつ、外出時も使いたいとの要望があり、 SQL Server に移行することになりました。どうするのが一番簡単に移行できるかと考えてみて、テーブルの部分を SQL Server に移行して、ODBCで接続して、リンクテーブルを貼れば、割と簡単に移行できるのではないかと考えて、試してみました。めったにやらない作業だったので、以下にその際にやったことをメモ書きで残しておきます。

移行前の修正

access を移行する前に、テーブル間の外部キー制約を見直しています。 主に見直した点は、

  • 2つのテーブル間に同じ主キーに対する外部キー(参照整合性制約あり)が設定されている
  • 複数の外部キーを設定してあるテーブルがあって、連鎖削除がある場合に、複数の経路で削除される場合がある

を解消するというものでした。どちらも SQL Server では実現できないというものです。

一つ目については、下記サイトとかがわかりやすいと思います。 www.accessdbstudy.net

二つ目については、マイクロソフトの下記の記事が(比較的)わかりやすいかな。

1 回の DELETE または UPDATE 操作によって起動される連鎖参照動作は、循環参照を含まないツリー形式になっている必要があります。DELETE または UPDATE 操作によって生じる一連の連鎖参照動作の影響を複数回受けるテーブルがあってはいけません。また、連鎖参照動作のツリーはすべての特定のテーブルへのパスが一意である必要があります。ツリーの分岐は、NO ACTION が指定されているテーブルか既定値であるテーブルに到達したところで終了します。 連鎖参照整合性制約

まあ、SQL Server ではできないというんだから、文句言っても始まらないので、テーブル構成の見直しなどで対応しておきます。 ただ、一部のテーブルで、2つのテーブル間に同じ主キーに対する外部キーがあり、ON DELETE CASCADE (と同等の動作)をどうしても実装したかったので、最終的には、SQL Server のafterトリガーを使って実現しました。

若干補足

ちなみに、こういう場合は、下記サイトのように instead of delete トリガーで代替する方法がよく見つかります。今回は、主キー側のテーブルに外部キーが設定されており、これが使えないので、afterトリガーにて実現しました。 www.mssqltips.com

なかなか一筋縄ではいかないですね。

バックエンドaccess ファイルを SQL Server へ移行

Access から SQL Server への移行には SQL Server Migration Assistant を使います。

SQL Server Migration Assistant

下記ページからダウンロードして、インストールします。作業時点のバージョンは 7.0 でした。

Download Microsoft SQL Server Migration Assistant 7.0 for Access from Official Microsoft Download Center

なお、最初デスクトップに作らせたショートカットから起動すると、64bit版が起動して、インストール済みのOfficeが32bit版だったので、起動に失敗してました。

最初は原因が不明で焦ったのですが、ネットを漁るとちらほら似たような情報があり、bit数が違うことが原因だと分かりました。で、スタートメニューから32bit版を起動すると、問題なく起動できました。 Officeは32bit版を使うことを推奨しているんだから、この辺りも気を使ってもらえると嬉しんですけどね>マイクロソフトさん

と、この記事書きながら気づいたんですが、SSMAインストール後に見れるreadmeを読むとインストール要件として、DAOプロバイダーが挙げられています。で、これというのはOficeをインストールすると一緒にインストールされるともあります。SSMAをインストールした環境のOfficeは32bit版だったので、DAOプロバイダーのbit数もおそらく32bitだと思われます。なので、Officeのbit数というよりは、Officeと一緒にインストールされているDAOプロバイダーのbit数といったほうが正確かもしれませんね。 このreadmeのインストール要件にも、Access/Officeが必要、とは書かれてないですからね(移行時にAccessがないと移行できません、なんて制約、うれしくないですしね)。

さて、問題なく起動出来たら、あとは画面のウィザードに従って、SQL Server に移行したいものを選択していくだけです。今回はaccdbファイルに含まれる全テーブルを移行します。

f:id:junichim:20161111151858p:plain

f:id:junichim:20161111151910p:plain

下記の画面で、移行したいAccessファイルを指定します。今回の場合、バックエンドのファイルになります。

f:id:junichim:20161111151919p:plain

テーブル一覧が表示されるので、移行したいテーブルを選択します。画面では全テーブルを指定しています。

f:id:junichim:20161111151929p:plain

SQL Server への接続情報を入力します。今回は、 AWS RDS 上に SQL Server Express Edition (12.0) を立ち上げてそれを使いました。

f:id:junichim:20161111151943p:plain

リンクテーブルを貼るかどうか聞かれるので、状況に応じて設定します。今回はバックエンド側のファイルを移行させるので、作成しませんでした。

f:id:junichim:20161111151952p:plain

上記のようにウィザードに従って作業すると、特に問題もなく作業が完了しました。

一応、移行レポートが表示されたので、あれこれ見ていたら、SQL Server に作成されたテーブルに、SSMA timestamp という謎のカラムが追加されています。 これはなんだ?と思って調べてみると、特に問題はないようです。このままにしとけ、というのが大勢のようなので、特に不都合を感じるまではこのままにしておきます。

stackoverflow.com

再度補足:移行方法について

このDBの開発・運用環境として Access 2013 を使っています。一つ前の Access 2010 まではアップサイジングウィザードという機能があり、これを利用してSQL Server に移行することができたようです。 最初は、Access2010を引っ張り出してきて、この機能を使っての移行も試してみました。一応、こちらのツールでも問題なく移行はできたのですが、nullableのプロパティが全カラムについてnullを許容する、などとなっていました。

これだと、都合が悪いので、SSMAで移行した次第です。

また、他にも SQL Server の管理ツールである SQL Server Management Studio のインポートを使うという手もあるようですが、これだと主キーの設定から再度やらないといけないので、やはり採用しませんでした。

ここまでで、バックエンド側の移行が無事に終わった(ような)ので、次はフロントエンド側Accessの改修作業に移ります。

AWS RDS の SQL Server のネイティブバックアップを使ってみました

最近 AWS RDS の SQL Server を使い始めました(もちろんExpressエディションです)。 いろいろ試していると、ネイティブバックアップという機能を使えるということなので、早速試してみました。

Microsoft SQL Server によるネイティブバックアップおよび復元のサポート - Amazon Relational Database Service

オプショングループの作成

ネイティブバックアップを使うためには、オプショングループに該当するオプションを追加する必要があります。 SQL Server 起動時は何も考えてなかったので、デフォルトオプショングループとなっています。このデフォルトオプショングループにオプションを追加することはできないので、新規にオプショングループを作成します。

RDSの管理画面から、『オプショングループ』を選択して、『グループの作成』を選択します。

f:id:junichim:20161101011059p:plain

『名前』と『説明』に適当な値を入れ、『データベースエンジン』は sqlserver-ex 、『メジャーバージョン』として12.00 を選択します(現在のインスタンスに合わせます)。 問題がなければ、オプショングループが作成されます。

オプションの追加

作成したオプショングループに対して、ネイティブバックアップのオプションを追加します。 対象のオプショングループを選択して、『オプションの追加』を選択します。 『オプション』として、SQLSERVER_BACKUP_RESTORE を選択し、IAMロールがない場合、『新規ロールの作成』をクリックします。

f:id:junichim:20161101011829p:plain

上記のような画面が表示されるので、必要に応じて、IAMロール、S3バケットなどを選択し、場合によっては新規作成します。

『すぐに適用』はこのオプショングループがDBインスタンスに割り当てられていないので、どっちを選択していても大丈夫(なはず)です。 『オプションの追加』ボタンを押すと、選択したオプションが追加されます。

オプショングループをインスタンスに割り当て

わかってみれば、大したことないのですが、実はこれが一番わかりませんでした。 要は、オプショングループをDBインスタンスに割り当てるためには、既存のDBインスタンスを選択して、『インスタンスの操作』から『変更』を選択し、インスタンスの内容の変更画面で、オプショングループを選択する、というものでした。

docs.aws.amazon.com

RDS 知ってると当たり前なのかな?もう少しドキュメントの書き方を工夫して、わかりやすくなってほしいと感じた部分です。

なお、オプショングループの変更と一緒に『すぐに適用』にチェックを入れておきます。『次へ』で確認画面が表示されるので、問題がなければ、『DBインスタンスの変更』をクリックします。 これで、問題が無ければすぐに変更作業が始まります。 しばらくすると、変更が完了となるので(インスタンス画面で確認できます)、新しいオプショングループが割り当てられていることが確認できます。

バックアップの取得

ここまでできたら、早速バックアップを取ってみたいと思います。

docs.aws.amazon.com

こちらのページあるように、 SQL Server Management Studio を起動して、SQL Server に接続します。 『新しいクエリ』を選択して、

EXEC msdb.dbo.rds_backup_database
    @source_db_name=N'database name',
    @s3_arn_to_backup_to='arn:aws:s3:::{bucket name}/{filename}',
    @overwrite_s3_backup_file=0;

と入力して、実行します。 問題が無ければ、しばらくするとS3側にバックアップファイルが作成されています。

取得したバックアップファイルは、同じDBインスタンスに復元できないなど制約はあるものの、復元オプションの一つとして使えそうかなと思っています。