Security Guidelines: ADO.NET 2.0

  • Article

 

Retired Content

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

patterns & practices Developer Center

patterns & practices Developer Center

J.D. Meier, Alex Mackman, Blaine Wastell, Prashant Bansode, Chaitanya Bijwe

Microsoft Corporation

October 2005

Applies To

  • ADO.NET version 2.0

Summary

This module presents a set of consolidated ADO.NET 2.0 data access security guidelines. The guidelines are organized into categories where mistakes are most often made, such as input/data validation, SQL injection, configuration and connection strings, authentication, and others. Each guideline explains what you should do and why you should do it, and then explains how you can implement the guideline. Where necessary, the guidelines refer you to companion How To modules, which provide detailed step-by-step instructions for more complex implementation procedures. This module has a corresponding checklist that summarizes the guidelines. For the checklist, see "Security Checklist: ADO.NET 2.0." It also includes an index of security guidelines for ADO.NET 2.0 code.

Contents

Objectives
How To Use This Module
What's New in 2.0
Index of Guidelines
Input / Data Validation
SQL Injection
Configuration and Connection Strings
Authentication
Authorization
Exception Management
Sensitive Data
Code Access Security Considerations
Deployment Considerations
Companion Guidance
Additional Resources

Objectives

In this module, you will learn to do the following:

  • Learn how and where to perform input validation.
  • Protect your application from SQL injection attacks.
  • Manage database connection strings in configuration files.
  • Authenticate callers and applications with the database.
  • Authorize application access to the database.
  • Prevent leaking sensitive data in exceptions.
  • Protect sensitive data in storage.
  • Learn how to grant appropriate code access security permissions.
  • Protect network communication with your database.

How to Use This Module

To get the most from this module, you should:

  • Use the index to browse the guidelines. Use the index to scan the guidelines and to quickly jump to a specific guideline.
  • Learn the guidelines. Browse the guidelines to learn what to do, why, and how.
  • Use the companion How To modules. Refer to the associated How To modules for more detailed step-by-step implementation details. The How Tos describe how to make use of the more complex solution elements required to implement a guideline.
  • Use the companion checklist. Use the associated checklist as a quick reference to help you learn and implement the guidelines.

What's New in 2.0

.NET Framework version 2.0 provides a number of enhanced and new features related to data access. The main features are:

  • Partial trust support. In ADO.NET 1.1, you could only use the SQL Server .NET data provider from partial trust applications because this provider was the only one that did not demand full trust. In ADO.NET 2.0, the Oracle .NET data provider, the OLE DB .NET data provider, and the ODBC .NET data provider no longer demand full trust. This allows you to access SQL Server and other databases from partial trust applications. Note however that medium trust ASP.NET policy only grants the SqlClientPermission required by the SQL Server .NET data provider. To use the other providers from a partial trust Web application, you need to customize policy and grant the appropriate permission: for example, OleDbPermission, OraclePermission, or OdbcPermission.
  • Improved connection string encryption. You can now use protected configuration to encrypt sections of your Machine.config and Web.config files by using either DPAPI or RSA encryption. This is particularly useful for encrypting database connection strings.
  • New connection string settings class. The System.Configuration namespace provides classes for working with information stored in configuration files. You can use the new ConnectionStringSettings class to retrieve connection strings from configuration files. The ConnectionString property contains the connection string value, and the Name property contains the name of the connection string specified in the <connectionStrings> section.
  • New connection string builder classes. The new DbConnectionStringBuilder class provides the base class from which strongly typed connection string builders derive. They allow you to programmatically create syntactically correct connection strings, and to parse and rebuild existing connection strings. The following built-in data providers supply strongly typed classes that inherit from System.Data.Common.DbConnectionStringBuilder:
    • System.Data.SqlClient.SqlConnectionStringBuilder
    • System.Data.OracleClient.OracleConnectionStringBuilder
    • System.Data.Odbc.OdbcConnectionStringBuilder
    • System.Data.OleDb.OleDbConnectionStringBuilder
  • New abstract base class for database exceptions. The new System.Data.Common.DbException is the base class for all exceptions thrown on behalf of a data source. This abstract class is used as the base class for provider-specific exception class implementations. These include:
    • System.Data.SqlClient.SqlException
    • System.Data.OleDb.OleDbException
    • System.Data.OracleClient.OracleException
    • System.Data.Odbc.OdbcException
  • Changing passwords in SQL Server 2005. You can use the new SqlConnection.ChangePassword method to change the SQL Server password for the user indicated in the connection string to the supplied new password. This allows .NET Framework applications to change the password of a user account without requiring administrator intervention on Microsoft Windows Server™ 2003 or later.
  • New managed wrapper for DPAPI. In .NET Framework version 1.1, you had to use P/Invoke to access the Win32 Data Protection API (DPAPI). .NET Framework version 2.0 provides a set of managed classes to access DPAPI. Code requires the DataProtectionPermission to be able to use DPAPI.

Index of Guidelines

Input / Data Validation

  • Use regular expressions to validate input by comparing against expected patterns.
  • If you use ASP.NET, use ASP.NET validator controls.
  • Do not rely on ASP.NET request validation.
  • Validate untrusted input passed to data access methods.

SQL Injection

  • Constrain and sanitize input data.
  • Use type-safe SQL parameters for data access.
  • Avoid dynamic queries that accept untrusted input.
  • With dynamic SQL, use character escaping to handle special input characters.
  • Use an account that has restricted permissions in the database.

Configuration and Connection Strings

  • Avoid credentials in connection strings.
  • Store encrypted connection strings in configuration files.
  • Do not use Persist Security Info="True" or "Yes".
  • Avoid connection strings constructed with user input.
  • Avoid Universal Data Link (UDL) files where possible.

Authentication

  • If possible, use Windows authentication.
  • If you use SQL authentication, use strong passwords.
  • If you use SQL authentication, protect credentials over the network.
  • If you use SQL authentication, protect credentials in configuration files.
  • Consider which identity to use to connect to the database.

Authorization

  • Restrict unauthorized callers.
  • Restrict unauthorized code.
  • Restrict application access to the database.

Exception Management

  • Use finally blocks to make sure that database connections are closed.
  • Consider employing the Using statement to make sure that database connections are closed.
  • Avoid propagating ADO.NET exceptions to users.
  • In ASP.NET, use a generic error page.
  • Log ADO.NET exception details on the server.

Sensitive Data

  • If you need to store sensitive data, encrypt it.
  • Protect sensitive data on the network.
  • Store hashes with salt instead of passwords.

Code Access Security Considerations

  • Use a Custom Policy if You Need to Access Non-SQL Server Databases from Partial Trust ASP.NET Applications.
  • Consider Restricting Database Access on Hosted Servers.
  • Do Not Rely on StrongNameIdentityPermission to Restrict Full Trust Callers.

Deployment Considerations

  • Apply Firewall Restrictions and Make Sure that Only the Required Ports are Open.
  • Store Encrypted Connection Strings in the Web.config File.
  • Use a Least-Privileged Database Login.
  • Enable Database Auditing, and Log Failed Login Attempts.
  • Protect Data Privacy and Integrity over the Network.

Input / Data Validation

When you construct an application that accesses data, you should assume that all user input is malicious until you can prove otherwise. Failure to do so can leave your application vulnerable to attack. The .NET Framework contains classes to help you validate and constrain input, and provides regular expression support to help evaluate string input data. Use the following guidelines to validate input and data:

  • Use regular expressions to validate input by comparing with expected patterns.
  • If you use ASP.NET, use ASP.NET validator controls.
  • Do not rely on ASP.NET request validation.
  • Validate untrusted input passed to data access methods.

Use Regular Expressions to Validate Input by Comparing with Expected Patterns

Use regular expressions to constrain the acceptable range of input characters and to check lengths. For pattern-based fields, such as tax identification numbers, ZIP codes, or postal codes, use expressions to validate the input with the expected pattern of acceptable characters.

In your data access routines, use the instance or static IsMatch method of the System.Text.RegularExpressionsRegex class to validate input, as shown in the following example.

using System.Text.RegularExpressions;
...
// Instance method:
Regex reg = new Regex(@"^[a-zA-Z'.]{1,40}$");
Response.Write(reg.IsMatch(name));

// Static method:
if (!Regex.IsMatch(name, 
                   @"^[a-zA-Z'.]{1,40}$"))
{
  // Name does not match schema
}

For performance reasons, you should use the static IsMatch method where possible, to avoid unnecessary object creation. For more information, see "How To: Use Regular Expressions to Constrain Input in ASP.NET" at https://msdn.microsoft.com/library/en-us/dnpag2/html/PAGHT000001.asp.

If You Use ASP.NET, Use ASP.NET Validator Controls

If you are using ASP.NET, use the ASP.NET validator controls to constrain and validate input in the presentation layer of your application. ASP.NET validator controls validate the associated control on the server and they provide a client-side implementation to perform validation on the client.

  • Use RegularExpressionValidator to constrain text input.
  • Use RangeValidator to check the ranges of numeric, currency, date, and string input.
  • Use CustomValidator for custom validation, such as ensuring that a date is in the future or in the past.

For more information, see "How To: Use Regular Expressions to Constrain Input in ASP.NET," at https://msdn.microsoft.com/library/en-us/dnpag2/html/PAGHT000001.asp

Do Not Rely on ASP.NET Request Validation

The ASP.NET request validation feature performs basic input validation. Do not rely on it. Use it as an extra precautionary measure in addition to your own input validation. Only you can define what constitutes good input for your application.

Request validation is enabled by default. You can see this by examining the validateRequest attribute, which is set to True on the <pages> element in the Machine.config.comments file. Make sure that it is enabled for all pages except those that need to accept a range of HTML elements.

Validate Untrusted Input Passed to Data Access Methods

If your data access code cannot trust the data passed to it, your data access code should validate the input. Two common situations where you need to provide validation in your data access code are the following:

  • Untrusted clients. If data can come from an untrusted source or you cannot guarantee how well the data has been validated and constrained, add validation logic that constrains input to your data access routines.
  • Library code. If your data access code is packaged as a library designed for use by multiple applications, your data access code should perform its own validation, because you can make no safe assumptions about the client applications.

For performance reasons, you might not want to duplicate validation logic in your data access code and in your application's presentation or business layers. However, you should only omit validation from your data access methods after carefully considering your application's trust boundaries. Omit the additional layer of validation only if you can be sure that the data passed to your data access code comes from a source inside the same trust boundary and has previously passed through validation code.

SQL Injection

SQL injection can occur when your application uses input to construct dynamic SQL statements to access the database, or if your code uses stored procedures that are passed strings that contain unfiltered user input. SQL injection can result in attackers being able to execute commands in your database using the privileges granted to your application's database login. The severity of the issue is magnified if the application uses an over-privileged account to connect to the database.

Common vulnerabilities that make your data access code susceptible to SQL injection attacks include:

  • Weak input validation
  • Dynamic construction of SQL statements without the use of type-safe parameters
  • Use of over-privileged database logins

To help prevent SQL injection, do the following:

  • Constrain and sanitize input data.
  • Use type-safe SQL parameters for data access.
  • Avoid dynamic queries that accept untrusted input.
  • With dynamic SQL, use character escaping to handle special input characters.
  • Use an account that has restricted permissions in the database.

Constrain and Sanitize Input Data

Check for known good data by validating for type, length, format, and range. If you do not expect numeric values, then do not accept them. Where possible, reject entries that contain binary data, escape sequences, and comment characters.

Consider where the input comes from. If it is from a trusted source that you know has performed thorough input validation, you might choose to omit data validation in your data access code. If the data is from an untrusted source or for additional protection, your data access methods and components should validate input.

Use Type-Safe SQL Parameters for Data Access

Using stored procedures does not necessarily prevent SQL injection. The important thing to do is use parameters with stored procedures. If you do not use parameters, your stored procedures can be susceptible to SQL injection if they use unfiltered input. You can use type-safe SQL parameters with stored procedures or dynamically constructed SQL command strings. Parameter collections, such as SqlParameterCollection, provide type checking and length validation. If you use a parameters collection, input is treated as a literal value, and SQL Server does not treat it as executable code. An additional benefit of using a parameters collection is that you can enforce type and length checks. Values outside of the range trigger an exception. This is a good example of defense in depth.

The following code shows how to use SqlParameterCollection when your code calls a stored procedure.

using System.Data;
using System.Data.SqlClient;

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

  myAdapter.Fill(userDataset);
}

If you cannot use stored procedures, you should still use parameters when constructing dynamic SQL statements. The following code shows how to use SqlParametersCollection with dynamic SQL.

using System.Data;
using System.Data.SqlClient;

using (SqlConnection connection = new SqlConnection(connectionString))
{
  DataSet userDataset = new DataSet();
  SqlDataAdapter myDataAdapter = new SqlDataAdapter(
         "SELECT au_lname, au_fname FROM Authors WHERE au_id = @au_id", 
         connection);                
  myDataAdapter.SelectCommand.Parameters.Add("@au_id", SqlDbType.VarChar, 11);
  myDataAdapter.SelectCommand.Parameters["@au_id"].Value = SSN.Text;
  myDataAdapter.Fill(userDataset);
}

Avoid Dynamic Queries that Accept Untrusted Input

Avoid creating dynamic queries directly from user input because this increases the risk of SQL injection. If the whole query or part of the query is built from user input, then malicious input can easily result in modified queries.

Constraining input might not be effective with dynamic queries, so you should avoid creating dynamic queries if the input is untrusted. Even the use of stored procedures cannot guarantee protection from attacks if the stored procedure creates dynamic queries within it. An example could be a stored procedure which takes the whole Where clause as a parameter and then appends it to the rest of the query before executing it.

With Dynamic SQL, Use Character Escaping to Handle Special Input Characters

If you must use dynamic SQL and need to deal with input characters that have special meaning to SQL Server such as the single quote character, you need to use character escaping. This is sometimes necessary because in some situations, you may not be able to use parameterized SQL. For example, you might need to accept a table name or column name as a parameter.

Start by creating a list of known acceptable characters. Use a regular expression to reject any input that contains characters other than those defined in the list. Then, use an escape routine that inserts an escape character or character sequence in front of the special character to make it harmless. The escaping technique ensures that the character no longer has meaning to SQL Server and is considered to be normal text. The following code is an example of an escaping routine.

private string SafeSqlLiteral(string inputSQL)
{ 
  return inputSQL.Replace("'", "''"); 
}

The following example shows how to use this routine to dynamically construct a query. Notice that the dynamic SQL statement is wrapped inside single quotation marks.

string strSQL = 
  "SELECT * FROM authors WHERE au_id = '" + 
  SafeSqlLiteral(Request.QueryString("input")) + "'";

Note   If you use parameters with dynamic SQL or stored procedures, no further effort is required to handle special characters. Special characters do not pose a problem because the parameters are strongly typed and are not treated as executable SQL.

Use an Account that has Restricted Permissions in the Database

Use an account that has restricted database permissions to limit what an attacker can accomplish with SQL injection. The attacker is restricted by the permissions granted to your application's database login.

Use the following process to limit your application's login 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 the application's 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.

For more information about SQL injection, see "How To: Protect From SQL Injection in ASP.NET," at https://msdn.microsoft.com/library/en-us/dnpag2/html/PAGHT000002.asp.

Configuration and Connection Strings

To help limit access to your data source, you must protect connection information, including credentials and data source names. You should use the ConnectionStringBuilder class when your code constructs connection strings from user input. Avoid storing database login credentials in plain text either in configuration files or in your code. Secrets such as this in code can easily be identified by examined the compiled MSIL or by using disassemblers.

To help protect connection strings, do the following:

  • Avoid credentials in connection strings.
  • Store encrypted connection strings in configuration files.
  • Do not use Persist Security Info="True" or "Yes".
  • Avoid connection strings constructed through user input.
  • Avoid Universal Data Link (UDL) files where possible.

Avoid Credentials in Connection Strings

Where possible, use Windows authentication to connect to the database. Connection strings that use Windows authentication do not contain credentials because the application's process or thread identity is used to connect.

To use Windows authentication, you need to use an appropriately formatted connection string, the precise syntax of which varies depending on the provider you are using.

  • With SqlClient, use Integrated Security=true.
  • With SqlClient: OleDb, use Integrated Security=SSPI.
  • With Odbc, use Trusted_Connection=yes.
  • With OracleClient, use Integrated Security=yes.

Additional benefits from using Windows authentication to connect to the database include:

  • Credentials are not transmitted over the network to the database.
  • The use of strong passwords and other password policies, such as password expiration, can be enforced through Microsoft Active Directory® directory service security policy.
  • Active Directory is the central point of account maintenance. There are no separate accounts in the database.

Store Encrypted Connection Strings in Configuration Files

To avoid storing connection strings in your code, you can store them in the Web.config file for an ASP.NET application and in the App.config file for a Windows application. You store connection strings in the <connectionStrings> section of the configuration file.

Connection strings are stored as key/value pairs, where the name can be used to look up the value stored in the connectionString attribute at run time. The following example of a configuration file shows a connection string named MyDatabaseConnection that refers to a connection string which connects to a local instance of SQL Server.

<connectionStrings>
  <add name="MyDatabaseConnection"
       connectionString="Persist Security Info=False;Integrated
                         Security=SSPI;database=Northwind;server=(local);"
       providerName="System.Data.SqlClient" />
</connectionStrings>

Use the following code to retrieve the connection string from the configuration file.

using System.Configuration;
...
string connectionString =
  ConfigurationManager.ConnectionStrings["MyDatabaseConnection"].ConnectionString;

For additional protection, use protected configuration and either DPAPI or RSA encryption to encrypt the <connectionStrings> section. To do so, you use the Aspnet_regiis utility.

Note   Use RSA in Web farms because you can easily export and import RSA keys across servers.

Encrypting connection strings with Aspnet_regiis does not change the code required to access the string because the decryption occurs automatically. Encrypting a connection string is particularly important if you use SQL authentication and you have credentials in the connection string. If you do not have credentials in the connection string, compare the additional security benefits of keeping the database name and server name secret with the additional deployment complexity that encrypting the configuration file introduces.

For more information about how to use DPAPI and RSA encryption to encrypt configuration file elements, see:

Do Not Use Persist Security Info="true" or "yes"

If you must supply a user ID and password when making a connection, you should make sure that this information is discarded after it is used to open the connection. This occurs when Persist Security Info is set to "false" or "no".

Setting the Persist Security Info keyword to "true" or "yes" in a connection string allows security-sensitive information, including the user ID and password; to be obtained from the connection after the connection has been opened.

Keeping Persist Security Info as "false" helps to make sure that the untrusted source does not have access to the security-sensitive information for your connection and also helps to make sure that no security-sensitive information is saved to disk with your connection string information. Persist Security Info is set to "false" by default.

Avoid Connection Strings Constructed With User Input

Where possible, avoid connection strings constructed with user input. If you must build a connection string dynamically with information from an external source—such as using a user-supplied data source or server name or a user ID and password—make sure that you validate any input from the source. Then, use one of the connection string builder classes that derive from System.Data.Common.DbConnectionStringBuilder class, as shown in the following code example. The code example shows the System.Data.SqlClient.SqlConnectionStringBuilder class for use with the SQL Server managed data provider.

using System.Data.SqlClient;
...
public string BuildConnectionString(string serverName, string uid, string pwd)
{
  // Validate input parameters
  ...

  // Build Connection string
  SqlConnectionStringBuilder connectionStringBuilder = new 
                                              SqlConnectionStringBuilder();
  connectionStringBuilder.DataSource = serverName;
  connectionStringBuilder.UserID = uid;
  connectionStringBuilder.Password = pwd;
  return connectionStringBuilder.ConnectionString;
}

The ConnectionStringBuilder class is new to ADO.NET 2.0. Use the ConnectionStringBuilder class to allow compile-time checks and to reduce the risk of connection string injection attacks. You should use this class to build your connection string, rather than passing simple strings to the Connection objects.

DbConnectionStringBuilder class provides the base class from which strongly typed connection string builders derive. These include SqlConnectionStringBuilder, OracleConnectionStringBuilder, OdbcConnectionStringBuilder, and OleDbConnectionStringBuilder.

You should avoid supplying connection information for an OleDbConnection in a Universal Data Link (UDL) file. UDL files are not encrypted and expose connection string information in clear text. Because a UDL file is an external file-based resource to your application, it cannot be secured by using the .NET Framework.

If you must use UDL files, restrict access to them by configuring an access control list (ACL) that restricts access to the account under which your application runs.

Authentication

When your application connects to a SQL Server database, you have a choice of Windows authentication or SQL authentication. Windows authentication offers greater protection. If you must use SQL authentication, perhaps because you need to connect to the database using a number of different accounts and you want to avoid calling LogonUser, make sure that you protect your approach as much as possible by following the guidelines outlined below.

  • If possible, use Windows authentication.
  • If you use SQL authentication, use strong passwords.
  • If you use SQL authentication, protect credentials on the network.
  • If you use SQL authentication, protect credentials in the configuration files.
  • Consider which identity to use to connect to the database.

If Possible, Use Windows Authentication

If you can, use Windows authentication when your application connects to SQL Server or other databases that support Windows authentication. Windows authentication offers the following security advantages as compared 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.

The following example uses Windows authentication with the ADO.NET data provider for SQL Server.

SqlConnection pubsConn = new SqlConnection(
    "server=dbserver; database=pubs; Integrated Security=SSPI;");

The following example uses the ADO.NET data provider for OLE DB data sources.

OleDbConnection pubsConn = new OleDbConnection(
   "Provider=SQLOLEDB; Data Source=dbserver; Integrated Security=SSPI;" +
   "Initial Catalog=northwind");

For more information, see "How To: Connect to SQL Server Using Windows Authentication in ASP.NET 2.0," at https://msdn.microsoft.com/library/en-us/dnpag2/html/PAGHT000008.asp

If You Use SQL Authentication, Use Strong Passwords

If you use SQL Server authentication, make sure that you use a least-privileged account with a strong password to prevent an attacker from 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=;"

Note   In SQL Server 2005, you can enable an account option to require strong passwords and the server will check passwords against the server operating system password policy.

If You Use SQL Authentication, Protect Credentials on the Network

If your application is not located in a physically secure isolated data center and you use SQL authentication, you should use Internet Protocol Security (IPSec) or Secure Sockets Layer (SSL) to create an encrypted communication channel between the Web server and database server . When you connect to SQL Server with SQL authentication, the credentials are not encrypted prior to transmission across the network. If you do not secure your network channel with IPSec or SSL, an attacker can easily capture credentials by using a network monitor.

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 secure all of the IP traffic between the Web and database servers, 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.

Note   During login, the SQL Server client encrypts the login packet by using SSL if the server has a certificate available. This occurs regardless of whether encryption is enabled for the connection.

If You Use SQL Authentication, Protect Credentials in the Configuration Files

To protect credentials in configuration files, place connection strings inside the <connectionStrings> section, and encrypt them by using the Aspnet_regiis.exe tool. For more information, see the section "Store Encrypted Connection Strings in Configuration Files," in this document.

Consider Which Identity to Use to Connect to the Database

When you connect to a database by using Windows authentication, you need to consider which account to use. Regardless of the account, make sure that it has limited permissions in the database to minimize the damage that can be done should the account be compromised or if an attacker manages a successful SQL injection attack. When using Windows authentication to connect to the database, do the following:

  • Use a trusted service account where possible. This is usually your application's process account. By using a single trusted service account, your application benefits from connection pooling, which provides greater scalability. Also, account administration and authorization within the database is simplified.
  • 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 make sure that the passwords of the two accounts are synchronized.
  • Use impersonation and delegation when necessary. If you need per-user authorization in the database or if you need to use operating system auditing to track the activity of individual users, use impersonation and delegation and access the database by using the caller's identity. This approach has limited scalability because it prevents the efficient use of connection pooling.

Authorization

You use data access authorization to determine who can retrieve and manipulate specific data. There are two approaches: your data access code can use authorization to determine whether or not to perform the requested operation, or the database can perform authorization to restrict the capabilities of the SQL login used by your application.

Figure 1 summarizes the authorization points and techniques that should be used to authorize access to data.

Ff647552.adonetguidelinesauthorizationpoints(en-us,PandP.10).gif

Figure 1. Data access code and database authorization

With inadequate authorization, a user might be able to see or modify the data of another user or an unauthorized user may be able to access restricted data. To address these threats, do the following:

  • Restrict unauthorized callers.
  • Restrict unauthorized code.
  • Restrict application access to the database.

Restrict Unauthorized Callers

Application code should authorize a user based on a role or identity before the application allows the user to connect to the database. Ideally, role checks should be performed in the business logic layer. For additional protection, data access code can perform authorization by using a variety of techniques, including those outlined below.

You can use principal permission demands on data access methods when you want method-level authorization. The following attribute ensures that only users who are members of the Manager role can call the GetCustomerDetails method.

using System.Security.Permissions;
...

[PrincipalPermissionAttribute(SecurityAction.Demand, Role="Manager")]
public void GetCustomerDetails(int CustId)
{
}

If finer granularity is required and authorization needs to be performed within a method, then you can perform imperative principal permission demands or explicit role checks on a block of code. The following code example shows how to perform an imperative principal permission demand.

using System.Security;
using System.Security.Permissions;

public void GetCustomerDetails(int CustId)
{
  try
  {
    // Imperative principal permission role check to verify
    // that the caller is a manager
    PrincipalPermission principalPerm = new PrincipalPermission(null, "Manager");
    principalPerm.Demand();
    // Code that follows is only executed if the caller is a
    // member of the "Manager" role
  }
  catch( SecurityException ex )
  {
   . . .
  }
}

The following code example uses explicit role checks.

public void GetCustomerDetails(int CustId)
{
  if(!Thread.CurrentPrincipal.IsInRole("Manager"))
  {
    . . .
  }
}

Restrict Unauthorized Code

Ensuring that only trusted code can access your data access classes and methods decreases the risk that malicious code can exploit your application.

To restrict access to your code, do the following:

  • Design for restricted access.
  • Place ASP.NET data access code in your application's Bin directory.
  • Use strong names for data access library code.

Design for Restricted Access

Carefully design the public interfaces, classes, and methods exposed by your data access code. Make sure that code which is for internal use by the data access library is marked private. When designing for restricted access, consider the following guidelines:

  • Consider creational patterns, such as Abstract Factory or Singleton, to help make sure that the data access library retains control over how its objects are instantiated. Having the factory return an interface also ensures that the actual class remains hidden from the outside.
  • If you expose extension points through a provider model to provide the use with the ability to implement an interface or extend a class, make sure that the extension class has restricted access to the underlying data access code.
  • Mark classes and methods that need not be accessed outside the data access block as internal.
  • Seal classes that need to be exposed but should not be inherited from.
  • Mark methods that do not need to be exposed as private or internal.

Place ASP.NET Data Access Code in Your Application's Bin Directory

If you are developing data access code for exclusive use by a single ASP.NET application, place the data access assembly in your application's Bin directory. To restrict access to the assembly, configure an ACL for the Bin directory that provides only read access to your application's unique process identity.

Use Strong Names for Data Access Library Code

Data access library code that is to be used by more than one application should be strong name signed and added to the global assembly cache. The added benefit of strong naming your library code is that the common language runtime prevents partially trusted code from calling a strong named assembly by adding a link demand for the FullTrust permission set.

Restrict Application Access to the Database

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 the authentication mechanism, restrict the account's permissions in the database.

Use the following process 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.

Unless there are specific reasons otherwise, the application should not be authorized to perform create, retrieve, update, and destroy/delete operations directly on any table. Instead, limit access to selected stored procedures only. If you must grant table access, grant the minimum access that the application requires.

By using a database role, you avoid granting permissions directly to a 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.

Exception Management

Exception conditions can be caused by configuration errors, bugs in your code, or malicious input. Without proper exception management, these conditions can reveal sensitive information about the location and nature of your data source, as well as connection details. Attackers often use information from an exception—such as the name of your server, database, or table—to help attack your application.

Consider the following guidelines to make sure that you have appropriate exception management:

  • Use finally blocks to make sure that database connections are closed.
  • Consider employing the Using statement to make sure that database connections are closed.
  • Avoid propagating ADO.NET exceptions to users.
  • In ASP.NET, use a generic error page.
  • Log ADO.NET exception details on the server.

Use Finally Blocks to Make Sure that Database Connections Are Closed

If exceptions occur, you must make sure that database connections are closed. To do so, use finally blocks. However, be aware that exceptions are expensive. Do not catch exceptions and then return them if your data access logic cannot add any value. A less costly approach is to permit the exception to propagate from the database to the caller. Similarly, do not wrap transaction attempts with try/catch blocks unless you plan to implement retry mechanisms. Use code similar to the following example to make sure that database connections are closed.

// Data access method
public string RetrieveProductName(int productID)
{
  ...
  SqlConnection conn = null;
  try
  {
    // Open database connection and perform data access
    ...  
  }
  catch (SqlException sqlex)
  {
    // Log exception details on the server
    ...
    // Re-throw a new more relevant exception 
    ...
  }
  finally
  {
    if(conn != null) 
       conn.Close(); // Ensures connection is closed
  }
}

Consider Employing the Using Statement to Make Sure that Database Connections Are Closed

The using statement is available to developers who use the Microsoft Visual C#® 1.1 and 2.0 and to developers who use Microsoft Visual Basic® .NET 2.0. At compile time, the using statement automatically generates a try and finally block that calls Dispose on the object allocated inside the using block. For connection objects, this ensures that the connection's Close method is called. The following code illustrates this syntax.

using ( SqlConnection conn = new SqlConnection( _connString) )
{
  // Open the connection and access the database
  ...
} // Dispose is called and the connection closed

During compilation, the preceding code is converted into the following equivalent code.

SqlConnection conn = new SqlConnection( _connString);
try
{
  // Open the connection and access the database
  ...
}
finally{
  conn.Dispose(); // Closes the connection
}

Avoid Propagating ADO.NET Exceptions to Users

You should prevent ADO.NET exception details from being displayed to your application users. ADO.NET exceptions include many details that are useful to an attacker, including database server names, database names, table names, and so on. Use try/catch blocks to trap exceptions on the server, log appropriate details for subsequent diagnostics, and return an appropriate error message to the user. Display only generic information. It is usually enough for users to know that an error has occurred and the operation did not succeed, and to have a failure error code that they can report to a help desk.

One approach is to catch ADO.NET exceptions on the server and then propagate a custom exception object from your data access code, as shown in the following example.

// Data access method
public string RetrieveProductName(int productID)
{
  ...
  try
  {
    // Open database connection and perform data access
    ...  
  }
  catch (SqlException sqlex)
  {
    // Log exception details on the server
    ...
    // Re-throw a new more relevant exception 
    throw new DataAccessException( 
                      "Failed to retrieve product information for product ID: " +
                      ProductID.ToString());,
  }
  finally
  {
    if(conn != null) 
       conn.Close(); // Ensures connection is closed
  }
}

Note that when ADO.NET data access code generates a database exception, the abstract System.Data.Common.DbException is thrown. The actual type of exception generated by ADO.NET depends on the data provider. For example, the exception type could be any of the following:

  • System.Data.Odbc.OdbcException
  • System.Data.OleDb.OleDbException
  • System.Data.OracleClient.OracleException
  • System.Data.SqlClient.SqlException

Note   If you are writing provider independent code, catch the DBException type. Otherwise catch one of the provider specific exception types listed above, depending on the provider you are using.

In ASP.NET, Use a Generic Error Page

In ASP.NET applications, it is a good practice to use a generic error page that is displayed for all unhandled exceptions. Define an application-level global error handler in Global.asax to catch any exceptions that are not handled in code. Do this to avoid accidentally returning detailed error information to the client. Use code similar to the following.

<%@ Application Language="C#" %>
 <%@ Import Namespace="System.Diagnostics" %>
 
 <script language="C#" >
 void Application_Error(object sender, EventArgs e)
 {
    //get reference to the source of the exception chain
    Exception ex = Server.GetLastError().GetBaseException();
 
    // log the details of the exception and page state to the
    // event log.
    EventLog.WriteEntry("My Web Application",
      "MESSAGE: " + ex.Message + 
      "\nSOURCE: " + ex.Source, 
      EventLogEntryType.Error);
 
    // Optional e-mail or other notification here...
 }
 </script>

Log ADO.NET Exception Details on the Server

Log detailed ADO.NET exception information on the server. This information is essential for problem diagnosis. The following code shows a helper routine that you can use to log SqlException details to the application event log.

using System.Data;
using System.Data.SqlClient;
using System.Data.Diagnostics;
...
// Data Access method
public string RetrieveProductName( int productID )
{
  SqlConnection conn = null;
  // Enclose all data access code within a try block
  try
  {
    conn = new SqlConnection(_connString);
    conn.Open();
    SqlCommand cmd = new SqlCommand("LookupProductName", conn );
    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.Add("@ProductID", productID );
    SqlParameter paramPN = 
         cmd.Parameters.Add("@ProductName", SqlDbType.VarChar, 40 );
    paramPN.Direction = ParameterDirection.Output;

    cmd.ExecuteNonQuery();
    // The finally code is executed before the method returns
    return paramPN.Value.ToString();  
  }
  catch (SqlException sqlex)
  {
    // Handle data access exception condition
    // Log specific exception details
    LogException(sqlex);
    // Wrap the current exception in a more relevant
    // outer exception and re-throw the new exception
    throw new DataAccessException(
                  "Unknown ProductID: " + productID.ToString(), sqlex );
  }
  catch (Exception ex)
  {
    // Handle generic exception condition . . .
    throw ex;
  }
  finally
  {
    if(conn != null) conn.Close(); // Ensures connection is closed
  }
}

// Helper routine that logs SqlException details to the 
// Application event log
private void LogException( SqlException sqlex )
{
  EventLog el = new EventLog();
  el.Source = "CustomAppLog";
  string strMessage;
  strMessage = "Exception Number : " + sqlex.Number + 
               "(" + sqlex.Message + ") has occurred";
  el.WriteEntry( strMessage );

  foreach (SqlError sqle in sqlex.Errors)
  {
    strMessage = "Message: " + sqle.Message +
                 " Number: " + sqle.Number +
                 " Procedure: " + sqle.Procedure +
                 " Server: " + sqle.Server +
                 " Source: " + sqle.Source +
                 " State: " + sqle.State +
                 " Severity: " + sqle.Class +
                 " LineNumber: " + sqle.LineNumber;
    el.WriteEntry( strMessage );
  }
}

For more information about logging errors, see "How to: Write to the Application Event Log (Visual C#)" at https://msdn.microsoft.com/en-us/library/42ste2f3.aspx, and How to: Write to an Application Event Log" at https://msdn.microsoft.com/en-us/library/07347hdt.aspx.

Sensitive Data

In most cases, you should protect any sensitive data that you keep in persistent storage. However, wherever possible, you should look for opportunities to avoid storing sensitive data. For example, store password hashes rather than the passwords themselves. To make sure that sensitive data cannot be viewed, use encryption and carefully examine the way in which you protect the encryption key.

To help protect sensitive data, do the following:

  • If you need to store sensitive data, encrypt it.
  • Protect sensitive data on the network.
  • Store hashes with salt instead of passwords.

If You Need to Store Sensitive Data, Encrypt It

Avoid storing sensitive data in the database, if possible. If you must store sensitive data, protect the data by using encryption. Use a strong symmetric encryption algorithm such as AES to encrypt the sensitive data before storing it in the database. Use DPAPI to encrypt the symmetric encryption key, and secure the encrypted key in a protected location such as the Windows registry in a key that has an ACL that restricts access to your application's process account.

Symmetric encryption requires the generation and secure storage of a key to be used for encryption. As far as possible, a new key and initialization vector (IV) should be created for every session and should not be stored for use in a later session. A new key and IV are automatically created when you create a new instance of one of the managed symmetric cryptographic classes by using the default constructor.

Note   SQL Server 2005 includes support for data encryption directly in the database.

Protect Sensitive Data over the Network

Sensitive data passed across the network to and from the database server could include application-specific data or database login credentials. To ensure the privacy and integrity of data over the network, either use a platform-level solution, such as that provided by a secure datacenter where Internet Protocol Security (IPSec) encrypted communication channels are used between servers, or configure your application to establish SSL connections to the database. The latter approach requires a server certificate installed on the database server.

  • Use SSL when you need granular channel protection for a particular application, instead of for all applications and services running on a computer.
  • Use Internet Protocol Security (IPSec) to secure the communication channel between two servers and to restrict which computers can communicate with one another. For example, you can help secure 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.

Store Hashes with Salt Instead of Storing Passwords

Do not store user passwords either in plain text or encrypted format. Instead, store non-reversible password hashes with salt. By storing your password with hashes and salt, you help to prevent an attacker that gains access to your user store from obtaining the user passwords. If you use encryption, you have the added problem of securing the encryption key. For ASP.NET applications, use one of the membership providers to help protect credentials in storage and where possible specify a hashed password format on your provider configuration.

If you must implement your own user stores, store one-way password hashes with salt. Generate the hash from a combination of the password and a random salt value. Use an algorithm such as SHA256. If your credential store is compromised, the salt value helps to slow an attacker who is attempting to perform a dictionary attack. This gives you additional time to detect and react to the compromise.

Code Access Security Considerations

To use the managed .NET data providers from partial trust applications, your application must be granted the appropriate code access security permission. To access non–SQL Server data sources from partial trust Web applications you need to modify policy. The permission requirements for each of the .NET data providers are as follows:

  • The SQL Server .NET data provider requires SqlClientPermission.
  • The OLE DB NET data provider requires OleDbPermission.
  • The Oracle .NET data provider requires OraclePermission.
  • The Odbc .NET data provider requires OdbcPermission.

Consider the following guidelines when you use code access security with data access code:

  • Use a custom policy if you need to access non–SQL Server databases from partial trust ASP.NET applications.
  • Consider restricting database access on hosted servers.
  • Do not rely on StrongNameIdentityPermission to restrict full trust callers.

Use a Custom Policy if You Need to Access Non-SQL Server Databases from Partial Trust ASP.NET Applications

Default medium trust ASP.NET policy grants applications the SqlClientPermission. This means that Web applications configured to run at medium trust can access SQL Server databases. If you need to access additional database types by using an alternate provider, you must create a custom policy and grant the appropriate permission (such as OleDbPermission) to access non–SQL Server OLE DB data sources.

For more information about customizing policy, see "How To: Use Medium Trust in ASP.NET 2.0" at https://msdn.microsoft.com/library/en-us/dnpag2/html/PAGHT000020.asp.

Consider Restricting Database Access on Hosted Servers

Adding the unrestricted OleDbPermission to your policy file means that your application can use any OLE DB provider on the server. In a hosted environment, an administrator might need to use the more advanced form of the OleDbPermission syntax to lock down connection strings used with OleDbPermission to allow access only to specific databases. The following example shows how to restrict access to a specific OLE DB data source.

<IPermission class="OleDbPermission"
             version="1">
  <add ConnectionString=
          "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\data\w4w.mdb"
       KeyRestrictions=""
       KeyRestrictionBehavior="AllowOnly"/>
</IPermission>

For more information, see "How To: Use Medium Trust in ASP.NET 2.0" at https://msdn.microsoft.com/library/en-us/dnpag2/html/PAGHT000020.asp.

Do Not Rely on StrongNameIdentityPermission to Restrict Full Trust Callers

If you protect your data access code with a link demand for a StrongNameIdentityPermission to restrict the code that can call your code, be aware that this only works for partial trust callers. The link demand will always succeed for full trust callers regardless of the strong name of the calling code.

In .NET 2.0 any fully trusted assembly will satisfy any demand, including a link demand for an identity permission that the assembly does not satisfy. In .NET 1.0 this did not happen automatically. However, a fully trusted assembly could simply call Assembly.Load, supplying as evidence the strong name it wants to satisfy or alternatively it could turn code access security off.

The only protection against fully trusted code is to put it in a separate process and run that process with a restricted token so that its limits are enforced by the operating system. This applies whether code marks its interfaces as internal, private, or places link demands for StrongNameIdentityPermission on them.

The following code sample shows a method decorated with a link demand for a specific StrongNameIdentityPermission.

using System.Security.Permissions;
. . .
[StrongNameIdentityPermission(SecurityAction.LinkDemand,
                              PublicKey="002...4c6")]
public void GetCustomerInfo(int CustId)
{
}

Deployment Considerations

Secure data access code can still be vulnerable to attack if it is not deployed in a secure manner. A common deployment practice is for the data access code and database to reside on separate servers. The servers are often separated by an internal firewall, which introduces additional deployment considerations. The deployment aspects which have an impact on security are:

  • Firewall restrictions
  • Connection string management
  • Login account configuration
  • Logon auditing
  • Data privacy and integrity on the network

Consider the following guidelines when you deploy your application:

  • Apply firewall restrictions and make sure that only the required ports are open.
  • Store encrypted connection strings in the Web.config file.
  • Use a least-privileged database login.
  • Enable database auditing, and log failed login attempts.
  • Protect data privacy and integrity over the network.

Apply Firewall Restrictions and Make Sure that Only the Required Ports are Open

If you connect to SQL Server through a firewall, configure the firewall, client, and server. Configure the client by using the SQL Server Client Network Utility. Configure the database server by using the Server Network Utility. By default, SQL Server listens on TCP port 1433, although you can change this. You must open the chosen port at the firewall.

Depending on the SQL Server authentication mode you choose and your application's use of distributed transactions, you may need to open several additional ports at the firewall:

  • If your application uses Windows authentication to connect to SQL Server, the necessary ports to support Kerberos or NTLM authentication must be open.
  • For networks that do not use Active Directory, TCP port 139 is usually required for Windows authentication.

If your application uses distributed transactions, for example automated COM+ transactions, you might need to configure your firewall to allow DTC traffic to flow between separate DTC instances and between the DTC and resource managers such as SQL Server.

Store Encrypted Connection Strings in the Web.config File

Many applications store connection strings in code, primarily for performance reasons. However, the performance benefit is negligible, and use of file system caching helps to ensure that storing connection strings in external files gives comparable performance.

Store connection strings in the <connectionStrings> section of your application's Web.config file, and then use Aspnet_regiis.exe to encrypt them by using either the DPAPI or RSA protected configuration providers.

If your application is deployed in a Web farm, use RSA encryption due to the ease with which you can export and import RSA keys. For more information, see "Store Encrypted Connection Strings in Configuration Files" in the "Configuration and Connection Strings" section of this module.

Use a Least-Privileged Database Login

It is essential that your application uses a least-privileged account to connect to the database. This is one of the primary threat mitigation techniques for SQL injection attacks.

As a developer, you must communicate to the database administrator the precise stored procedures and (possibly) tables that the application's login needs to access. Ideally, you should only allow the application's login to have execute permissions on a restricted set of stored procedures that are deployed with the application.

Use strong passwords for the SQL or Windows account or accounts used by the application to connect to the database.

For more information about restricting the application's account in the database, see the section, "Restrict Application Access to the Database" in the "Authorization" section of this module.

Enable Database Auditing, and Log Failed Login Attempts

You should configure SQL Server to log failed login attempts and possibly successful login attempts. Auditing failed login attempts is helpful to detect an attacker who is attempting to discover account passwords.

By default, SQL Server login auditing is not enabled. Minimally, you should audit failed logins.

Note   Log entries are written to SQL log files. By default, these are located in C:\Program Files\Microsoft SQL Server\MSSQL\LOG. You can use any text reader, such as Notepad, to view them.

To enable SQL Server auditing

  1. Start SQL Server Enterprise Manager, expand the SQL Server Group, and then expand your SQL Server.
  2. Right-click your SQL Server, and then click Properties.
  3. Click the Security tab.
  4. Set the Audit level to either All or Failure.
  5. Restart SQL Server for the changes to audit policy to take effect.

For more information about SQL Server audit logs, see the TechNet article, "SQL Server 2000 Auditing," particularly the section, "Understanding the Audit Log," at https://technet.microsoft.com/en-us/library/dd277388.aspx.

Protect Data Privacy and Integrity over the Network

If you use SQL authentication to connect to SQL Server, make sure that login credentials are not exposed over the network. Either install a certificate on the database server (which causes SQL Server to encrypt the credentials) or use an IPSec-encrypted channel to the database. For more information, see "Protect Sensitive Data On the Network" in the "Sensitive Data" section of this module.

Companion Guidance

Additional Resources

Feedback

Provide feedback by using either a Wiki or e-mail:

We are particularly interested in feedback regarding the following:

  • Technical issues specific to recommendations
  • Usefulness and usability issues

Technical Support

Technical support for the Microsoft products and technologies referenced in this guidance is provided by Microsoft Support Services. For product support information, please visit the Microsoft Product Support Web site at https://support.microsoft.com.

Community and Newsgroups

Community support is provided in the forums and newsgroups:

To get the most benefit, find the newsgroup that corresponds to your technology or problem. For example, if you have a problem with ASP.NET security features, you would use the ASP.NET Security forum.

Contributors and Reviewers

  • External Contributors and Reviewers: Anil John, Johns Hopkins University–Applied Physics Laboratory; Frank Heidt
  • Microsoft Product Group: Don Willits, Pablo Castro, Stefan Schackow
  • Microsoft IT Contributors and Reviewers: Akshay Aggarwal, Shawn Veney, Talhah Mir
  • Microsoft Services and PSS Contributors and Reviewers: Adam Semel, Tom Christian, Wade Mascia
  • Microsoft patterns & practices Contributors and Reviewers: Carlos Farre
  • Test team: Larry Brader, Microsoft Corporation; Nadupalli Venkata Surya Sateesh, Sivanthapatham Shanmugasundaram, Infosys Technologies Ltd.
  • Edit team: Nelly Delgado, Microsoft Corporation
  • Release Management: Sanjeev Garg, Microsoft Corporation

patterns & practices Developer Center

Retired Content

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.