ASP.NET 2.0 Security Guidelines - Data Access

From Guidance Share

Jump to: navigation, search

- J.D. Meier, Alex Mackman, Blaine Wastell, Prashant Bansode, Andy Wigley, Kishore Gopalan


Contents

Encrypt your connection strings

In ASP.NET version 1.1, you could encrypt connection strings by using DPAPI encryption, although you had to write managed code to wrap the calls to DPAPI. This approach also presented problems in Web farms because of machine affinity. In ASP.NET version 2.0, you can use a protected configuration provider, such as DPAPI or RSA, which is easier to use in Web farms. You do not have to write code because you use the Aspnet_regiis.exe utility.

In ASP.NET version 2.0 applications, place your database connection strings inside the <connectionStrings> element of the Web.config file and then encrypt that element by using the Aspnet_regiis utility. In a Web farm, use the RSA-protected configuration provider because RSA keys can be exported and imported across servers.

For more information, see How To: Encrypt Configuration Sections in ASP.NET 2.0 Using DPAPI and How To: Encrypt Configuration Sections in ASP.NET 2.0 Using RSA.

Use Least-Privileged Accounts for Database Access

Your application should connect to the database by using a least-privileged account. This limits the damage that can be done in the event of a SQL injection attack or in the event of an attacker obtaining your account's credentials. With Windows authentication, use a least-privileged account with limited operating system permissions, and limited ability to access Windows resources. Regardless of authentication mechanism, restrict the account's permissions in the database.

Use the following pattern to limit permissions in the database:

  1. Create a SQL Server login for the account.
  2. Map the login to a database user in the required database.
  3. Place the database user in a database role.
  4. Grant the database role limited permissions to only those stored procedures or tables that your application needs to access.

Ideally, provide no direct table access and limit access to selected stored procedures only. If you must grant table access, grant the minimum access that the application requires. For example, do not grant update access if read access is sufficient.

By using a database role, you avoid granting permissions directly to the database user. This isolates you from potential changes to the database user name. For example, if you change the database user name, you can simply add the new user to the database role and remove the existing one.

Use Windows Authentication Where Possible

Prefer Windows authentication when connecting to SQL Server or other databases that support it. Windows authentication offers a number of security advantages in comparison to SQL authentication:

  • Accounts are centralized and managed by your Active Directory or local authority store.
  • Strong password policies can be controlled and enforced by your domain or local security policy.
  • Passwords are not transmitted over the network.
  • User IDs and passwords are not specified in database connection strings.

For more information, see How To: Connect to SQL Server Using Windows Authentication in ASP.NET 2.0.


If You Use Windows Authentication, Use a Trusted Service Account

If you use Windows authentication, use a trusted service account to access the database when possible. This is usually your application's process account. By using a single trusted service account, your application benefits from connection pooling; this provides greater scalability. Also, account administration and authorization within the database is simplified. If you need per-user authorization in the database or need to use operating system auditing to track the activity of individual users, you need to use impersonation and delegation and access the database using the caller's identity. This approach has limited scalability because it prevents the efficient use of connection pooling.


If You Cannot Use a Domain Account, Consider Mirrored Accounts

If you cannot use domain accounts because of domain trust or firewall restrictions, consider using mirrored service accounts instead. With this approach, you still use Windows authentication, but you create two local accounts with the same name and password on the Web server and database server. You configure your application to run using the local account created on the Web server and create a SQL login for the local account on the database server. With this approach, you must ensure that the passwords of the two accounts remain in synchronization.

For more information, see How To: Connect to SQL Server Using SQL Authentication in ASP.NET 2.0.


When Using SQL Authentication, Use Strong Passwords

If you use SQL Server authentication, make sure you use a least-privileged account with a strong password to prevent an attacker guessing your account's password. A strong password should be at least seven characters in length and contain a combination of alphabetic, numeric, and special characters.

Avoid using blank passwords and the sa account as shown in the following connection string.

SqlConnectionString = "Server=YourServer\Instance; Database=YourDatabase; uid=sa; pwd=;"
 

Use least-privileged accounts with a strong password, such as the following.

SqlConnectionString= "Server=YourServer\Instance;
                     Database=YourDatabase;
                     uid=YourStrongAccount; 
                     pwd=YourStrongP@ssw0rd;"
 

When Using SQL Authentication, Protect Credentials Over the Network

If your application is not located in a physically secure isolated data center, you should use Internet Protocol Security (IPSec) or SSL to create an encrypted communication channel between the Web server and database server if your use SQL authentication. Failure to do this means that credentials can be easily captured with a network monitor. When you connect to SQL Server with SQL authentication, the credentials are not encrypted prior to transmission across the network.

Use SSL when you need granular channel protection for a particular application instead of for all applications and services running on a computer. If you want to protect all the IP traffic between Web and database server, use IPSec. You can also use IPSec to restrict which computers can communicate with one another. For example, you can help protect a database server by establishing a policy that permits requests only from a trusted client computer, such as an application or Web server. You can also restrict communication to specific IP protocols and TCP/UDP ports.

When Using SQL Authentication, Protect Credentials in Configuration Files

To protect credentials in configuration files, encrypt them. Place your database connection strings inside the <connectionStrings> element of the Web.config file and then encrypt that element by using the Aspnet_regiis utility. You can use DPAPI or RSA encryption. Use RSA in Web farms because you can easily export and import RSA keys across servers. Protecting connection strings is particularly important for connection strings that use SQL authentication because they contain clear text user IDs and passwords.

Note You should also encrypt connection strings if you use Windows authentication. Although this form of connection string does not contain credentials, you should aim to keep server and database names private. For more information, see How To: Encrypt Configuration Sections in ASP.NET 2.0 Using DPAPI and How To: Encrypt Configuration Sections in ASP.NET 2.0 Using RSA.


Validate Untrusted Input Passed to Your Data Access Methods

If your data access methods receive input parameters from outside the trust boundary of your data access code, make sure you validate them for type, length, format, and range. You can use regular expressions for text input and perform type and range checks on numeric data. If you do not do this, your data access code is potentially susceptible to SQL injection.

Only omit input parameter validation in your data access methods if you know for certain that data can only be supplied by trusted code, such as your application's business logic, which you know has thoroughly validated the data passed to it.

Note Avoid storing encoded data; instead, encode the data as close as possible to the output. When Constructing SQL Queries, Use Type Safe SQL Parameters Use type safe parameters when constructing SQL queries to avoid possible SQL injection attacks that can occur with unfiltered input. You can use type safe parameters with stored procedures and with dynamic SQL statements. Parameters are treated as literal values by the database and not as executable code. Parameters are also checked for type and length.

The following code shows how to use type safe parameters with the SqlParameterCollection when calling a stored procedure.


using System.Data;
using System.Data.SqlClient;
using (SqlConnection connection = new SqlConnection(connectionString))
{
 DataSet userDataset = new DataSet();
 SqlDataAdapter myCommand = new SqlDataAdapter(LoginStoredProcedure", connection);
 myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
 myCommand.SelectCommand.Parameters.Add("@au_id", SqlDbType.VarChar, 11);
 myCommand.SelectCommand.Parameters["@au_id"].Value = SSN.Text;
 myCommand.Fill(userDataset);
}
 

In the preceding code example, the input value cannot be longer than 11 characters. If the data does not conform to the type or length defined by the parameter, the SqlParameter class throws an exception. For more information about preventing SQL injection, see How To: Protect from SQL Injection in ASP.NET.


Avoid Dynamic Queries That Accept User Input

Avoid constructing SQL queries in code that include user input; instead, prefer parameterized store procedures that use type safe SQL parameters. If you construct queries dynamically using user input, your code is susceptible to SQL injection. For example, avoid the following style of code.


// Use dynamic SQL
SqlDataAdapter myCommand = new SqlDataAdapter(
        "SELECT au_lname, au_fname FROM authors WHERE au_id = '" + 
        SSN.Text + "'", myConnection);
 

If a malicious user supplies "' ; DROP DATABASE pubs --'" for the SSN.Text field, the code inserts the user's malicious input and generates the following query.


SELECT au_lname, au_fname FROM authors WHERE au_id = ; DROP DATABASE pubs --'
 

The ; (semicolon) character tells SQL that this is the end of the current statement, which is then followed by the following malicious SQL code, which in this example drops the authors table.

Personal tools