第1回　軽量ASP.NET開発の概要とSQL Server Expressの準備：連載：ASP.NETによる軽量業務アプリ開発（3/3 ページ）

» 2014年05月13日 18時11分公開

[arton， ]

データベースの作成

　データベースを作成するには、SQL Server Management Studioのようなツールを利用するのが一般的だと考えられるが、ここではPowerShellを利用する方法を示す。この方法であれば、SQL Server Management Studioを別途インストールしなくてもデータベースを作成できるからである。

　なお、PowerShellのプロンプトは、既定では「PS カレントディレクトリ >」という形式であるが、ここでは単に「>」に省略する。

　また、以下の例では作成するデータベース名を「testdb」としている。

［コラム］PowerShellの簡単な説明

　以下のリストではPowerShellを使って.NET FrameworkのSystem.Data.SqlClient名前空間のSqlConnectionとSqlCommandという2つのクラスを操作している。このため、C#を知っていればほとんどの点については問題なく読めると想像できる。以下を読み進めていく上では、次のことに留意しておけばよいだろう。

変数名は「$」で始まる。また変数宣言はなく、最初に代入した時点で作成される
C#のnewキーワードの代わりにNew-Objectコマンドを利用してインスタンスを生成する
文末尾の「;」は記述する必要はない
文字列内のエスケープには「\」ではなく「`」（バッククォート）を使う。逆にいうと「\」をエスケープする必要はない

1. SQL Serverが管理するデータベースを作成する

　既定ではASP.NETが使用するIISユーザーはWindows認証を利用してSQL Server Expressに接続できないため、最初に「ログイン」を作成する。なお、これを行うと、同一アプリケーションプールを利用するASP.NETをユーザーインスタンスで利用できなくなるので注意が必要である。

> $conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;Integrated Security=SSPI") ← これからデータベースを作成するので、DataBaseパラメーターは不要
> $conn.Open()
> $cmd = $conn.CreateCommand()
> $cmd.CommandText = "CREATE LOGIN [IIS APPPOOL\DefaultAppPool] FROM WINDOWS" ← （1）
> $cmd.ExecuteNonQuery()
-1
> $cmd.CommandText = "create database testdb" ← データベースを作成する
> $cmd.ExecuteNonQuery()
-1
> $cmd.CommandText = "use testdb" ← 作成したデータベースを利用する
> $cmd.ExecuteNonQuery()
-1
> $cmd.CommandText = "CREATE USER IISAppPoolUser FOR LOGIN [IIS APPPOOL\DefaultAppPool]" ← LOGINに対応したユーザーをデータベースに作成する
> $cmd.ExecuteNonQuery()
-1
> $cmd.CommandText = "exec sp_addrolemember N'db_datareader', N'IISAppPoolUser'" ← 読み取り権限（db_datareaderロール）を付与する
> $cmd.ExecuteNonQuery()
-1
> $cmd.CommandText = "exec sp_addrolemember N'db_datawriter', N'IISAppPoolUser'" ← 書き込み権限（db_datawriterロール）を付与する
> $cmd.ExecuteNonQuery()
-1
> $conn.Close()

SQL Serverが管理するデータベースを作成
　 （1）SQLEXPRESSはIISからのログインを考慮していないので、LOGINを作成する必要がある。DefaultAppPoolは既定でASP.NETが使用するIISのアプリケーションプール名。他のアプリケーションプールを利用する場合は、それに応じたLOGINの作成が必要である。

　なお、Webアプリからテーブルを作成したりするのであれば、db_ddladminロールの付与も必要である。

　Webアプリから上で作成したデータベースを利用するには、接続文字列に以下を指定することになる。

Data Source=.\SQLEXPRESS;Integrated Security=SSPI;DataBase=testdb;

SQL Serverが管理するデータベースをWebアプリから利用する場合の接続文字列

2. ユーザープロセスが直接利用するファイルとしてデータベースを作成する

　データベースの作成前に、直接ASP.NET用のディレクトリにデータベースを作成するか、それとも作成後に移動するかについて考える必要がある。これは、ASP.NET用のディレクトリにユーザーがアクセスできるように権限を付与するか、それともユーザーのディレクトリにIISがアクセスできるように権限を付与するか、という選択でもある。

　この選択に対しては、開発用のPCを利用していることを前提として、IISのディレクトリにユーザーが直接アクセスできるように権限を付与することを勧める。その方が、後でASPXファイルを修正して実行したりするのが楽だからである。また逆にすると、リモートからIISを経由してユーザー固有のディレクトリにアクセスできることになる。これは避けた方が無難である。

　ここでは、C:\inetpub\wwwrootの下にtestというディレクトリを作成して、そこにデータベースを作成する。ここで作成したtestディレクトリは、後にWebアプリ化することになる。

　なお、本連載の以降の回では、こちらの方法で作成したデータベースを利用することを前提とする。そのため、すでに1の方法でデータベースを作成した場合は、以下のリストに従ってPowerShellからコマンドを実行して、作成したデータベースを削除する。

　これは、同じSQL Server Expressはユーザーインスタンスで実行しているかどうかにかかわらず、同じ名前のデータベースを重複して利用できないからである。

　また、1で作成したログインによってIISからのログインが権限借用中エラーとなるため、作成したログインも削除する必要がある。

　なお、同様に作成したユーザーはtestdbデータベースにひも付いて作成されているため、testdbのdrop（「drop database testdb」コマンド）によって同時に削除される。

> $conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;Integrated Security=SSPI")
> $conn.Open()
> $cmd = $conn.CreateCommand()
> $cmd.CommandText = "drop database testdb"
> $cmd.ExecuteNonQuery()
-1
> $cmd.CommandText = "drop login [IIS APPPOOL\DefaultAppPool]"
> $cmd.ExecuteNonQuery()
-1
> $conn.Close()

システムに作成したデータベースとログインを削除する

　ユーザーインスタンスのデータベースの作成手順は以下となる。

（1） Windows Explorer（以下、Explorer）を使ってC:\inetpub\wwwrootディレクトリの下にtestディレクトリを新規作成する

　　－ Explorerのコンテキストメニューに管理者権限での実行を示す盾のアイコンが出ていることに気付かれると思う。このため、作成した後のディレクトリには現在のユーザーからのアクセス権は付与されない

（2） （1）で作成したディレクトリのプロパティダイアログを表示して、［セキュリティ］タブで［編集］ボタンをクリックし、［testのアクセス許可］ウィンドウの［追加］ボタンをクリックして［ユーザーまたはグループの選択］ウィンドウで現在操作を行っているユーザーを追加する。その後、［アクセス許可］の［変更］をチェックして有効にする

（3） （1）で作成したtestディレクトリにApp_Dataという名前の子ディレクトリを作成する

（4） PowerShellで以下のリストを実行する

> $conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;Integrated Security=SSPI;User Instance=true") ← 1と異なりUser Instanceパラメーターを指定する
> $conn.Open()
> $cmd = $conn.CreateCommand()
> $cmd.CommandText = "create database testdb on (name=testdb,filename='c:\inetpub\wwwroot\test\App_Data\testdb.mdf')" ← ［A］
> $cmd.ExecuteNonQuery()
-1
> $cmd.CommandText = "sp_detach_db testdb" ←［B］
> $cmd.ExecuteNonQuery() ← ［B］
-1
> $cmd.Dispose()
> $conn.Close()

ユーザープロセスが直接利用するファイルとしてデータベースを作成
　 ［A］filenameパラメーターにファイル名だけを与えると、データベースファイルはC:\Users\ユーザー名\AppData\Local\Microsoft\Microsoft SQL Server Data\SQLEXPRESSディレクトリの下に作成される。
　 ［B］ASP.NETが利用できるようにデタッチする。

　上のリストを実行すると、c:\inetpub\wwwroot\test\App_Dataディレクトリの下にtestdb.mdfファイル（データベースのデータ格納用）とtestdb_log.ldfファイル（データベースのログ用）の2つのファイルが作成される。これらのファイルに対するIISからのアクセス権限は読み取りと実行のみなので、書き込み権限を付与する。

（5） Explorerから各ファイルのプロパティページを表示し、［セキュリティ］タブで［編集］ボタンをクリックして［～のアクセス許可］ウィンドウで［IIS_IUSRS］に対する［アクセス許可］で［書き込み］をチェックして有効にする

　C:\inetpub\wwwroot\testディレクトリに配置したWebアプリから上で作成したデータベースを利用するには、接続文字列に以下を指定することになる。

Data Source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|testdb.mdf;User Instance=true

ユーザープロセスが直接利用するファイルとして作成したデータベースを利用する場合の接続文字列

　なお、上記の接続文字列の「|DataDirectory|」は、ASP.NETのアプリケーションディレクトリ直下のApp_Dataディレクトリを意味する。

　以上で、Webアプリから利用するデータベースの作成が完了した。

今回のまとめ

　ここでは便宜のためSQL Server Expressを利用してデータベースへアクセスする方法と、ASP.NETのユーザープロセスを利用してデータベースへアクセスする方法の両方の作成方法を示した。

　aを利用すると、IIS稼働中にPowerShellなどの別のプロセスからもデータベースのメンテナンスが実行できるなど、メリットは大きい。その半面、作成したWebアプリが脆弱（ぜいじゃく）だと、Webアプリ固有のデータベースだけではなく、システムが持つデータベース全体が影響を受ける可能性がある*6。

*6　とはいうものの、冒頭で述べたように部門レベルのイントラネットのサービスに限定するのであれば、aの手法でも問題はないと考えられる。

　一般論としては、データベースを含め、C;\inetpub\wwwrootディレクトリの下の1つのディレクトリでWebアプリとデータの両方を格納できるbの方法を選択するのがよい。そのため、以降のアプリケーションではbの方法で作成したデータベースを利用する例を示す。ただし、プログラム上の相違点は、ここで説明したように接続文字列のみなので修正は容易なはずだ。

　次回は、今回作成したtestdbデータベースとtestディレクトリを利用して、ASP.NETを利用した基本的なデータベース操作について説明する。