HOW TO: Configure SQL Server Security for .NET Applications


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
  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.

    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


For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
315736 HOW TO: Secure an ASP.NET Application by Using Windows Security
315588 HOW TO: Secure an ASP.NET Application Using Client-Side Certificates
818014 HOW TO: Secure Applications That Are Built on the .NET Framework
back to the top