IRM保護したExcelが開けない（Microsoft Rights Management System, RMS）

2015年の12月に入ってからIRM保護をかけたExcelを開こうとすると、
「アクセス制限サービスに接続中に問題が発生しました。後でもう一度試すか、
　詳細に関して管理者に問い合わせてください。」
とのメッセージが表示されるようになりました。

上記エラーがファイルが全く開けないのでさて困ったぞとなっていたのですが
どうもRMS 2003 のサポートというか製品寿命が2015/11/25に終了したらしい。

まあサポートが終わっているので仕方がないと言えばそうなのだが、突然ファイルが開けなくなるのは困る。
裏ワザでIRMを外してちょちょっと開けるようになるのならそれでいいのだが、そんな事が簡単にできたらIRMの意味が全くない。

という事で対応を迫られた結果サーバーをRMS 2003 から AD RMS(2008 R2) の環境に移行することになりました。

Technetのブログにやり方が書いてあったので参考に進めました。
[参考URL]RMS Troubleshooting: v1.x certificate expiration issues

一応私がやった手順を記載しますが、実際にサーバーを移行しようと考えている方は上記サイトをご自分で確認してください。


前置きが長くなりましたが、RMS v1(2003)から AD RMS(2008 R2)へRMSを移行する方法です。
手順の概要は次の通り
1.RMSのSQLサーバーのバックアップ
2.[旧サーバ]Trusted publishing domain (TPD) のエクスポート
3.[ADサーバ]ADサーバーからService Connection Point を削除
4.[新サーバ]新マシンのOSインストール(2008 R2)
5.[新サーバ]AD RMSの役割のインストール
6.[新サーバ]システム時刻変更
7.[新サーバ]Trusted publishing domain (TPD) のインポート
8.[新サーバ]システム時刻戻す


ステップバイステップはこちら
1.RMSのSQLサーバーのバックアップ
　SQL Server Enterprise Manager でRMS構成SQL Serverに接続する
　"DRMS_Config_(サーバー名)_80"という名前のデータベースをバックアップする
　　

2.[旧サーバ]Trusted publishing domain (TPD) のエクスポート
　ここでスクショを取り忘れたのですが"RMSの管理"からTPDのエクスポートをします。
　上記のTechnetを参照しながらやってみてください。
　エラーが出ることもあるらしいので気を付けて。
　出力はxmlファイルになります。

　xmlファイルを別の場所に移したら旧サーバのネットワークを無効化してシャットダウンしておきます。

3.[ADサーバ]ADサーバーからService Connection Point を削除
　ADサーバーで"Active Directroyサイトとサービス"を起動
　
　もし見当たらないよ～ってなったら最上位のノードを選択した状態で、メニューの[表示]-[サービスノードを表示]で表示されます。

4.[新サーバ]新マシンのOSインストール(2008 R2)
　コンピュータ名は旧サーバと違う名前にします。
　DNSサーバーでCNAMEを作成して旧サーバの名称での問い合わせに対して新サーバのIPが返るように設定します。

　これはRMS用のhttpアドレスで、旧サーバでのアドレスを使い続けるために必要な設定になります。
　
5.[新サーバ]AD RMSの役割のインストール
　役割の追加で"Active Directory Rights Management サービス"にチェック
　IISとかも勝手にインストールされます。

　
　
　
　
　
　
　
　ここでRMS用のURLを決めるのだが、旧サーバと同じアドレスになるように設定する。
　通常は旧サーバのホスト名のはず。
　
　
　

6.[新サーバ]システム時刻変更
　サーバの時刻を一時的に"2015年11月25日"に修正します。
　Hyper-Vで新サーバを立ち上げた場合は、ホストとの時刻同期を切っておくことをお勧めします。

7.[新サーバ]Trusted publishing domain (TPD) のインポート
　
　1.でエクスポートしたxmlファイルを取り込みます。
　
　ちなみにエクスポート時はパスワードのチェックはありませんが、インポート時はドメインポリシーに沿ったパスワードの複雑性が求められます。
　私はパスワードを適当につけたため１回インポートに失敗しました（汗）
　旧サーバを立ち上げてエクスポートし直して、DNSの設定もやり直したらうまく動いたのでよかったです。


8.[新サーバ]システム時刻戻す

以上の手順でIRM保護をしていたファイルが開けるようになっているはずです。

注意点ですが、

AD RMSはRMS v1のCALでアクセスできません（ライセンス的に。多分）。

私のところではIRMの利用率が低かったこともあり、既存のIRM保護ファイルは全てIRMの解除をして、RMSサーバもつぶす方向で対応するつもりです。

Report Server で円グラフを使う

Microsoft SQL Server 2008 R2 のReporting Serivce についてです。

レポートに円グラフを使ってみたんですが、
円グラフで重要な指標といえば構成比。
要は全体に対するパーセンテージですね。

グラフ上にパーセンテージを表示させるのは比較的簡単で、
データラベルを表示させて、
データラベルのプロパティで、「#PERCENT」 を選べばグラフ上にパーセンテージが表示されます。

ただ、このままでは小数点以下が2桁表示されていて
表示が込み入ってくると見づらくなってくるので、小数点以下の桁数を調整したいんですが、
ここに罠が。


データラベルのプロパティを見ると"Format"プロパティがあるので、
ここかなーと思って設定しても全然反映されない。

正解は、先の「#PERCENT」に桁数の指定をつけて、「#PERCENT{P1}」と設定します。
（正確に言うと、データラベルのLabelプロパティ）

分かりにくっ！！

＜参考URL＞
http://msdn.microsoft.com/ja-jp/library/dd239373(v=sql.105).aspx

ピボットテーブルが便利すぎる

最近SQL Serverのピボットテーブルを覚えました。
こんな感じで出来ます。

データソースのテーブルとしては、

＜uriage_t＞
 商品コード | 売上月 | 売上金額
-------------------------------------------
 A          | 1月    |  \1,000
 A          | 2月    |  \2,000
 A          | 3月    |  \3,000
 B          | 1月    |  \4,000
 C          | 3月    |  \5,000

で、ピボットテーブルを作成するSQLは、

SELECT *
FROM uriage_t
PIVOT (
  SUM(売上金額) FOR 売上月 IN ([1月], [2月], [3月])
)

すると、こんな感じに集計されます。

 商品コード | 1月    | 2月    | 3月
-------------------------------------------
 A          | \1,000 | \2,000 | \3,000
 B          | \4,000 | <NULL> | <NULL>
 C          | <NULL> | <NULL> | \5,000

SQLのポイントとしては、次の通り

• 集計対象のテーブルにくっつけてPIVOT句を書く
• SUM指定された項目を集計する
• FORで指定された値が列として出力
• 集計対象のテーブルの項目の内、SUMとFORで指定された列以外で、GROUP BY が自動的にかかる

一覧表をバーンと出すのに便利なんですが、それでも足りない点がいくつかあります。

• 集計列は固定で指定しなければいけない（動的SQLを使えば一応できる）
• 集計の軸は１つのみ（例えば、金額と数両を同時に集計できない）

特に動的SQLは、C#やVBなどのアプリケーション側であれば簡単といえば、簡単なんですが、ストアド内で動的SQLを使おうとすると、一挙にソースの見通しが悪くなるのが辛い。

とはいえ、これが使えると『SQLでデータを取得して、主キーが同じ値の間ループして、該当する列に値を入れていく』とか言うめんどっちいことが要らなくなります。

便利なんで、ついつい使っちゃうんですが、単発のレポートを作るんだったら
Excelのピボットの方が１０倍簡単なのが悲しいところですな。

とほほ・・

SSASの開発のために、開発環境にカレンダーテーブルとビューを作ってあったんですが、 本番環境の一部のテーブルを戻すために本番バックアップを開発環境にリストアしたら、上書きされてなくなってしまった。 なくなったことに気づいて、開発環境のバックアップから戻そうにも、バックアップの保持期限が2週間のため既に存在せず・・・ 同じように作ってみたものの、ディメンションの処理でエラーを起こしてしまう。 これから解析かと思うとうんざりですな。 開発環境＝いつでもいじくりまわして良い環境とか思っていると痛い目にあうという話でした。

データのインポートがSQL Serverエージェントで実行できない

SQL Server で夜間の内に本番環境からテスト環境にデータをコピーしているんですが、
SQL Serverエージェントのジョブがコケていました。

ジョブの履歴でエラーを確認してみると、

説明: エラー 0x8009000B "指定された状態で使用するには無効なキーです。" により、保護された XML ノード "DTS:Password" の暗号化を解除できませんでした。この情報にアクセスする権限がない可能性があります。

とのこと。

原因は、SQL Server Management Studio の「データのインポート」機能を使ってパッケージを作り、SQL Server上に保存していたんですが、
パッケージの保存時にデータの暗号化がされていたためでした。

暗号化すると何かと面倒なので、ロールでアクセス制御するようにパッケージを作り直しました。


「データのインポート」でパッケージを作成し、保存時に、保護レベルを [アクセス コントロールをサーバー ストレージおよびロールに依存する (Rely on server storage and roles for access control)] に設定します。


[参考URL]
トラブルシューティング : SQL Server エージェントを使用した SSIS パッケージ実行
http://msdn.microsoft.com/ja-jp/library/dd440760(v=sql.100).aspx

暗号化したい人は、SQL Serverエージェントの作成時にパスワードを指定する必要があります。
[参考URL]
SQL Server エージェント ジョブを使用してパッケージを実行する方法
http://msdn.microsoft.com/ja-jp/library/ms139805(v=sql.90).aspx


SQL Server 2000のときは何も考えずにできたんですけどね。
世知辛い世の中です。

Analysis Serviceのリレーションシップ設計

最近SQL ServerのAnalysis Services の構築を試みてます。

一応、「徹底検証 SQL Server 2008 データウェアハウス環境構築」という本を片手にやってますが、いかんせん分からないことが多すぎる。

まあ多次元データベースに関する知識がこれっぽっちもないので仕方ないのかもしれませんが・・・

今日引っかかったのは複合キーによるテーブル結合。

ずばり、、できないらしいです。

説明しますと、ファクトテーブルを中心にディメンションテーブル（通常はマスタの類）を結合してきますが、この時にディメンションテーブルの主キーが複合キー（複数列からなる主キー）だとうまくデータが取れません。

この場合は、複合キーを結合やらなにやらで1つの論理主キー列を作成して、その列でリレーションを張る必要があります。


細かいことはMSDNで。
http://msdn.microsoft.com/ja-jp/library/gg492102.aspx


4時間くらい悩んだ…

UPDATEでSELECT文

Microsoft SQL Server で、SELECT文の結果で、UPDATEをかけたい場合

UPDATEでSELECT文を書きたい場合、方法はいくつかあるが自分が便利だと思うものを。


例）テーブルB で、テーブルAの列を更新したい場合

UPDATE テーブルA
SET 列1 = B.列2
FROM テーブルA A
　INNER JOIN テーブルB B ON A.キー1 = B.キー1
WHERE A.列 = [更新対象の条件]

この構文の便利なところは、SELECT文のSQLの結果を事前に確認できるところ。

上記のSQLのFROM句以下はそのままで、

SELECT *
FROM テーブルA A
　INNER JOIN テーブルB B ON A.キー1 = B.キー1
WHERE A.列 = [更新対象の条件]

とすると、更新対象のレコードと、更新する値を事前に確認できる。

これで全レコード書き換えちゃったみたいなミスもなくなりますね。