ASP.NET 2.0 Security Practices - Data Access

From Guidance Share

Jump to: navigation, search


How to protect database connection strings

Place connection strings inside the <connectionStrings> setting in Web.config, and then encrypt the <connectionStrings> configuration section by using one of the protected configuration providers (RSA or DPAPI). For more information about doing this and choosing between RSA and DPAPI, see How to encrypt sensitive data in Machine.config and Web.config in the Configuration topic.

How to access a database from ASP.NET

Use Windows authentication where possible and use a least privileged service identity while connecting to SQL Server. Usually, this will be your least privileged application's process account. By using a service account, you benefit from connection pooling. If you need per user authorization in the database, you can use impersonation (and delegation) and access the database with the original caller's account, but this will prevent efficient connection pooling.

How to use Windows authentication to connect to SQL Server

To use Windows authentication, configure SQL Server appropriately and then use a connection string that contains either "Trusted_Connection=Yes", or "Integrated Security=SSPI" as shown in the following code. The two strings are equivalent and both result in Windows authentication.

"server=MySQL; Integrated Security=SSPI; database=Northwind"
"server=MySQL; Trusted_Connection=Yes; database=Northwind"

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

How to access SQL Server by using SQL authentication

If you cannot use Windows authentication to SQL Server, you must use SQL authentication.

To use SQL authentication:

  • Use a least-privileged user ID to connect to SQL.
  • Use a strong password for the SQL user account.
  • Secure the channel between the Web server and database server because credentials are passed in an unencrypted format. For example, use SSL or IPSec.
  • Secure the SQL connection string (which contains plaintext credentials). For more information, see How to encrypt sensitive data in Machine.config and Web.config in the Configuration topic.

If you connect to a SQL Server database using credentials (user name and password), your connection string looks like the following.

SqlConnectionString = "Server=YourServer\Instance;

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

How to use the Network Service account to connect to SQL Server

The Network Service account has network credentials, so it can be used to access resources such as a database server in the same domain or in a domain with an appropriate trust relationship.

Note If you grant access to the Network Service account, any application on the same Web server that runs using that identity has access. For individual authorization and application isolation, use distinct identities. For more information, see How to create a service account for ASP.NET in the Configuration topic.

To grant access to SQL Server for the network service account:

  1. Create a SQL login for the Network Service account. The name appears as domainName\<WebServerMachineName>$ if your database is on a separate server. You can use Enterprise Manager or run the following SQL statement to create the SQL Login:
    exec sp_grantlogin [domainName\<WebServerMachineName>$]
  2. Create a database user in the required database and map the login to the database user. Or you can run the following SQL statement:
    exec sp_grantdbaccess [domainName\<WebServerMachineName>$]
  3. Place the database user in a database role. This enables you to assign permissions to roles instead of individual users, which helps should the user account change.
  4. Grant permissions to the role. Ideally, just grant execute permissions to selected stored procedures and provide no direct table access.

Within the client application, use a connection string that contains either "Trusted_Connection=Yes" or "Integrated Security=SSPI". The two strings are equivalent and both result in Windows authentication (assuming that your SQL Server is configured for Windows authentication). For more information, see How To: Use the Network Service Account to Access Resources in ASP.NET.

How to prevent SQL injection

Validate input and use parameterized stored procedures for data access. The use of parameters (for example, SqlParameterCollection) ensures that input values are checked for type and length and values outside the range throws an exception. Parameters are also treated as safe literal values and not executable code within the database. The following code shows how to use SqlParameterCollection when calling a stored procedure called LoginStoredProcedure which accepts @au_id of type varchar(11) as a parameter.

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;


Avoid passing SQL queries to be executed as a parameter to a stored procedure. Instead pass query parameters only.

Use structured exception handling to catch errors when accessing the database and prevent them from propagating back to the client. A detailed error message may reveal valuable information, such as the connection string, SQL server name, or table and database naming conventions that attackers can use for more precise attacks.

As an additional precaution, use a least privileged account to access the database, so that even if your application is compromised the impact will be minimized. For more information, see How To: Protect From SQL Injection in ASP.NET.

Personal tools