Web Applications That Create Their Own Databases
By Sergey Nosov
April 23, 2014
A great deal of Web applications rely on some kind of data storage. Some applications store data directly in the file system, others take advantage of a relational database management system. The database system could be running on a separate sever, or in the case of a single fully integrated web server computer, locally.
Many of the web applications are capable of creating their own databases on first start or as a part of initial configuration. In this article we will talk about such applications that connect to a local Microsoft SQL Server using Integrated Security or Windows Authentication mode.
If you launch a web application and see the following error, then this article is definitely for you.
Server Error in '/' Application.
----------------------------------------------------------------------
CREATE DATABASE permission denied in database 'master'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: CREATE DATABASE permission denied in database 'master'.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[SqlException (0x80131904): CREATE DATABASE permission denied in database 'master'.]
----------------------------------------------------------------------
Before Service Pack 2 of Windows Server 2008, web applications, by default, run using the “NT AUTHORITY\Network Service” account identity. In more recent versions of the Windows Server operating system this behavior changed.
When you create a web site using Internet Information Services (IIS) Manager, an application pool with the name matching the name of the web-site is created. You can create additional application pools for different applications within a web-site, or share pools among applications.
By default, the application pool Identity property, in the Process Model section, is set to ApplicationPoolIdentity. This makes the web applications in the pool run under a unique account automatically created by the operating system just for this pool. All these accounts have a form of “IIS APPPOOL\<Pool Name>”, where <Pool Name> is the name of your pool.
For example, if your pool name is “MyWebApp” then the automatically generated virtual account, web applications in this pool will run under, is “IIS APPPOOL\MyWebApp”.
It is worth noting that the IIS Application Pool Identity accounts, in Windows Server 2008 R2, will not properly show when using the “Find Now” feature of the Select User or Group dialog. So, whenever you need to refer to one of these accounts, just type it in directly.
So, how can we let a web application running with Application Pool Identity create its own MS SQL Server databases? The following are the steps.
- Launch the Microsoft SQL Server Management Studio, and connect to the local database server.
- Select “New Query” from the tool bar, place the following query in the new SQL box (replace “MyWebApp” with the name of the actual IIS Application Pool used by your web application), and click the “Execute” button or F5 key.
USE [master]
GO
CREATE LOGIN [IIS APPPOOL\MyWebApp] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
use [master]
GO
GRANT CREATE ANY DATABASE TO [IIS APPPOOL\MyWebApp]
GO
This should be it, the web applications running in the, in our case, “MyWebApp” IIS application pool will have the proper permissions to create new databases.
This short article is meant to explain what could stand in a way of web applications trying to create their own databases. In production deployments please carefully consider security implications of any configuration changes that you are making.
Good luck.