Create ASP.net data storage layer (5)

Asp.net| Create | Security issues for data IIS, asp.net, and SQL Server

SQL Server, Internet Information Server, and the ASP.net engine all provide a solid and reliable security model that works well together. To ensure user data and application security, Microsoft also sets a fairly low value for the default settings for each service. The challenge for most developers is to use SQL Server, IIS, and asp.net to set the appropriate level of trust between applications and data without leaving a security vulnerability that others can easily hack into. Because of the three types of services involved (SQL Server, IIS, and ASP.) NET), three key steps are required to ensure the security of the solution. This section discusses a more common (and reliable) way to set sufficient permissions and trust levels for WEB applications.

Note: For the big topic of security and WEB solutions, this series of articles makes it difficult to start a more thorough discussion. To better understand this issue and possible solutions, see the Security asp.net application creation pattern and Practice series articles: Authentication, authorization, and secure communication.
Defines the DotNetKB custom IIS user account.

The safest way to secure a Web application is to define a custom user with limited permissions and then configure IIS to run as a custom user when executing your Web application. This is fairly easy to implement, ensuring that every visitor who accesses your WEB application has only the permissions you want them to have.

The first step is to build a new Windows user (called DotNetKB in this case), set an enhanced password for it, and then add it to the Windows Guest Group (guest Windows group). Also, make sure that the Password never expires (password never expires) and user cannot change Password (user cannot alter password) check box are checked. This generates a user with limited permissions that you can use as an identity when you run your WEB application in IIS (see Figure 7).


Figure 7: User with limited permissions generated

Then, call the Internet Information Server administrator and select the Web application that hosts the pages. In this example, you can choose to host the Web application (Dotnetkb_website) that hosts the test pages that were generated in the previous article. Right-click on the Web application in the tree view and select Properties from the context-related menu ... (Properties ... ）。 Then select Directory Security and click the dialog box to Anonymous the Edit button in the Access and authentication control (anonymous access and validation controls) section. Finally, enter the custom user name (DotNetKB), deselect the Allow IIS to control password (allow IIS for password) check box, and enter the password for the custom user account. After you complete all of these tasks, click the OK button to save the changes to the IIS metabase (see Figure 8).


Figure 8:authentication Methods (Validation method) dialog box

At this point, IIS will run under a custom account with limited permissions. When any visitor accesses the Web page of your application, it will run as this custom user, with only the authentication permissions of that custom user.

Authorize DotNetKB user accounts to access SQL Server

Then, you need to give 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 one such user and grant them permission to access a particular object. This article describes how to use SQL Server Enterprise Manager to complete this operation. You can also see a script example from the following article.

Note: Although Visual Studio. NET 2003 has many powerful integration features that are compatible with SQL Server, it does not allow easy administration of user and user rights from Visual Studio. NET 2003. In large organizations and teams, these advanced tasks are typically done by the database administrator.

Therefore, after you start SQL Server Enterprise Manager, you can follow these steps to add a custom user (DotNetKB) to the database (see Figure 9):

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, click the right mouse button on the users node under the database and select New database User ... (New database user ...) ）。 When the database user Properties-new User (properties-New User) dialog box is displayed, select <new> (< new >) from the Login name Drop-down box.

When you display the SQL Server login properties-new login (SQL Server login Properties-New Login) dialog box, select the General tab and enter DotNetKB in the name input box. Make sure the Windows Authentication (Windows Authentication) radio button is selected and select the name of the computer that contains the custom user account from the Domain drop-down box. Then select DotNetKB from the Database drop-down box.
Now, select the Databases (Database) tab, locate the DotNetKB database in the list at the top of the dialog box, and select it. Then, make sure that 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.


Figure 9: Adding custom users to the database

You then need to add execution permissions for all stored procedures and custom functions in the DotNetKB database. To do this, you only need to grant permissions to the public (common) role. You can grant permissions to DotNetKB users, which makes it easier for future logins (when they gain access to DotNetKB) to execute stored procedures without adding new permissions for each user.

The following steps are granted to execute permissions for stored procedures and functions in the DotNetKB database:

Highlight the Users node under the DotNetKB database in the tree view to display the user list for this database. Locate the DotNetKB user and double-click on it to open the Database Users Properties dialog box.
When highlighting (selecting) public (Common) roles, click Properties ... (Properties ... button to open the Database Role Properties dialog box. Then click Permissions ... (Permission ... button to display a list of database objects and permission settings.

After you select the public (common) role in the top database role Drop-down list in the dialog box, locate all stored procedures and custom functions defined for this database (you may need to expand the dialog box to see the full name), and make sure that the Execute check box. You may find that some other check boxes for some system objects are also selected, and do not change these options.
Finally, after you set all Execute permissions, click the OK button to save the changes and close the dialog box. Click the OK button in turn until all the dialog boxes are closed.

At this point, you have created a custom user for IIS and set the appropriate permissions for that user in SQL Server. Now you need to make a configuration change in the ASP.net Web project to make sure that ASP.net uses the same user account to execute all calls to SQL Server.

Set up your ASP.net application to simulate DotNetKB users

The final step in generating a solid, reliable configuration for asp.net Web applications running under IIS is to configure the ASP.net Web application to accept the Windows user identity from IIS and to access other operating system resources. To do this, you simply enter a line of code in the Web.config root file.

Note: Although we haven't really developed a asp.net Web application for hosting our pages at the moment, you can use this information to validate the functionality of the data access layer in the next section of the build test page.

The modified Web.config file looks like this:

<configuration>
<system.web>
... Other elements ...
<identity impersonate= "true"/><!--assume that the IIS user identity-->
... Other elements ...
</system.web>
</configuration>
Note that you simply add the <identity> element and set the impersonation attribute to True (true). You do not have to enter a user account or password because this information will be provided by IIS. That is, even if others can read your profile, they cannot determine which identity credentials are used to execute your WEB application.

At this point, you have generated a custom user and set up appropriate permissions for it to access SQL Server and IIS. Now, let's create some test pages to make sure it works. From here you can see that everything is OK.