Weblog it-azusa

SQLserver – インポートで行削除ができない

インポートで行削除ができない

先日、SQLserver2005が動いているサーバーの外付けディスクがクラッシュしてしまいました。この中にはSQLserverのデータファイルも入っていました。重要なデータは他のサーバーにバックアップしてありましたが、SQLserverのデータファイルはコピーができないため、ファイルのバックアップはしていませんでした。ただデータは主なレコードを別のSQLserver2012にスクリプトでコピーをしていたため、全て失うということではありません。

コピーしたSQLserver2012のデータファイルをアタッチできれば、それだけで修復は終わりですが、バージョンが上位ということで、はねられてしまいます。しかたなく、別のフォルダに残っていた古いデータファイルをアタッチして、SQLserver2012からインポートすることにしました。

SQLserver2005のインポートには、転送先テーブル内の行を削除するというオプションがあり、それを使用してインポートしました。ところが結果を見ると、行（レコード）が複数できています。おかしいと思い再度やってみると、さらに同じ行が増えます。転送先テーブル内の行を削除するというオプションが効いていないようです。
転送先テーブル内の行を手動で削除すればいいのでしょうが、大変手間がかかります。新たにテーブルを作成するというオプションもありますが、この場合IDENTITYがコピーされず、手動で設定し直しとなります。初めてSQLserverにデータベースを作成する時はこの方法でおこなうのですが、今回のようにレコードだけのコピーに新たにテーブルを作成するというのは抵抗があります。

どうもおかしいと思い、ネットで調べてみると、なんとか見つかりました。どうもSQLserver2005のインポートのバグのようです。

インポートのウィザードではデータベースを選ぶと、全テーブルの一覧が出ます。そこで左上の変換元にチェックを入れると、全テーブルが選択されて反転表示されます。この状態でマッピングの編集を押すと設定ウィンドウが出て、行の削除とIDの挿入にチェックを入れます。これで次へを押せばインポートが実行されます。結果は最初に書いたように行の削除は行われず、行がダブルためIDの挿入もエラーを起こすという、めちゃめちゃな結果になります。


↑左上の変換元にチェック（全選択）

これを避けるためには、左上の変換元（全テーブル一括）のチェックを止めて、変換元テーブルの左側にあるチェックボックスを個別にオンにします。さらにこの変換元テーブルを1つ選択し、ハイライトさせた状態で「マッピングの編集」をクリックします。設定ウィンドウが出て、行の削除とIDの挿入にチェックを入れます。


↑テーブルを1つづつ選択

これを全てのテーブルに繰り返し設定します。全てのテーブルの設定がすんだら次へを押してインポートを実行します。結果は変換先のテーブルの行が削除され、IDも問題なく挿入されます。いくつもテーブルがあると少し大変になりますが、新たにテーブルを作成して、IDENTITYやキーの再設定をするよりはましといえます。
このようなバグはSQLserverのバージョンアップで解消されていると思われます。バージョンアップする方がいいとは思いますが、上位互換がなくなる機能もありますので、安定した運用に支障をきたすことがあります。バグは、方法があれば、できるだけ解消しておいた方がいいでしょう。

頻繁に来る不正アクセス

以前から、サーバーのアプリケーションログに「ユーザー ‘sa’ はログインできませんでした。」というイベントが多数記録されており、時々「ログファイルが一杯になった」という警告まで出ていました。SQLserverへの不正アクセスです。IPアドレスを当ブログのIPアドレスの計算方法 でチェックをかけると、当然のことですが、全て外国からのものでした。
当サーバーには、ポートのフィルターをセットしていて、誰でも知っているTCP/1433、1434、UDPは閉じてあります。動的ポートが生きているのかと思い、MSのサイトの説明に従って、動的ポートを削除し、固定ポートをセットしました。IP1～IPALLまであるのも、「すべて受信待ち」をいいえにして、ローカルアドレスのみ有効にしました。これでいいと思い、翌日見て見ると、また例の失敗イベントが山ほど書き込まれていました。

ポートが固定できない？

MSの説明どおりにやってもポートは固定できないのではないかという疑問も生じました。その場合には、現在の外に開いているサーバーをやめて、ローカルの閉じたサーバーにするしかないとも考えました。
それにしても分からないのは、開けるポートの情報は山ほどありますが、閉じるポートの情報はほとんどないということです。攻撃してくる側はSQLserverを知り尽くしており、高いWeb技術を持っている訳ですから、これでは勝負になりません。サーバーを引っ越す前に、それでもと思い検索してみると、やっとのことでポートを記載しているページを見つけました。
SQLserverの使用する全ポート
これを見るとSQLserverは沢山のポートを使用しているのが分かります。この中から当サーバーでは、開いているポートを書き出し、それを閉じることにしました。この中には、既に閉じている1433、1434のポートももちろんあります。

ようやく攻撃ポートを閉じる

翌日、ログを見てみると、例のログイン失敗は完全に無くなっていました。ようやくポートを閉じることができました。それにしてもSQLserverは無防備なサーバーです。ファイアウォールで囲っていないと大変危険ではないかと思います。多くのWindowsレンタルサーバーがSQLserverを動作させています。中には古いサーバーもあるかもしれません。攻撃するハッカーからは、格好の的であり、既に「saでログイン」されているサーバーもあるような気がします。