SQL Server, Internet Information Server, and the ASP.NET engine all provide solid security models that work well together. To keep user data and applications safe, Microsoft also sets the default settings for each service to fairly low values.
The challenge most developers face is how to use SQL Server, IIS, and ASP.NET to set the appropriate level of trust between applications and data without leaving security holes that can be easily compromised by others. Because there are three types of services involved (SQL Server, IIS, and ASP.NET), there are three key steps to take to ensure the security of the solution. This section discusses one of the more common (and reliable) methods of setting adequate permissions and trust levels for Web applications.
Define DotNetKB custom IIS user account
The safest way to secure your Web application is to define a custom user with limited permissions, and then configure IIS to run as the custom user when executing your Web application. This is fairly easy to implement and ensures that every visitor to your Web application only has the permissions you want them to have.
The first step is to generate a new Windows user (called DotNetKB in this example), set a strong password for it, and then add it to the Windows Guest Group (Guest Windows Group). Also, make sure the Password never expires and User cannot change password checkboxes are selected. This will generate a limited-privileged user that you can use as an identity when running your Web application in IIS (see Figure 1).
Then, call Internet Information Server Administrator and select the Web application that hosts these pages. In this example, you can select the Web application (DotNetKB_WebSite) that hosts the test page generated earlier. Right-click the web application in the tree view and select Properties... from the context-sensitive menu. Then select Directory Security and click the Edit button in the Anonymous access and authentication control section of the dialog box. Finally, enter a custom username (DotNetKB), deselect the Allow IIS to control password checkbox, and enter a password for the custom user account. After completing all this work, click the OK button to save these changes to the IIS configuration database (see Figure 2).
[Cut-Page]
At this point, IIS will run under a custom account with limited permissions. Any visitor who visits your application's web pages will run as this custom user and will only have authentication permissions for that custom user.
Authorize the DotNetKB user account to access SQL Server
You then need to grant the custom user the appropriate permissions to access the database (DotNetKB). To do this, you can use Microsoft SQL Server Enterprise Manager or write a custom script to create such a user and grant it access to specific objects. This article describes how to do this using SQL Server Enterprise Manager.
Note: Although Visual Studio .NET 2003 has many powerful integrated features that are compatible with SQL Server, it does not allow easy management of users and user permissions from within Visual Studio .NET 2003. In large organizations and teams, these advanced tasks are typically performed by database administrators. Therefore, after starting SQL Server Enterprise Manager, you can follow the steps below to add a custom user (DotNetKB) to the database.
·In the tree view on the left, expand the node to display the DotNetKB database. On my computer, the tree view is structured as follows: Console Root SQL Server Group (LOCAL) (Windows NT) Databases DotNetKB.
·Then, right-click on the Users node under the database and select New Database User... (New Database User...). When the Database User Properties - New User dialog box appears, select (<New>) from the Login name drop-down box.
·When the SQL Server Login Properties - New Login dialog box appears, select the General tab and enter DotNetKB in the Name input box. Make sure the Windows Authentication radio button is selected and select the name of the computer where the custom user account is located from the Domain drop-down box. Then select DotNetKB from the Database drop-down box.
·Now, select the Databases tab, find the DotNetKB database in the list at the top of the dialog box and select it. Then, make sure the public role is selected in the list at the bottom of the dialog box. Finally, click the OK button at the bottom of the dialog box to save your changes.
Then, you need to add execute permissions to all stored procedures and custom functions in the DotNetKB database. To do this, you only need to grant permissions to the public role. You can grant permissions to DotNetKB users, which will make it easier for future logins (when these users gain access to DotNetKB) to execute stored procedures without the need to add new permissions for each user.
The following are the steps to grant execution permissions to stored procedures and functions in the DotNetKB database:
·Highlight the Users node under the DotNetKB database in the tree view to display a list of users for this database. Locate the DotNetKB user and double-click on it to open the Database Users Properties dialog box.
· With the public role highlighted (selected), click the Properties... button to open the Database Role Properties dialog box. Then click the Permissions... button to display a list of database objects and permission settings.
With the public role selected in the Database role drop-down list at the top of the dialog box, find all stored procedures and custom functions defined for this database (you may need to expand the dialog box to see the full names) and make sure Select the EXECUTE checkbox next to each item. You may find that some system objects have some other checkboxes checked, please do not change these options.
·Finally, after setting all EXECUTE permissions, click the OK button to save changes and close the dialog box. Click the OK button one after another until all dialog boxes are closed.
At this point, you have created a custom user for IIS and set the user's corresponding permissions in SQL Server. Now, you need to make a configuration change in the ASP.NET Web project to ensure that ASP.NET uses the same user account for all calls to SQL Server.
Set up your ASP.NET application to impersonate DotNetKB users
The final step in generating a solid and reliable configuration for an ASP.NET Web application running under IIS is to configure the ASP.NET Web application so that it can accept a Windows user ID from IIS and can be used to access other operating system resources. To do this, you only need to enter a line of code in the root web.config file.
The modified web.config file looks like this:
<configuration>
<system.web>
...other elements...
<identity impersonate="true"/> <!-- Assuming IIS user ID-->
...other elements...
</system.web>
</configuration>
Note that you just add the element and set the mock attribute to true. You do not have to enter a user account or password as this information will be provided by IIS. That is, even if others are able to read your configuration file, they cannot determine which identity credentials are used to execute your web application.
At this point, you have generated a custom user and set appropriate permissions for it to access SQL Server and IIS.
At this point, IIS will run under a custom account with limited permissions. Any visitor who visits your application's web pages will run as this custom user and will only have authentication permissions for that custom user.
Authorize the DotNetKB user account to access SQL Server
You then need to grant the custom user the appropriate permissions to access the database (DotNetKB). To do this, you can use Microsoft SQL Server Enterprise Manager or write a custom script to create such a user and grant it access to specific objects. This article describes how to do this using SQL Server Enterprise Manager.
Note: Although Visual Studio .NET 2003 has many powerful integrated features that are compatible with SQL Server, it does not allow easy management of users and user permissions from within Visual Studio .NET 2003. In large organizations and teams, these advanced tasks are typically performed by database administrators. Therefore, after starting SQL Server Enterprise Manager, you can follow the steps below to add a custom user (DotNetKB) to the database.
·In the tree view on the left, expand the node to display the DotNetKB database. On my computer, the tree view is structured as follows: Console Root SQL Server Group (LOCAL) (Windows NT) Databases DotNetKB.
·Then, right-click on the Users node under the database and select New Database User... (New Database User...). When the Database User Properties - New User dialog box appears, select (<New>) from the Login name drop-down box.
·When the SQL Server Login Properties - New Login dialog box appears, select the General tab and enter DotNetKB in the Name input box. Make sure the Windows Authentication radio button is selected and select the name of the computer where the custom user account is located from the Domain drop-down box. Then select DotNetKB from the Database drop-down box.
·Now, select the Databases tab, find the DotNetKB database in the list at the top of the dialog box and select it. Then, make sure the public role is selected in the list at the bottom of the dialog box. Finally, click the OK button at the bottom of the dialog box to save your changes.
Then, you need to add execute permissions to all stored procedures and custom functions in the DotNetKB database. To do this, you only need to grant permissions to the public role. You can grant permissions to DotNetKB users, which will make it easier for future logins (when these users gain access to DotNetKB) to execute stored procedures without the need to add new permissions for each user.
The following are the steps to grant execution permissions to stored procedures and functions in the DotNetKB database:
·Highlight the Users node under the DotNetKB database in the tree view to display a list of users for this database. Locate the DotNetKB user and double-click on it to open the Database Users Properties dialog box.
· With the public role highlighted (selected), click the Properties... button to open the Database Role Properties dialog box. Then click the Permissions... button to display a list of database objects and permission settings.
With the public role selected in the Database role drop-down list at the top of the dialog box, find all stored procedures and custom functions defined for this database (you may need to expand the dialog box to see the full names) and make sure Select the EXECUTE checkbox next to each item. You may find that some system objects have some other checkboxes checked, please do not change these options.
·Finally, after setting all EXECUTE permissions, click the OK button to save changes and close the dialog box. Click the OK button one after another until all dialog boxes are closed.
At this point, you have created a custom user for IIS and set the user's corresponding permissions in SQL Server. Now, you need to make a configuration change in the ASP.NET Web project to ensure that ASP.NET uses the same user account for all calls to SQL Server.
Set up your ASP.NET application to impersonate DotNetKB users
The final step in generating a solid and reliable configuration for an ASP.NET Web application running under IIS is to configure the ASP.NET Web application so that it can accept a Windows user ID from IIS and can be used to access other operating system resources. To do this, you only need to enter a line of code in the root web.config file.
The modified web.config file looks like this:
<configuration>
<system.web>
...other elements...
<identity impersonate="true"/> <!-- Assuming IIS user ID-->
...other elements...
</system.web>
</configuration>
Note that you just add the element and set the mock attribute to true. You do not have to enter a user account or password as this information will be provided by IIS. That is, even if others are able to read your configuration file, they cannot determine which identity credentials are used to execute your web application.
At this point, you have generated a custom user and set appropriate permissions for it to access SQL Server and IIS.