Configuring an MVC 4/IIS app to access SQL Server

21 Aug 2012

Summary:
In this post I review the steps required to configure an MVC 4 web app running on the full version of IIS (not IIS Express) to be able to access SQL Server 2012.

Introduction

It's something that you see time after time: a web app that was accessing the database just fine in the development environment starts throwing security exceptions when you deploy to another environment. I encountered this the other day when I moved my MVC 4 + LocalDb + IIS Express app to run on full IIS and SQL Express 2012.

It's tempting to quickly resolve things by giving every likely account full permissions to do everything, thus postponing the need to give the issue any real thought! Sooner or later though, this issue will re-surface. So it's better to have a good grasp on what's actually happening while things are still in development.

Changing from IIS Express + LocalDb to IIS + SQL Express

By default, Visual Studio 2012 configures MVC apps to run on IIS Express (the dev-only, lightweight web server) and use LocalDb (an embedded database that requires no management, starts/stops as required, and uses the AttachDbFileName property to point to a database file that's normally in a folder in your VS project).

You can easily change this default arrangement from the Project | project name Properties | (Web tab) window. Simply uncheck Use IIS Express:

At this point, Visual Studio will prompt you to use SQL Express, instead of LocalDb, with the full version of IIS:

Click Yes. Visual Studio will now comment-out your previous default connection string and replace it with something similar to the following:

  <connectionStrings>
	<add name="DefaultConnection" 
	  connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\dbname.mdf;Initial Catalog=dbname;
		Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" />
  </connectionStrings>

The database .mdf file will normally be located in your App_Data folder. To verify this, make sure the Show All Files button is selected in Solution Explorer, then look in App_Data:

Assuming that you alread have SQL Express installed (if not, download it from here), go ahead and run the web app - more than likely it won't work. If you debug the statements that interact with the database, you should see an exception is thrown. For example, if you're using the Microsoft Entity Framework (MEF), the inner exception shows the key bit of info: a login for IIS APPPOOL\\DefaultAppPool failed:

  InnerException {"Cannot open database \"rarcher.azurewebsites.net.Models.PostDbRepository\" requested by the login. 
	The login failed.\r\nLogin failed for user 'IIS APPPOOL\\DefaultAppPool'."}
	System.Exception {System.Data.SqlClient.SqlException}

This "login failure" tells us that the ASP.NET worker process (w3wp.exe) doesn't have login rights on the database. The worker process will be running using the Windows account specified by the IIS App Pool being used to run your web app. The default app pool is named DefaultAppPool, and as the name suggets, it's the default app pool for all IIS web sites. You can see this for yourself if you open IIS Manager (inetmgr from the Run box), and then select Application Pools, DefaultAppPool. The Identity field shows the account used by the default app pool:

If you now select your web site, then view Advanced properties, you'll see that it's configured to use the default app pool:

Using Task Manager you can view information on the ASP.NET worker process - here we can clearly see it's using DefaultAppPool:

Resolving SQL Login Issues

To resolve the login issue, you have a number of choices. The following list shows three options, there are more, but these are the most commonly used. Also, apart from option 1., either of the other two options may be used securely, although I personally prefer option 3:

  1. Bad: Change the Identity used by the default app pool to be the administrator account you used when installing SQL Server (which will, therefore, have a SQL login using Windows authentication)

    This is a very bad idea, but commonly done. I've seen many, many posts on the web (including on Microsoft forums) advocating this approach. Although it seems like a good quick-fix, in production this would be a ticking time-bomb. Anybody attempting to hack the database (e.g. through SQL injection) would gain unlimited permissions on the database
  2. Good: Change the connection string in web.config to use SQL authentication, and use an existing SQL login (but NOT sa!)

    This is better, but you then have the issue of storing the login credentials in web.config, which is possibly a security weak-point. I'm always uneasy when I see Visual Studio projects with database passwords in plain view in web.config, and while there are techniques to store passwords in encrypted form in the registry, this itself can cause permissions issues (because the app pool must have permission to read the registry, or you have to temporarily impersonate another account with the necessary permissions). The same cautionary notes related to using a Windows admin account apply equally to using the sa (system administrator) SQL login
  3. Best: Create a new app pool, using the built-in, limited-permission account ApplicationPoolIdentity (or, you could create a custom, low-permission Windows account), then give the new app pool a SQL login using Windows authentication. You should make sure the app pool login only has sufficient SQL permissons, and no more

Creating an New App Pool

To create an new app pool, select Application Pools in IIS, then click Add Application Pool. Give your app pool a name:

Select the new app pool and click Advanced Settings. Make sure the Identity field is configured to use either the built-in ApplicationPoolIdentity account, or a custom (non-admin) Windows account.

Configure your Web App's App Pool

In IIS, select your web site, click Advanced Settings, then set the Application Pool field to your new app pool:

Create a SQL Login

Open SQL Server Management Studio. Expand the Security | Logins node. Right-click Logins and select New Login. On the General page, create a new Windows Authentication login by typing (you can't search for it as it's not a Windows account) the full name of the app pool (IIS APPPOOL\app_pool_name):

On the Server Roles page, give the login a public role:

On the User Mapping page, select your web app's database, then grant the login the minimum set of roles it requires to operate:

You should now find your web app has (secure) access to the required database.

Conclusion

Configuring an MVC web app app running on IIS to be able to access SQL Server is pretty straightforward. We've seen that what you DON'T want to do is to simply give the ASP.NET worker process elevated permissions, which can present a real security threat. Creating an IIS App Pool with a minimal set of permissions, and running the web app using that account is a much better approach. All that's required then is to give that app pool account a login on SQL Server, and give it permissions to work with the required database.