WebSurfer's Home

トップ > Blog 1   |   Login
Filter by APML

ユーザーインスタンス

by WebSurfer 14. September 2010 21:54

Visual Studio での SQL Server 接続の設定

将来的には無くなる機能だそうですが、SQL Server 2005/2008 Express Edition には、Visual Studio と連携しての DB の取り扱いに便利な「ユーザーインスタンス」という機能があります。

(2014/6/11 追記:ユーザーインスタンスが非推奨になり、その代わりに SQL Server 2012/2014 Express LocalDB の使用が推奨されています。Visual Studio 2012, SQL Server Express 2012 以降では LocalDB が使用できますので、そちらを使用することをお勧めします。)

左の画像は、Visual Studio 2010 を利用して、Web サイトプロジェクトのソリューションエクスプローラで App_Data フォルダに SQL Server データベース (Database2.mdf) を追加し、サーバーエクスプローラで Database2.mdf を右クリックして「接続の変更」で内容を見たところです。

画像をクリックすると拡大画像が表示されます。データソース(S) が「Microsoft SQL Server データベースファイル (SqlClient)」となっていますが、これがユーザーインスタンスを利用した接続になります。

この「ユーザーインスタンス」と、実際の運用環境で使用する「既定のインスタンス」とはかなり違います。自分なりに簡単に違いをまとめたので、忘れないように書いておきます。

(1) 既定のインスタンス

SQL Server が起動された時点から存在するインスタンスで、実際の運用環境ではこれに接続して DB を利用します。

ただしこれを利用するには、事前に、かつ静的に、目的の DB をデーターベースエンジンにアタッチし、ログインアカウントを設定し、DB に対するアクセス権の設定などを行う必要があります。

他に「名前つきインスタンス」というのもありますが、特定の名前がついているだけで「既定のインスタンス」と同じです(細かいことを言えば違いますが、とりあえずここではそう思っていいです)。

(2) ユーザーインスタンス

SqlConnection.Open で自動的に PC にログインしているユーザー専用のインスタンスを作り(既定のインスタンスとは別に)、接続文字列で指定した DB を動的にアタッチし、そのユーザーに DB 所有者の特権を与える・・・という感じです。

それゆえアタッチとかログインなどの設定をしなくても、Access と同様に SQL Server に接続して DB を扱えるようになります。

ただし、リモート接続は不可とか、レプリケーションは無効とか、SQL Server 認証をサポートしないなど制約が多いので、Access のように使いたいという特殊なケースを除いては、これを実際に運用に使うことはありません。

ユーザーインスタンスの詳しい説明は MSDN ライブラリ「管理者以外のユーザーのためのユーザー インスタンス」にありますので、そちらも見てください。

Visual Studio を使って開発する場合、特に Express Edition では Visual Studio からは「ユーザーインスタンス」にしか接続できないという制限があります。ですから、MSDN フォーラムの書き込みなどを見ていると、アタッチとか、ログインアカウントの設定などは知らない人も多いようです。

また、自サーバー構築で、開発環境をそのままサーバーに導入して、接続文字列もそのまま使って、ユーザーインスタンスに接続している人もいるようです。

Tags:

SQL Server

DB のアタッチ

by WebSurfer 13. September 2010 23:56

SQL Server の既定のインスタンスに接続するためには、まず当該 DB をデータベースエンジンにアタッチしなければなりません。無知な素人(自分のこと)は、たぶんここでハマります。また時間を無駄にすることがないように備忘録を残しておきます。

ポイントは ACL の設定です。アタッチする前に、SQL Server のサービスアカウントに、データ/ログファイル (.mdf, .ldf) があるフォルダに対するフルコントロール権限を与えることが必要です。

ACL の設定

そのことは、MSDN ライブラリ「Windows サービス アカウントの設定」(SQL Server 2005 の記事ですが、このあたりは最新版でも同じ)に書いてあったのですが、隅っこのほうに小さく書いてあって気がつくのにかなり時間がかかりました。(笑)

「SQL Server サービスに対する Windows 権限の確認」のセクションに以下のように書いてあります。

"MSSQLServer サービスの開始アカウント: アカウントは、データまたはログ ファイル (.mdf、.ndf、.ldf) が常駐するフォルダーに対するフル コントロール権限を持っている必要があります。"

なお、Express Edition の場合は、上記の MSDN ライブラリのページに書いてあるアカウント名、グループ名を以下のように読み替えてください。(下の「2013/7/1 追記」に注意)

アカウント: MSSQL$SQLEXPRESS(正確には NT SERVICE\MSSQL$SQLEXPRESS)
グループ:  SQLServerMSSQLUser$<computer_name>$SQLEXPRESS

ASP.NET 開発環境で作った mdf ファイルを App_Data フォルダごとサーバーにコピーして、それをアタッチするのであれば、App_Data フォルダへのフルアクセス権を与えなければなりません。上の画面は、その場合の設定です。

ホスティングサービスが提供する SQL Server を使う場合は知らなくて済む話ですが、自サーバーを構築しようとするとこのあたりは避けて通れません。

ただ、ホスティングサービスの場合、mdf ファイルをコピーしてサーバーに張り付けることができないところに別の苦労があるのですけどね。

------- 2013/7/1 追記 -------

SQL Server の Express 版をインストールすると、デフォルトでは「名前つきインスタンス」となり、インスタンス名は SQLEXPRESS になります。(詳しくは、別の記事 SQLEXPRESS は「名前つきインスタンス」名 を参照してください)

上記のアカウント名 MSSQL$SQLEXPRESS、グループ名 SQLServerMSSQLUser$ComputerName$SQLEXPRESS はデフォルトで SQL Server Express をインストールした場合のものです。

Express 版でも、既定のインスタンスまたは SQLEXPRESS 以外の名前つきインスタンスとしてインストールできますが、その場合は上記の名前とは異なってきますので注意してください。具体的には以下のようになるはずです。

<アカウント名>
既定のインスタンス: NT SERVICE\MSSQLSERVER
名前つきインスタンス: NT SERVICE\MSSQL$<instance_name>

<グループ名>
既定のインスタンス: SQLServerMSSQLUser$<computer_name>$MSSQLSERVER
名前つきインスタンス: SQLServerMSSQLUser$<computer_name>$<instance_name>

------- 2014/6/12 追記 -------

正しく権限が設定できていても、アタッチしたデーターベースが「読み取り専用」となることがあります。

その場合は以下のようにすると「読み取り専用」を解除することができます。

SQL Server Management Studio を起動する ⇒ オブジェクトエクスプローラに表示されている当該データベースを右クリック ⇒ 出てくるコンテキストメニューの[プロパティ(R)]をクリック ⇒ 「データーベースのプロパティ」ダイアログが表示される ⇒ 「ページの選択」ウィンドウで[オプション]を選択 ⇒ 「その他のオプション/状態/読み取り専用データベース」を False に設定(下の画像参照)

「読み取り専用」の解除

Tags: ,

SQL Server

ユーザー権限の設定

by WebSurfer 12. September 2010 20:55

開発環境でユーザーインスタンスを使って DB に接続するのではなく、DB ファイル(mdf ファイル)を SQL Server データーベースエンジンにアタッチして、それにアクセスして使えるようにするには、いろいろ面倒なことがいっぱいあります。

今回は、DB のアタッチとか、ログインユーザーの作成は済んでいるとして、ログインユーザーへの権限の与え方について備忘録を書いておきます。

ログインユーザーのサーバーロール設定

まず左の画像ですが、BGLB\SQLUser というログインユーザーへサーバーロールを与えているところです。クリックすると拡大画像が表示されます。

サーバーロールを与えるというより、デフォルトで public 権限が与えられていて、自分では何も設定していないのですが。(画像のように public にはデフォルトでチェックが入っていて、それを外そうとしても外せません)

public のサーバーレベルでの権限は、デフォルトで VIEW ANY DATABASE 権限と、既定のエンドポイント(TSQL Local Machine、TSQL Named Pipes、TSQL Default TCP、TSQL Default VIA)に対する CONNECT 権限のみです。権限は変更できるのですが、よほどの事情がない限り変更する必要はなさそうです。権限を追加したりすると、ログインユーザー全員にサーバーレベルでその権限を与えてしまうことになるので、注意が必要です。

ログインユーザーには、public の他に、dbcreator など 固定サーバーロール の権限を与えることができますが、データベースの更新、削除、実行、接続、選択、挿入をするだけの一般ユーザーに対しては、与えるサーバーロールは public のみで不足はないと思います。

ときどき、ADO.NET を使ったアプリケーションが出す "dbcreator 権限がありません" というようなエラーメッセージに惑わされ(?)て、ログインユーザーに dbcreator のセキュリティ特権を与えて(上の画像で言うと[サーバーロール (S):]下の一覧の中の dbcreator にチェックを入れて)解決したという記事を Web で目にしますが、それはやりすぎです。そのユーザーにサーバー全体で dbcreator 権限を与えてしまうことになりますので。

ログインユーザーの作り方は割愛しますが、何故 BGLB\SQLUser というドメインユーザーアカウントを使っているかについて、ちょっと説明します。

自分の試験環境ではドメインコントローラに SQL Server をインストールしていますが(サーバーを 1 台しか持っていないので、苦肉の手段です)、その場合、NETWORK SERVICE マシンアカウントを SQL Server の実行アカウントに使用できないそうです(やってみましたが、実際できませんでした)。

そういうわけで、BGLB\SQLUser というドメインユーザーアカウントを設けて、それを SQL Server の実行アカウントにしています。

また、NETWORK SERVICE は何故かログインアカウントに設定できなかったので、BGLB\SQLUser をログインアカウントにしています。Web アプリケーション(IIS のワーカープロセス)から SQL Server にアクセスするために、BGLB\SQLUser を偽装しています。

ユーザーマッピング

次にユーザーマッピングの設定です。「このログインにマップされたユーザー(D)」でアクセスする DB を指定します。

アクセスする DB にチェックをつけるだけで、その他はデフォルトで OK です。「UserDatabase のデータベースロールメンバーシップ(R)」もデフォルトの public のみとしておきます。

アクセス権限の細かい設定は、それぞれの DB で行います。もちろん、それぞれの DB で行うより上位のレベル(即ち、ログインアカウントでの設定)でも可能ですが、それでは権限の与えすぎになってしまうようです。

その設定は、当該 DB を右クリックして表示される「データベースのプロパティ」ダイアログで行います。次の画像を見てください。

ユーザーマッピング

「ユーザーまたはロール(U)」でアカウントを選んで(画像では BGLB\SQLUser のみですが)、「BGLB\SQLUser の権限(P)」で「明示的」タブを選択します。

そこで、更新、削除、実行、接続、選択、挿入にチェックを入れれば、ほとんどの場合 OK だと思います。

この画面は、「明示的」タブでの選択結果を「有効」タブで確認しているところです。

本当は、データを操作するのに必要なストアドを作って、ストアドに権限を設定するのがよいのだそうです(DB 全体に対する権限を与えるのではなくて)。

でも、そこまではとてもできないので、特定のアカウントに対して、特定の DB 全体を CONNECT, EXECUTE, SELECT, INSERT, DELETE, UPDATE できる 6 つの権限を与えてみました。それでは権限の与えすぎかもしれませんが。

以上、特定のデーターベースユーザーに権限を与える方法を書きましたが、データベースロールを使う方法もあります。

例えば、データベースロールの中にも public ロールがあり、これに権限を与えると当該データベースのデータベースユーザー(ログインユーザーではなくて当該データベースにマップされた特定のユーザーのみである点に注意)全員にその権限が適用されます。

それではあるユーザーに対しては権限の与えすぎになってしまう場合は、そのユーザーの権限を「拒否」して調整することができます。

その方法については、SQL Server 2012 自習書の記事 3.8 オブジェクト権限の状態(GRANT/DENY/REVOKE)が参考になると思います。

Tags:

SQL Server

About this blog

2010年5月にこのブログを立ち上げました。その後ブログ2を追加し、ここはプログラミング関係、ブログ2はそれ以外のトピックスに分けました。

Calendar

<<  August 2022  >>
MoTuWeThFrSaSu
25262728293031
1234567
891011121314
15161718192021
22232425262728
2930311234

View posts in large calendar