More than 3 years have passed since last update.

ジャパンシステム株式会社

【ASP.NET＆Microsoft SQL Server】IIS(local filesystem)で公開したWebアプリにSQL Serverを紐づける

Last updated at 2021-08-20Posted at 2021-08-03

ちょっと詰まったので、色々調査しました、メモ.

私的にもまとめておくと、ほかの記事が読みやすかったりするので良いと思う。

SQL Serverって扱うの結構大変よね

開発＆動作環境

Windows 10 20H2 (OSビルド19042.1110)
Visual Studio 2019 Professional (Version 16.9.4)
Microsoft SQL Server 2016 (13.0.4001)は、Visual Studio導入時に「ASP .NETとWeb開発」をクリックすると、同時にインストールされるものを設定する(※変更可)
SQL Serverに定義されたデータにアクセスするため、SQL Server Management Studioを導入

開発はASP .NET MVC Core 5で実施 (LTS版である6に今後移行する前提)
IISで実行するプログラムは、スタンドアロンまたはグループ内ネットワークのみアクセスするのを想定してfilesystemとする

※有償で必要なものはVisual Studio 2019 Professionalのみ(意外とお手軽).頑張ればVS Codeでもいけるはずや.
※IISのインストールなどの内容は他の記事を参考にすること

やりたいこと

以下、アプリケーションの定義ファイル(appsetting.json)の中身。

DBの設定用に

OpMeasureDb ... デバッグ用
(Visual Studioの動作確認用に、IIS Expressを使って動作確認をする)
OpMeasureDbRuntime ... ランタイム用
(本番の運用のために、IISを使って運用する. ソフトウエアのアップデートの時には、デプロイ先のfilesystemのファイルのみを入れ替えて実行できるようにする)

として分割し、デバッグ用のデータベースは別の環境でテストを完了したものを用意する。

Visual Studio 2019の画面に従って、Publish(発行)を実施。
発行時のディレクトリは、C:\deployment\を指定。

発行するとこんな感じで、一連のexeファイルやdll、pdbファイル、jsonファイルなどを一気に出力してくれます

このとき、OpMeasureDb_debugに定義されているように

{
 "ConnectionStrings" : {
  "OpMeasureDb_debug" : "Server=(localdb)\\mssqllocaldb;Database=OpMeasureDb;
      Trusted_Connection=true;MultipleActiveResultSets=true"
 }
}

と書くと、C#側のstartup.csで定義されているメソッドConfigureServicesの引数IServiceCollection型servicesで、例えば

startup.cs

// AppDbContextはDbContextを継承して作ったクラスで、
// Code Firstの形式で定義したクラス
services.AddDbContext<AppDbContext>(option => options.
         UseSqlServer(Configuration.GetConnectionString("OpMeasureDb_debug")));

と書くだけで、SQL Server 2016で簡単にCode Firstを実現することが出来る。

ところで、これを例えば、ランタイムモードにしたときに

{
 "ConnectionStrings" : {
  "OpMeasureDb" : "Server=(localdb)\\mssqllocaldb;Database=OpMeasureDbRuntime;
      Trusted_Connection=true;MultipleActiveResultSets=true"
 }
}

として、

startup.cs

// AppDbContextはDbContextを継承して作ったクラスで、
// Code Firstの形式で定義したクラス
services.AddDbContext<AppDbContext>(option => options.
         UseSqlServer(Configuration.GetConnectionString("OpMeasureDb")));

と書けば最初は通るものと考えていた。もちろん、Visual Studioでデフォルトで実行するときに使用されるIIS Expressでは実行されるし、生成されたexeを直接実行しても動くのだ。

でも、これをIIS経由で動かそうとすると、DBがまともに動かなくなった。
というか、動くけれども、Postmanやブラウザなどで、データ定義済みのデータベースからWebAPI経由でGET命令でデータ取得しようとしても、データが空のまま出力される。なぜだ！？と考えて、２日経ったとさ

~~前置きがすごく長くなりました~~ SQL Serverを使うとこういうトラブルに見舞われやすいなと体感で感じます。

原因

Startup.csに、以下の文面を追加してみた。
(静的クラスUserNLogに、PrintDebugと書くとデバッグ情報を出してくれるようにするとします)

Startup.cs

UserNLog.PrintDebug(System.Security.Principal.Windows.Identity.GetCurrent().Name);

こう書くと、ユーザログオン名を出力してくれるようになるみたいなので、さっそくやってみました。
すると、デバッグ時には

DEBUG DESKTOP-*******\myuser

という風に、例えばwindows認証のアカウントがmyuserだったら、コンピュータ名とアカウント名を同時に出力してくれていたのですが、IISで実行すると

DEBUG IIS APPPOOL/OPSimulator

と出ていました。

つまり、データベースにアクセスするときのユーザが違っていた。つまり、myuserには権限はあるけれども、APPPOOL/OPSimulatorにはデータベースにアクセスする権限がないというわけだ！

従って、APPPOOL/OPSimulatorにアクセス権を通せば、この課題はクリアになります。

解決策

いろいろありますがベターな方向性

　ここから先、色々やり方はあると思います。
　（詳しい内容は参考サイトを参照）

①　例えば、SQL Serverのインスタンス名が登録されていないのだから、単純に言えばIIS側の設定で、常にDESKTOP-******* /myuserになるようにログイン出来るようにすればよいじゃない、という案が浮かびます。一応これ、可能です。

　方法は単純で、IISの画面からアプリケーションプールを開き、開こうとしているアプリケーション(今回であればOPSimulator)を右クリックし、詳細設定を押します。そこから、IDをDESKTOP-******* /myuserとすれば、解決できたりします。

　ただし、IISが外部攻撃に遭った際に、DESKTOP-******* /myuser権限で実行できるものが実行できてしまいますので、業務アプリレベルでASPを使う用途でもアンチパターンとして避けるべきと考えます。

② 参考サイトのApproach 3のように、SQL Server 2016 (特にExpress)にはSQLEXPRESSという固有のグローバルインスタンスが存在するので、それを使うのもありです。 ~~個人的にはちょっと気持ち悪いけどね~~

③ 最後に、参考サイトのApproach 2のようなやり方。
　そもそも、今のままで実行できていないかというと、MSSQLEXPRESSも実は固有のデータベースになっていて、共有データベースに設定できないからなのです。
　
　従って、共有可能なインスタンスを自分で作って、それを共有可能に設定し、ASP .NET側からも、共有インスタンスとしてデータベースアクセスするように変えればよいのです。

さて、解決策

1. DBの作成＆共有インスタンスに設定

Visual Studio開発者用コマンドプロンプトを管理者権限で開いて、次のように入力します

DBの生成


C:\Windows\System32>sqllocaldb create OPSimulator

LocalDBインスタンス "OPSimulator"はバージョン13.1.4001.0で作成されました。

生成されたDBの確認

C:\Windows\System32>sqllocaldb info
MSSQLLocalDB
OPSimulator
ProjectsV13

生成したOPSimulatorを共有インスタンスに指定

C:\Windows\System32>sqllocaldb share OPSimulator IIS_DB

プライベートLocalDBインスタンス "OPSimulator"は共有名"IIS_DB"で共有します。

共有インスタンスになっているかを確認

C:\Windows\System32>sqllocaldb info OPSimulator

名前　OPSimulator
バージョン　
...
共有名 IIS_DB

"test_db"でインスタンスを作るとこんな感じになります

2. 生成したインスタンスをVisual Studioに紐づけ

Management Studioを開き、さっそく作成したOPSimulatorでログインしてみてください。

さっそく権限を確認してみると、publicと一部のログイン情報以外は権限が与えられていないことが分かります。ただし、所有者であるDESKTOP-******* /myuserは気にせずとも権限が自動で設定されます。

次に生成されたインスタンスに無理やり権限が入るように設定します。
Visual Studio 2019を開き、今データベースを使用としているソリューションを開きます。

その後に、「SQL Server Object Explorer」を開き、SQL Serverを右クリックして、SQL Serverを追加します。

接続(Connect)の画面が開かれたら、該当するDB（この場合OPSimulator)をクリックし、Connect(接続)をクリックします。

開いた後に、新たに追加されたDBインスタンスを開き、新たにクエリ文を追加します。
クエリ文は、追加されたDBを押して「New Query(クエリの追加)」で作業できます。
文章はこんな感じで打ちます。

権限登録

create login [IIS APPPOOL\OPSimulator] from windows;
exec sp_addsrvrolemember N'IIS APPPOOL\OPSimulator', sysadmin

IIS APPPOOL\OPSimulatorの部分ですが、これはIISのアプリケーション名によって適宜変更します。
実行すると、権限が割り振られますので、Management Studioを改めて開いてIIS APPPOOL\OPSimulator\に権限が追加されていることを確認します。

なお、この設定にするとsysadmin相当の権限が与えられますので、それを回避したい場合はManagement Studioで適宜変更します。

3. DBテーブルやスキーマの登録

まあ、ここはシステムによるところですね。
SQL文をべた書きにしている場合は、ここでは扱わないとして、

Code Firstでマイグレーションでシステム構築している場合は、visual studioのpackage manager consoleを使って、次のように入力します。

PackageConsoleManagerでのCodeFirstの実行

> update-database -connection 'server=(localdb)\OPSimulator;database=OpMeasureDbRuntime'
Build started
Build succeeded.
Done.

connectionに新たに作ったインスタンス名を置くのがポイントです。
OpMeasureDbRuntimeの部分は別にデータベースが作られていなくても`、自動的に好き勝手に作ることが出来ます。

4. appsetting.jsonの編集＆発行(publish)

appsettings.jsonを書き換えて、発行(publish)します。

appsettings.json

{
 "ConnectionStrings" : {
  "OpMeasureDb" : "Server=(localdb)\\.\\IIS_DB;Initial Catalog=OpMeasureDbRuntime;
      Integrated Security = true"
 }
}

発行後は、生成されたexeファイルを実行してください。
この場合、簡易的にIIS Expressが実行されますが、DBエラーが発生する場合は強制終了します。

5. IIS＆Webブラウザでの動作確認

IISですでにfilesystemでページを公開している場合は、IIS側では特別何かを行う必要がありません。

IIS自体の再起動は必要ですので、IISのアプリケーションプールで定義されたアプリケーションをもう一度起動すると良いでしょう。

6. スタートアップ動作確認

Windows 10が起動した段階では、DBインスタンスは起動しないので、SQL Serverのインスタンスは手動で実行する必要があります。
もっとも簡単なのは、スタートアップから右クリック、ファイル名を指定して実行で「shell:startup」と入力して、
そのフォルダの中に例えば、以下のようなバッチファイルを置くと良いでしょう。

opmeasuredb_startup.bat

sqllocaldb start .\IIS_DB

タイムアウト対策

(2021.8.20)更新

6.に書いたsqllocaldb startは、それが実行されてから、一定時間(デフォルト10分)ログインされない状態が続くと、エラー終了してしまうようです。

その対策はこちら

実質コピペしますが、コマンドプロンプトを開いて、こんな感じで実行します

C:\> sqlcmd -S np:\\.\pipe\LOCALDB#SH9D87FB\tsql\query

1> sp_configure 'show advanced options', 1;
2> GO

Configuration option 'show advanced options' changed from 1 to 1. 
Run the RECONFIGURE statement to install.

1> RECONFIGURE;
2> GO

1> sp_configure 'user instance timeout', 5;
2> GO

Configuration option 'user instance timeout' changed from 5 to 5. 
Run the RECONFIGURE statement to install.

1> RECONFIGURE;
2> GO

sp_configure 'user instance timeout', 5;のところで、時間を変えると、タイムアウト時間を変更できます。

私見

Windows 11に変わりIISも正直レガシーの部類に入ってくるので、今後どうなるかはちょっと分からないです。

まあ、ASP .NET MVC Core 6がLTSとしてアップデートされますので、他のCoreに追随し切れていないフレームワークと比べると、いきなりなくなることはなさそう。 ~~でもこういうややこしい使い方は少し改善されてほしいよねぇ~~

参考サイト

10年前の古い記事ですが、Microsoft SQL Server2016 & Visual Studio 2019でも動作確認が出来た。
Approach 2で対応しました。　

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up