HOW TO: Configure SQL Server Security for .NET Applications (815154)

SUMMARY

This article describes how to configure the SQL Server for .NET applications. By default, the SQL Server denies access to user accounts that have not explicitly been granted access to a database, a table, or a view. By default, ASP.NET applications run in the context of the ASPNET user account. Unless you permit access to the ASPNET user account, an ASP.NET application cannot read and cannot update data in an SQL Server database. This article describes the process that you can use to permit an ASP.NET application to have permissions to an SQL Server 2000 database.

Note You must give the ASPNET user account only minimal permissions to run. This limits the potential damage that may result to an ASP.NET application that is compromised by a malicious attacker.

back to the top

Configure the SQL Server

To permit an ASP.NET application that executes in the context of the ASPNET account to access an SQL Server database, follow these steps:

1. On the taskbar, click start.
2. Point to Programs and then point to Microsoft SQL Server.
3. Double-click Enterprise Manager.
4. Expand Microsoft SQL Servers and then expand the SQL Server group that contains your server.
5. Expand your server branch and then expand Security.
6. Right-click Logins and then select New Login to open the SQL Server Login Properties-New Login dialog box.
7. Click the General tab. In the name field, enter the name of the ASP.NET user.
   
   By default, this is a local account with the name ASPNET.
8. Click the Database Access tab.
9. Under Specify Which Databases Can Be Accessed By This Login, select the databases that are used by the ASP.NET application.
   
   You generally do not have to permit access to the Model database, the Master database, the Msdb database, or the Tempdb database.
10. For each database that the account requires access to, verify that the Public role in the Permit In Database Role list is selected.
11. Click OK to return to Enterprise Manager.
12. Expand the Databases branch, and then expand the branch for the database that your ASP.NET application requires access to. Click to select Users.
13. In the right pane, right-click the ASPNET user account and then click Properties.
    
    The Database User Properties dialog box appears.
14. Click Permissions.
    
    A new dialog box appears. This dialog box shows the permissions for the ASPNET user account for all objects in the database. Scroll through the list and then select the check boxes that are associated with the tables and the views that the application requires access to. For tables and views that the application must read, but not write to, select only the SELECT column. For tables and views that must be updated, select the SELECT, the UPDATE, the INSERT, and the DELETE check boxes as appropriate.
15. After you grant all the required permissions, click OK two times to return to Enterprise Manager.
16. Close Enterprise Manager.

back to the top