【SQL】PowerPivotを使用してデータベースに接続するとアクセスが拒否される


PowerPivot のだいご味の1つは、利用者自身がデータベースに接続して分析を行える点にありますから、利用者に公開したいデータベースにはアクセス権を設定しておかなければなりません

多くのインフラ担当のSEにとって「SQL Server のセキュリティ」というのは鬼門となっており、実は「適当に」設定してあるシステムは少なくありません。

そんな状況で、一般ユーザー権限で EXCEL 2010 ベータ版の PowerPivot を使用し、SQL Serverに接続する場合を考えてみます。

image

[データベースインポートウィザード]に SQL Server のサーバー名(ここでは SQL01)を入力して[接続テスト]ボタンをクリックすると、環境によってはエラーが出ると思います。

image 

原因は3つ考えられます。

  • SQL Server がインストールされているサーバーの ファイアウォール設定によりポートが閉じられている
    → [セキュリティが強化されたWindows ファイアウォールの設定]を見直してください
  • SQL Server に接続しようとしているプロトコルが許可されていない
    → SQL Server 構成マネージャーを見直してください。
    image
  • ユーザーにSQL Serverにアクセスするための権限が無い 

今回は、3つ目の原因である「ユーザーにSQL Serverにアクセスするための権限が無い」場合の対処法について考えてみます。

以前にもご紹介しましたが、SQL Server 2008 自習書シリーズの1つ「セキュリティ」および「ログイン認証とオブジェクト権限」は、SQL Serverのセキュリティに関し、とても詳しくそしてわかりやすく解説してくれています(例によってSQLQualityの松本さんありがとうございます!!!)。

SQL Server 2008 自習書シリーズ http://technet.microsoft.com/ja-jp/sqlserver/dd610200.aspx

image
これはわかりやすい!!PowerPivot を展開する予定があるなら必読です!

ここでは、ドメインユーザーである junichia に対して Northwind データベースおよび配下のテーブルへの「参照」が可能になるようにアクセス権を設定していきます。

自習書にならい、3つのチェックポイントを意識して設定してみます。

なお、この例で使用している SQL Server のバージョンは、2008 R2 です。

【第1のチェックポイント:SQL Serverへの接続を可能にする】

セキュリティの設定を何もいじっていない状態で、「接続テスト」を行うと、以下のエラーが表示されるはずです。

image
サーバーに接続できませんでした。理由:ユーザーはログインできませんでした。

この場合は以下の対処を行います。

  • SQL Server Management Studio を起動し、[セキュリティ]-[ログイン]を展開
  • [ログイン]を右クリックして[New Login] を選択
  • [ログイン - 新規作成] 画面で、[ログイン名]にドメインユーザーである「junichia」を、[既定のデータベース]に「NorthwindJ」を指定する
  • [OK]をクリック
    image

ここまで完了すると、SQL Serverへの接続が可能になります。PowerPivotの接続画面で再度接続にトライしてみてください。今度はサーバーには接続できるものの、以下のようなエラーが出力されます。

image
サーバーに接続できませんでした。理由:ユーザーの規定のデータベースは開けません。ログインに失敗しました。

自習書にも記載されているように、「第1のチェック」だけではデータベースに接続することはできないためです。

【第2のチェックポイント:データベースへの接続】

次に、junichia に対して、NorthwindJ データベースへの接続を許可します。

SQL Server Management Studio で、junichia のプロパティを開き、[ユーザーマッピング] を選択してください。

ここで、データベースの一覧から「NorthwindJ」をチェックして[OK]をクリックします。

image

この状態で、再度「接続テスト」をしてみてください。以下のように正しく接続できるはずです。

image

しかし、ウィザードを進めて、いざテーブルの一覧を取得しようとすると、以下のようにまたエラーが発生します。これはオブジェクトの操作権限が無いためです。そこで「第3のチェックポイント]に進みます。

image
No Tables ware found in the data source.

【第3のチェックポイント:オブジェクトの操作権限】

  • SQL Server Management Studio の[データベース]ノードを開き、[NorthwindJ] を右クリックして[プロパティ]を選択する
    image
  • [データベースのプロパティ]画面で[権限]を選択し、「TECHFIELDERS\junichia」の明示的な権限一覧で「選択」にチェックする。「接続」にはすでにチェックされている。
    image

以上で完了です。

テーブル一覧には、以下のように NorthwindJ データベース内のテーブル一覧が表示され、各テーブルに対して select 文が発行できるため、PowerPivot内へのデータのインポートが可能になります。

image 

今回はデータベース全体に対して「選択」権限を与えましたが、もちろんテーブル単位に設定することも可能です。

今後 PowerPivot を展開して「セルフサービスBI」を社内に展開しようとされている管理者の皆さん。SQL Serverのセキュリティの理解は必須です。

がんばりましょう。私も、苦手なSQL Serverですが、がんばります。

Comments (0)

Skip to main content