Chapter 14 – Building Secure Data Access


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

Improving Web Application Security: Threats and Countermeasures

J.D. Meier, Alex Mackman, Michael Dunner, Srinath Vasireddy, Ray Escamilla and Anandha Murukan
Microsoft Corporation

Published: June 2003

Last Revised: January 2006

Applies to:

  • ADO.NET 1.1

See the "patterns & practices Security Guidance for Applications Index" for links to additional security resources.

See the Landing Page for the starting point and a complete overview of Improving Web Application Security: Threats and Countermeasures.

Summary: This chapter shows you how to build secure data access code, and address top threats to data access code including SQL injection. In addition to addressing SQL injection, the chapter covers design considerations for secure data access code, database authorization strategies, securing connection strings and protecting sensitive data in the database by using hashing techniques for passwords and encryption for sensitive data such as credit card numbers.


In this Chapter
How to Use This Chapter
Threats and Countermeasures
Design Considerations
Input Validation
SQL Injection
Configuration Management
Sensitive Data
Exception Management
Building a Secure Data Access Component
Code Access Security Considerations
Deployment Considerations
Additional Resources

In this Chapter

  • Preventing SQL injection attacks
  • Encrypting data in the database
  • Securing data over the network
  • Securing database connection strings
  • Handling data access exceptions


The database is a prime target for application level attacks. Application level attacks are used to exploit vulnerabilities in your data access code to gain access to the database. If all other attack vectors are closed, then the application's front door, port 80, becomes the path of choice for an attacker to steal, manipulate, and destroy data.

This chapter shows you how to build secure data access code and avoid common vulnerabilities and pitfalls. The chapter presents a series of countermeasures and defensive techniques that you can use in your data access code to mitigate the top threats related to data access.

How to Use This Chapter

To get the most out of this chapter, read the following chapters before or in conjunction with this chapter:

  • Read Chapter 2, "Threats and Countermeasures." This will give you a broader and deeper understanding of potential threats and countermeasures faced by Web applications.
  • Read Chapter 4, "Design Guidelines for Secure Web Applications." In this chapter, you will learn the architecture and design challenges and guidelines for building a secure solution.
  • Read Chapter 18, "Securing Your Database Server." Read Chapter 18 to understand how the database servers are secured.
  • Read Chapter 7, "Building Secure Assemblies." The guidelines and recommendations in Chapter 7 for building secure assemblies and for developing secure managed code should also be applied to data access code.
  • Use the Assessing Chapters. To review the security of your data access at different stages of the product cycle, refer to the Web services sections in the following chapters: Chapter 5, "Architecture and Design Review for Security," Chapter 21, "Code Review," and Chapter 22, "Deployment Review."
  • Use the Checklist. "Checklist: Securing Data Access" in the Checklists section of this guide includes a checklist for easy reference. Use this task-based checklist as a summary of the recommendations in this chapter.

Threats and Countermeasures

To build secure data access code, know what the threats are, how common vulnerabilities arise in data access code, and how to use appropriate countermeasures to mitigate risk.

The top threats to data access code are:

  • SQL injection
  • Disclosure of configuration data
  • Disclosure of sensitive application data
  • Disclosure of database schema and connection details
  • Unauthorized access
  • Network eavesdropping

Figure 14.1 illustrates these top threats.


Figure 14.1

Threats and attacks to data access code

SQL Injection

SQL injection attacks exploit vulnerable data access code and allow an attacker to execute arbitrary commands in the database. The threat is greater if the application uses an unconstrained account in the database because this gives the attacker greater freedom to execute queries and commands.


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 counter SQL injection attacks, be sure to:

  • Constrain and sanitize input data.
  • Use type safe SQL parameters for data access. These parameters can be used with stored procedures or dynamically constructed SQL command strings. Parameters perform type and length checks and also ensure that injected code is treated as literal data, not executable statements in the database.
  • Use an account that has restricted permissions in the database. Ideally, you should only grant execute permissions to selected stored procedures in the database and provide no direct table access.

Disclosure of Configuration Data

The most sensitive configuration data used by data access code is the database connection string. If a compromised connection string includes a user name and password, the consequences can be greater still.


The following vulnerabilities increase the security risk associated with compromised configuration data:

  • Use of SQL authentication, which requires credentials to be specified in the connection string
  • Embedded connection strings in code
  • Clear text connection strings in configuration files
  • Failure to encrypt a connection string


To prevent disclosure of configuration data:

  • Use Windows authentication so that connection strings do not contain credentials.
  • Encrypt the connection strings and restrict access to the encrypted data.

Disclosure of Sensitive Application Data

Many applications store sensitive data, such as customer credit card numbers. It is essential to protect the privacy and integrity of this type of data.


Coding practices that can lead to the disclosure of sensitive application data include:

  • Storing data with no encryption
  • Weak authorization
  • Weak encryption


To prevent disclosure of sensitive application data:

  • Use strong encryption to secure the data.
  • Authorize each caller prior to performing data access so that users are only able to see their own data.

Disclosure of Database Schema and Connection Details

If your code returns exception details to the client, a malicious user can use the information to attack the server. Exceptions in data access code can reveal sensitive information, such as database schema details, the nature of the data store, and SQL code fragments.


The following vulnerabilities can result in information disclosure:

  • Inadequate exception handling
  • Weak ASP.NET configuration that allows unhandled exception details to be returned to the client


To prevent such disclosure:

  • Catch, log, and handle data access exceptions in your data access code.
  • Return generic error messages to the caller. This requires appropriate configuration of the <customErrors> element in the Web.config or Machine.config configuration file.

Unauthorized Access

With inadequate authorization, users may be able to see another user's data and may be able to access other restricted data.


Practices that can allow unauthorized access include:

  • Lack of authorization in data access code providing unrestricted access
  • Over-privileged database accounts


To prevent unauthorized access:

  • Use principal permission demands to authorize the calling user.
  • Use code access security permission demands to authorize the calling code.
  • Use limited permissions to restrict the application's login to the database and to prevent direct table access.

Network Eavesdropping

The deployment architecture of most applications includes a physical separation of the data access code from the database server. As a result, sensitive data such as application-specific data or database login credentials must be protected from network eavesdroppers.


The following practices increase vulnerability to network eavesdropping:

  • Clear text credentials passed over the network during SQL authentication
  • Unencrypted sensitive application data sent to and from the database server


To limit vulnerability to network eavesdropping:

  • Use Windows authentication to avoid sending credentials over the network.
  • Install a server certificate on the database server. This results in the automatic encryption of SQL credentials over the network.
  • Use an SSL connection between the Web server and database server to protect sensitive application data. This requires a database server certificate.
  • Use an IPSec encrypted channel between Web and database server.

Design Considerations

Before you start writing code, there are a number of important issues to consider at design time. The key considerations are:

  • Use Windows authentication.
  • Use least privileged accounts.
  • Use stored procedures.
  • Protect sensitive data in storage.
  • Use separate data access assemblies.

Use Windows Authentication

Ideally, your design should use Windows authentication for the added security benefits. With Windows authentication, you do not have to store database connection strings with embedded credentials, credentials are not passed over the network, and you benefit from secure account and password management policies. You do however need to carefully consider which account you will use to connect to SQL Server using Windows authentication.

For more information, see "Authentication" later in this chapter.

Use Least Privileged Accounts

Your application should use a least privileged account that has limited permissions in the database. Be sure that the application login to the database is appropriately authorized and restricted. For details, see "Authorization" later in this chapter.

Using least privileged accounts reduces risk and limits the potential damage if your account is compromised or malicious code is injected. In the case of SQL injection, the command executes under the security context defined by the application login and is subject to the associated permissions that the login has in the database. If you connect using an overprivileged account — for example, as a member of the SQL Server sysadmin role — the attacker can perform any operation in any database on the server. This includes inserting, updating, and deleting data; dropping tables; and executing operating system commands.

Important   Do not connect to SQL Server using the sa account or any account that is a member of the SQL Server sysadmin or db_owner roles.

Use Stored Procedures

Stored procedures offer performance, maintenance, and security benefits. Use parameterized stored procedures for data access where possible. The security benefits include:

  • You can restrict the application database login so that it only has permission to execute specified stored procedures. Granting direct table access is unnecessary. This helps mitigate the risk posed by SQL injection attacks.
  • Length and type checks are performed on all input data passed to the stored procedure. Also, parameters cannot be treated as executable code. Again, this mitigates the SQL injection risk.

If you cannot use parameterized stored procedures for some reason and you need to construct SQL statements dynamically, do so using typed parameters and parameter placeholders to ensure that input data is length and type checked.

Protect Sensitive Data in Storage

Identify stored data that requires guaranteed privacy and integrity. If you store passwords in database solely for the purposes of verification, consider using a one-way hash. If the table of passwords is compromised, the hashes cannot be used to obtain the clear text password.

If you store sensitive user-supplied data such as credit card numbers, use a strong symmetric encryption algorithm such as Triple DES (3DES) to encrypt the data. Encrypt the 3DES encryption key using the Win32 Data Protection API (DPAPI), and store the encrypted key in a registry key with a restricted ACL that only administrators and your application process account can use.

Why not DPAPI?

While DPAPI is recommended for encrypting connection strings and other secrets such as account credentials that can be manually recovered and reconstructed in the event of machine failure, it is less suited to storing data like credit card numbers. This is because of recoverability issues (if the keys are lost, there is no way to recover the encrypted data) and Web farm issues. Instead, you should use a symmetric encryption algorithm such as 3DES and encrypt the encryption key using DPAPI.

The main issues that make DPAPI less suited for storing sensitive data in the database are summarized below:

  • If DPAPI is used with the machine key and you pass CRYPTPROTECT_LOCAL_MACHINE to the CryptProtectData and CryptUnprotectData functions, the machine account generates the encryption keys. This means that each server in a Web farm has a different key, which prevents one server from being able to access data encrypted by another server. Also, if the Web server machine is destroyed, the key is lost, and the encrypted data cannot be recovered from the database.

  • If you use the machine key approach, any user on that computer can decrypt the data (unless you use additional encryption mechanisms).

  • If you use DPAPI with a user key and use local user accounts, each local account on each Web server has a different security identifier (SID) and a different key is generated, which prevents one server from being able to access data encrypted by another server.

  • If you use DPAPI with a user key and you use a roaming user profile across the machines in the Web farm, all data will share the same encryption/decryption key. However, if the domain controller responsible for the roaming user profile account is damaged or destroyed, a user account with the same SID cannot be recreated, and you cannot recover the encrypted data from the database.

    Also, with a roaming user profile, if someone manages to retrieve the data, it can be decrypted on any machine in the network, provided that the attacker can run code under the specific user account. This increases the area for potential attack, and is not recommended.

Use Separate Data Access Assemblies

If you have a choice, avoid placing data access logic directly in ASP.NET pages or in code-behind files. There are security, reuse, and maintenance advantages to placing data access logic in a separate assembly and implementing a logical data access layer that is separate from your application business and presentation logic.

From a security perspective, you can:

  • Use a strong name for the assembly, which provides tamperproofing.
  • Use sandboxing to isolate your data access code, which is important if your code needs to support partial-trust callers — for example, partial-trust Web applications.
  • Use data access methods and classes that authorize calling code using code identity permission demands.

For defense in depth, perform principal-based authorization using principal permission demands in your business components and use code identity permission demands to authorize the code that calls your data access logic, as shown in Figure 14.2.


Figure 14.2

Separation of presentation, business, and data access layers

For more information about authorization for data access code, see the "Authorization" section, later in this chapter.

Input Validation

Aside from the business need to ensure that your databases maintain valid and consistent data, you must validate data prior to submitting it to the database to prevent SQL injection. If your data access code receives its input from other components inside the current trust boundary and you know the data has already been validated (for example, by an ASP.NET Web page or business component) then your data access code can omit extensive data validation. However, make sure you use SQL parameters in your data access code. These parameters validate input parameters for type and length. The next section discusses the use of SQL parameters.

SQL Injection

SQL injection attacks can occur when your application uses input to construct dynamic SQL statements to access the database. SQL injection attacks can also occur if your code uses stored procedures that are passed strings which contain unfiltered user input. SQL injection can result in attackers being able to execute commands in the database using the application login. The issue is magnified if the application uses an overprivileged account to connect to the database.

Note   Conventional security measures, such as the use of SSL and IPSec, do not protect you against SQL injection attacks.

Preventing SQL Injection

Use the following countermeasures to prevent SQL injection attacks:

  • Constrain input.
  • Use type safe SQL parameters.

Constrain Input

Validate input for type, length, format, and range. If you do not expect numeric values, then do not accept them. Consider where the input comes from. If it is from a trusted source that you know has performed thorough input validation, you may choose to omit data validation in your data access code. If the data is from an untrusted source or for defense in depth, your data access methods and components should validate input.

When constraining input, it is a good practice to create a list of acceptable characters and use regular expressions to reject any characters that are not on the list. The potential risk associated with using a list of unacceptable characters is that it is always possible to overlook an unacceptable character when defining the list; also, an unacceptable character can be represented in an alternate format to pass validation.

Use Type Safe SQL Parameters

The Parameters collection in SQL provides type checking and length validation. If you use the Parameters collection, input is treated as a literal value and SQL does not treat it as executable code. An additional benefit of using the Parameters collection is that you can enforce type and length checks. Values outside of the range trigger an exception. This is a healthy example of defense in depth.

Important   SSL does not protect you from SQL injection. Any application that accesses a database without proper input validation and appropriate data access techniques is susceptible to SQL injection attacks.

Use stored procedures where you can, and call them with the Parameters collection.

Using the Parameters Collection with Stored Procedures

The following code fragment illustrates the use of the Parameters collection:

SqlDataAdapter myCommand = new SqlDataAdapter("AuthorLogin", conn);
myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter parm = myCommand.SelectCommand.Parameters.Add(
                       "@au_id", SqlDbType.VarChar, 11);
parm.Value = Login.Text;

In this case, the @au_id parameter is treated as a literal value and not as executable code. Also, the parameter is type and length checked. In the sample above, the input value cannot be longer than 11 characters. If the data does not conform to the type or length defined by the parameter, an exception is generated.

Note that 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. For example, the following code fragment is vulnerable:

SqlDataAdapter myCommand = new SqlDataAdapter("LoginStoredProcedure '" +
                               Login.Text + "'", conn);

Important   If you use stored procedures, make sure you use parameters.

Using the Parameters Collection with Dynamic SQL

If you cannot use stored procedures, you can still use parameters, as shown in the following code fragment:

SqlDataAdapter myCommand = new SqlDataAdapter(
"SELECT au_lname, au_fname FROM Authors WHERE au_id = @au_id", conn);
SqlParameter parm = myCommand.SelectCommand.Parameters.Add("@au_id",
                        SqlDbType.VarChar, 11);
parm.Value = Login.Text;

Using Parameter Batching

A common misconception is that if you concatenate several SQL statements to send a batch of statements to the server in a single round trip, then you cannot use parameters. However, you can use this technique if you make sure that parameter names are not repeated. You can easily do this by adding a number or some other unique value to each parameter name during SQL text concatenation.

Using Escape Routines

Another approach used to protect against SQL injection attacks is to develop escape routines that add escape characters to characters that have special meaning to SQL, such as the single apostrophe character. The following code fragment illustrates an escape routine that adds an escape character:

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

The problem with routines such as this and the reason why you should not rely on them completely is that an attacker could use ASCII hexadecimal characters to bypass your checks. However, you should use escape routines as part of your defense-in-depth strategy. Escape routines are most useful when parameterized SQL cannot be used and you are forced to use dynamic SQL instead. This is because special characters in input pose a threat only with dynamic SQL and not with parameterized SQL.

Note   Do not rely on escape routines alone. Use them in conjunction with input validation.


When your application connects to a SQL Server database, you have a choice of Windows authentication or SQL authentication. Windows authentication is more secure. 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, take additional steps to mitigate the additional risks as far as possible.

Note   Using LogonUser to create an impersonation token requires the powerful "Act as part of the operating system" privilege on Microsoft Windows 2000 and so this approach should be avoided. This is not required on Windows Server 2003.

Consider the following recommendations:

  • Use Windows authentication.
  • Protect the credentials for SQL authentication.
  • Connect using a least privileged account.

Use Windows Authentication

Windows authentication does not send credentials over the network. If you use Windows authentication for a Web application, in most cases, you use a service account or a process account, such as the ASP.NET process identity account, to connect to the database. Windows and SQL Server must both recognize the account you use on the database server. The account must be granted a login to SQL Server and the login needs to have associated permissions to access a database.

When you use Windows authentication, you use a trusted connection. The following code fragments show typical connection strings that use Windows authentication.

The example below uses the ADO.NET data provider for SQL Server:

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

The example below 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");

Protect the Credentials for SQL Authentication

If you must use SQL authentication, be sure that the credentials are not sent over the network in clear text and encrypt the database connection string because it contains credentials.

To enable SQL Server to automatically encrypt credentials sent over the network, install a server certificate on the database server. Alternatively, use an IPSec encrypted channel between the Web and database servers to secure all traffic sent to and from the database server. To secure the connection string, use DPAPI. For more information, see "Secure Your Connection String" in the "Configuration Management" section, later in this chapter.

Connect Using a Least Privileged Account

Your application should connect to the database by using a least privileged account. If you use Windows authentication to connect, the Windows account should be least privileged from an operating system perspective and should have limited privileges and limited ability to access Windows resources. Additionally, whether or not you use Windows authentication or SQL authentication, the corresponding SQL Server login should be restricted by permissions in the database.

For more information about how to create a least privileged database account and the options for connecting an ASP.NET Web application to a remote database using Windows authentication, see "Data Access" in Chapter 19, "Securing Your ASP.NET Application and Web Services."


The authorization process establishes if a user 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, and the database can perform authorization to restrict the capabilities of the SQL login used by your application.

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

  • Restrict unauthorized callers.
  • Restrict unauthorized code.
  • Restrict the application in the database.

Figure 14.3 summarizes the authorization points and techniques that should be used.


Figure 14.3

Data access authorization, assembly, and database

Notice how the data access code can use permission demands to authorize the calling user or the calling code. Code identity demands are a feature of .NET code access security.

To authorize the application in the database, use a least privileged SQL server login that only has permission to execute selected stored procedures. Unless there are specific reasons, the application should not be authorized to perform create, retrieve, update, destroy/delete (CRUD) operations directly on any table.

Note   Stored procedures run under the security context of the database system. Although you can constrain the logical operations of an application by assigning it permissions to particular stored procedures, you cannot constrain the consequences of the operations performed by the stored procedure. Stored procedures are trusted code. The interfaces to the stored procedures must be secured using database permissions.

Restrict Unauthorized Callers

Your code should authorize users based on a role or identity before it connects to the database. Role checks are usually used in the business logic of your application, but if you do not have a clear distinction between business and data access logic, use principal permission demands on the methods that access the database.

The following attribute ensures that only users who are members of the Manager role can call the DisplayCustomerInfo method:

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

If you need additional authorization granularity and need to perform role-based logic inside the data access method, use imperative principal permission demands or explicit role checks as shown in the following code fragment:

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

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

The following code fragment uses an explicit, programmatic role check to ensure that the caller is a member of the Manager role:

public void DisplayCustomerInfo(int CustId)
    . . .

Note   Additionally, if you are using ASP.NET 2.0 and you have enabled the Role Manager feature, you can use the Roles API to perform role checks. For more information on using the Role Manager feature in ASP.NET 2.0, see "How To: Use Role Manager in ASP.NET 2.0."

Restrict Unauthorized Code

By using .NET Framework code access security — specifically, code identity demands — you can limit the assemblies that can access your data access classes and methods.

For example, if you only want code written by your company or a specific development organization to be able to use your data access components, use a StrongNameIdentityPermission and demand that calling assemblies have a strong name with a specified public key, as shown in the following code fragment:

using System.Security.Permissions;
. . .
public void GetCustomerInfo(int CustId)

To extract a text representation of the public key for a given assembly, use the following command:

sn -Tp assembly.dll

Note   Use an uppercase "T" in the –Tp switch.

Because Web application assemblies are dynamically compiled, you cannot use strong names for these assemblies. This makes it difficult to restrict the use of a data access assembly to a specific Web application. The best approach is to develop a custom permission and demand that permission from the data access component. Full trust Web applications (or any fully trusted code) can call your component. Partial trust code, however, can call your data access component only if it has been granted the custom permission.

Note   Although you can compile and strong name your Web Application and its assemblies in .NET 2.0, any strong-named assembly can satisfy and pass a StrongNameIdentityPermission link demand. Therefore it is recommended that you use the custom permission demand in this situation.

For an example implementation of a custom permission, see "How To: Create a Custom Encryption Permission" in the "How To" section of this guide.

Restrict the Application in the Database

The preferred approach is to create a SQL Server login for the Windows account that the application uses to connect to the database. Then map the SQL Server login to a database user in your database. Place the database user in a user-defined database role and grant permissions to that role. Ideally, you should only grant the role execute access to the stored procedures used by the application.

For details about how to configure this approach, see "Configuring Data Access for Your ASP.NET Application in Chapter 19, "Securing Your ASP.NET Application and Web Services."

Configuration Management

Database connection strings are the main configuration management concern for data access code. Carefully consider where these strings are stored and how they are secured, particularly if they include credentials. To improve your encryption management security:

  • Use Windows authentication.
  • Secure your connection strings.
  • Secure UDL files with restricted ACLs.

Use Window Authentication

When you use Windows authentication, the credentials are managed for you and the credentials are not transmitted over the network. You also avoid embedding user names and passwords in connection strings.

Secure Your Connection Strings

If you need to use SQL authentication, then your connection contains the user name and password. If an attacker exploits a source code disclosure vulnerability on the Web server or manages to log on to the server, the attacker can retrieve the connection strings. Similarly, anyone with a legitimate login to the server can view them. Secure connection strings using encryption.

Encrypt the Connection String

Encrypt connection strings by using DPAPI. With DPAPI encryption, you avoid encryption key management issues because the encryption key is managed by the platform and is tied to either a specific computer or a Windows user account.

Note   If you are running .NET 2.0, you can use the Protected Configuration feature for encrypting the connection string. For more information on encrypting the connection string, 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."

If you are running .NET 1.1, to use DPAPI, you must call the Win32 DPAPI functions through P/Invoke.

For details on how to build a managed wrapper class, see "How To: Create a DPAPI Library" in the "How To" section of "Microsoft patterns & practices Volume I, Building Secure ASP.NET Applications: Authentication, Authorization, and Secure Communication" at

Store Encrypted Connection Strings Securely

The encrypted connection string can be placed in the registry or in the Web.config or Machine.config file. If you use a key beneath HKEY_LOCAL_MACHINE, apply the following ACL to the key:

Administrators: Full Control
Process Account: Read

Note   The process account is determined by the process in which your data access assembly runs. This is usually the ASP.NET process or an Enterprise Services server process if your solution uses an Enterprise Services middle tier.

Alternatively you can consider using HKEY_CURRENT_USER, which provides restricted access. For more information, see the "Registry" section in Chapter 7, "Building Secure Assemblies."

Note   If you use the Visual Studio.NET database connection Wizards, the connection strings are stored either as a clear text property value in the Web application code-behind file or in the Web.config file. Both of these approaches should be avoided.

Although it is potentially less secure than using a restricted registry key, you may want to store the encrypted string in the Web.config for easier deployment. In this case, use a custom <appSettings> name-value pair as shown below:

<?xml version="1.0" encoding="utf-8" ?>
   <add key="connectionString" value="AQA..bIE=" />

To access the cipher text from the <appSettings> element, use the ConfigurationSettings class as shown below:

using System.Configuration;
private static string GetConnectionString()
  return ConfigurationSettings.AppSettings["connectionString"];

Do Not Use Persist Security Info='True' or 'Yes'

When you include the Persist Security Info attribute in a connection string, it causes the ConnectionString property to strip out the password from the connection string before it is returned to the user. The default setting of false (equivalent to omitting the Persist Security Info attribute) discards the information once the connection is made to the database.

Secure UDL Files with Restricted ACLs

If your application uses external universal data link (UDL) files with the ADO.NET managed data provider for OLE DB, use NTFS permissions to restrict access. Use the following restricted ACL:

Administrators: Full Control
Process Account: Read

Note   UDL files are not encrypted. A more secure approach is to encrypt the connection string using DPAPI and store it in a restricted registry key.

Sensitive Data

Many Web applications store sensitive data of one form or another in the database. If an attacker manages to execute a query against your database, it is imperative that any sensitive data items — such as credit card numbers — are suitably encrypted.

  • Encrypt sensitive data if you need to store it.
  • Secure sensitive data over the network.
  • Store password hashes with salt.

Encrypt Sensitive Data if You Need to Store It

Avoid storing sensitive data if possible. If you must store sensitive data, encrypt the data.

Note   The .NET Framework 2.0 provides support for Advanced Encryption Standard (AES), a symmetric encryption algorithm, in addition to DES and 3DES. It is recommended that you use a strong symmetric encryption algorithm such as 3DES or AES.

Using 3DES Encryption

To store sensitive data, such as credit card numbers, in the database, use a strong symmetric encryption algorithm such as 3DES.

During development, to enable 3DES encryption

  1. Use the RNGCryptoServiceProvider class to generate a strong (192 bit, 24 byte) encryption key.

  2. Back up the encryption key, and store the backup in a physically secure location.

  3. Encrypt the key with DPAPI and store it in a registry key. Use the following ACL to secure the registry key:

    Administrators: Full Control
    Process Account (for example ASPNET): Read

At runtime, to store encrypted data in the database

  1. Obtain the data to be encrypted.
  2. Retrieve the encrypted encryption key from the registry.
  3. Use DPAPI to decrypt the encryption key.
  4. Use the TripleDESCryptoServiceProvider class with the encryption key to encrypt the data.
  5. Store the encrypted data in the database.

At runtime, to decrypt the encrypted secrets

  1. Retrieve the encrypted data from the database.
  2. Retrieve the encrypted encryption key from the registry.
  3. Use DPAPI to decrypt the encryption key.
  4. Use the TripleDESCryptoServiceProvider class to decrypt the data.

With this process, if the DPAPI account used to encrypt the encryption key is damaged, the backup of the 3DES key can be retrieved from the backup location and be encrypted using DPAPI under a new account. The new encrypted key can be stored in the registry and the data in the database can still be decrypted.

Note   Using DPAPI is easier in .NET 2.0 because the ProtectedData class now provides a managed wrapper to DPAPI, For more information, see ".NET Framework Class Library - ProtectedData Class."

If you are running .NET 1.1, you need create a managed DPAPI library. For more information, see "How To: Create a DPAPI Library" in the "How To" section of "Microsoft patterns & practices Volume I, Building Secure ASP.NET Applications: Authentication, Authorization, and Secure Communication" at

Secure Sensitive Data Over the Network

Sensitive data passed across the network to and from the database server may 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 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.

For more information about using SSL and IPSec, see "How To: Use IPSec to Provide Secure Communication Between Two Servers" and "How To: Use SSL to Secure Communication to SQL Server 2000" in the "How To" section of "Microsoft patterns & practices Volume I, Building Secure ASP.NET Applications: Authentication, Authorization, and Secure Communication" at

Store Password Hashes with Salt

If you need to implement a user store that contains user names and passwords, do not store the passwords either in clear text or in encrypted format. Instead of storing passwords, store non-reversible hash values with added salt to mitigate the risk of dictionary attacks.

Note   A salt value is a cryptographically strong random number.

Creating a Salt Value

The following code shows how to generate a salt value by using random number generation functionality provided by the RNGCryptoServiceProvider class within the System.Security.Cryptography namespace.

public static string CreateSalt(int size)
  RNGCryptoServiceProvider rng = new RNGCryptoServiceProvider();
  byte[] buff = new byte[size];
  return Convert.ToBase64String(buff);

Creating a Hash Value (with Salt)

The following code fragment shows how to generate a hash value from a supplied password and salt value.

public static string CreatePasswordHash(string pwd, string salt)
  string saltAndPwd = string.Concat(pwd, salt);
  string hashedPwd =
                                             saltAndPwd, "SHA1");
  return hashedPwd;

More Information

For more information about implementing a user store that stores password hashes with salt, see "How To: Use Forms Authentication with SQL Server 2000" in the "How To" section of "Microsoft patterns & practices Volume I, Building Secure ASP.NET Applications: Authentication, Authorization, and Secure Communication" at

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 in addition to valuable connection details. The following recommendations apply to data access code:

  • Trap and log ADO.NET exceptions.
  • Ensure database connections are always closed.
  • Use a generic error page in your ASP.NET applications.

Trap and Log ADO.NET Exceptions

Place data access code within a try / catch block and handle exceptions. When you write ADO.NET data access code, the type of exception generated by ADO.NET depends on the data provider. For example:

  • The SQL Server .NET Framework data provider generates SqlExceptions.
  • The OLE DB .NET Framework data provider generates OleDbExceptions.
  • The ODBC .NET Framework data provider generates OdbcExceptions.

Trapping Exceptions

The following code uses the SQL Server .NET Framework data provider and shows how you should catch exceptions of type SqlException.

  // Data access code
catch (SqlException sqlex) // more specific
catch (Exception ex) // less specific

Logging Exceptions

You should also log details from the SqlException class. This class exposes properties that contain details of the exception condition. These include a Message property that describes the error, a Number property that uniquely identifies the type of error, and a State property that contains additional information. The State property is usually used to indicate a particular occurrence of a specific error condition. For example, if a stored procedure generates the same error from more than one line, the State property indicates the specific occurrence. Finally, an Errors collection contains SqlError objects that provide detailed SQL server error information.

The following code fragment shows how to handle a SQL Server error condition by using the SQL Server .NET Framework data provider:

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

// Method exposed by a Data Access Layer (DAL) Component
public string GetProductName( int ProductID )
  SqlConnection conn = new SqlConnection(
        "server=(local);Integrated Security=SSPI;database=products");
  // Enclose all data access code within a try block
    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;

    // 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
    // Wrap the current exception in a more relevant
    // outer exception and re-throw the new exception
    throw new Exception(
                  "Failed to retrieve product details for product ID: " +
                   ProductID.ToString(), sqlex );

    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 );

Ensure Database Connections Are Closed

If an exception occurs, it is essential that database connections are closed and any other limited resources are released. Use finally blocks, or the C# using statement to ensure that connections are closed whether an exception condition occurs or not. The above code illustrates the use of the finally block. You can also use the C# using statement, as shown below:

using ((SqlConnection conn = new SqlConnection(connString)))
  // Connection will be closed if an exception is generated or if control flow
  // leaves the scope of the using statement normally

Use a Generic Error Page in Your ASP.NET Applications

If your data access code is called by an ASP.NET Web application or Web service, you should configure the <customErrors> element to prevent exception details propagating back to the end user. You can also specify a generic error page by using this element, as shown below.

<customErrors mode="On" defaultRedirect="YourErrorPage.htm" />

Set mode="On" for production servers. Only use mode="Off" when you are developing and testing software prior to release. Failure to do so results in rich error information, such as that shown in Figure 14.4, being returned to the end user. This information can include the database server name, database name, and connection credentials.


Figure 14.4

Detailed exception information revealing sensitive data

Figure 14.4 also shows a number of vulnerabilities in the data access code near the line that caused the exception. Specifically:

  • The connection string is hard-coded.
  • The highly privileged sa account is used to connect to the database.
  • The sa account has a weak password.
  • The SQL command construction is susceptible to SQL injection attack; the input is not validated, and the code does not use parameterized stored procedures.

Building a Secure Data Access Component

The following code shows a sample implementation of a CheckProductStockLevel method used to query a products database for stock quantity. The code illustrates a number of the important security features for data access code introduced earlier in this chapter.

Note   If you are running .NET 2.0, you can use the Protected Configuration feature in .NET 2.0 to encrypt the connection string in the configuration file. You do not need any code for decrypting the connection string because the .NET runtime does this for you. For more information on encrypting connection strings, 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."

using System;
using System.Data;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
using System.Collections.Specialized;
using Microsoft.Win32;
using DataProtection;

public static int CheckProductStockLevel(string productCode)
  int quantity = 0;
  // (1) Code protected by try/catch block
    // (2) Input validated with regular expression
    //     Error messages should be retrieved from the resource assembly to help
    //     localization. The Localization code is omitted for the sake of brevity.
    if (Regex.IsMatch(productCode, "^[A-Za-z0-9]{12}$") == false)
      throw new ArgumentException("Invalid product code" );
    //(3) The using statement ensures that the connection is closed
    using (SqlConnection conn = new SqlConnection(GetConnectionString()))
      // (4) Use of parameterized stored procedures is a countermeasure for
      //     SQL injection attacks
      SqlCommand cmd = new SqlCommand("spCheckProduct", conn);
      cmd.CommandType = CommandType.StoredProcedure;

// Parameters are type checked
      SqlParameter parm =
      parm.Value = productCode;
      // Define the output parameter
      SqlParameter retparm = cmd.Parameters.Add("@quantity", SqlDbType.Int);
      retparm.Direction = ParameterDirection.Output;
      quantity = (int)retparm.Value;
  catch (SqlException sqlex)
    // (5) Full exception details are logged. Generic (safe) error message
    //     is thrown back to the caller based on the SQL error code
    //     Log and error identification code has been omitted for clarity
    throw new Exception("Error Processing Request");
  catch (Exception ex)
    // Log full exception details
    throw new Exception("Error Processing Request");
  return quantity;

// (6) Encrypted database connection string is held in the registry
private static string GetConnectionString()
  // Retrieve the cipher text from the registry; the process account must be
  // granted Read access by the key's ACL
  string encryptedString = (string)Registry.LocalMachine.OpenSubKey(
  // Use the managed DPAPI helper library to decrypt the string
  DataProtector dp = new DataProtector(DataProtector.Store.USE_MACHINE_STORE);
  byte[] dataToDecrypt = Convert.FromBase64String(encryptedString);
  return Encoding.ASCII.GetString(dp.Decrypt(dataToDecrypt,null));

The code shown above exhibits the following security characteristics (identified by the numbers in the comment lines).

  1. The data access code is placed inside a try/catch block. This is essential to prevent the return of system level information to the caller in the event of an exception. The calling ASP.NET Web application or Web service might handle the exception and return a suitably generic error message to the client, but the data access code does not rely on this.

  2. Input is validated using a regular expression. The supplied product ID is checked to verify that it contains characters in the range A–Z and 0–9 only, and does not exceed 12 characters. This is the first in a set of countermeasures designed to prevent SQL injection attacks.

  3. The SqlConnection object is created inside a C# using statement. This ensures that the connection is closed inside the method regardless of whether an exception occurs. This mitigates the threat of denial of service attacks, which attempt to use all available connections to the database. You can achieve similar functionality by using a finally block.

  4. Parameterized stored procedures are used for data access. This is another countermeasure to prevent SQL injection.

  5. Detailed error information is not returned to the client. Exception details are logged to assist with problem diagnosis.

  6. The Encrypted database connection string is stored in the registry. One of the most secure ways of storing database connection strings is to use DPAPI to encrypt the string and store the encrypted cipher text in a secured registry key that has a restricted ACL. (For example, use Administrators: Full Control and ASP.NET or Enterprise Services process account: Read, depending on which process hosts the component.)

    Other options are discussed in the "Database Connection Strings" section of this chapter.

    Note   The code shows how to retrieve the connection string from the registry and then decrypt it using the managed DPAPI helper library. This library is provided in "How To: Create a DPAPI Library" in the "How To" section of "Microsoft patterns & practices Volume I, Building Secure ASP.NET Applications: Authentication, Authorization, and Secure Communication" at

Code Access Security Considerations

All data access is subject to code access security permission demands. Your chosen ADO.NET managed data provider determines the precise requirements. The following table shows the permissions that must be granted to your data access assemblies for each ADO.NET data provider.

Table 14.1   Code Access Security Permissions Required by ADO.NET Data Providers

ADO.NET Data Provider Required Code Access Security Permission
SQL Server SqlClientPermission

Supports partial trust callers including Medium trust Web applications.

OLE DB OleDbPermission*
Oracle OraclePermission*
ODBC OdbcPermission*

*The OLE DB, Oracle, and ODBC providers support only Full trust callers on versions 1.0 and 1.1 of the .NET Framework. To use these providers from partial trust Web applications, you must sandbox your data access code, which necessitates a dedicated data access assembly. For an example that shows how to sandbox data access code and use the OLE DB data provider from a Medium trust Web application see Chapter 9, "Using Code Access Security with ASP.NET."

Note   In .NET 2.0, the Oracle, OLE DB, and the ODBC .NET data providers no longer demand full trust. This allows you to access SQL Server and other databases from partial-trust applications. To use these providers from a partial-trust Web application, you need to customize the policy and grant the appropriate permission; for example, OleDbPermission, OraclePermission, or OdbcPermission. For more information on customizing trust-level policy, see "How To: Use Code Access Security in ASP.NET 2.0."

If you use the ADO.NET SQL Server data provider, your code must be granted the SqlClientPermission by code access security policy. High and Medium trust Web applications have this permission.

Whether or not code is granted the SqlClientPermission determines whether or not the code can connect to SQL Servers. You can also use the permission to place restrictions on the use of database connection strings. For example, you can force an application to use integrated security or you can ensure that if SQL Server security is used then blank passwords are not accepted. Violations of the rules you specify through the SqlClientPermission result in runtime security exceptions.

For more information about how to use SqlClientPermission to constrain data access, see "Data Access" in Chapter 8, "Code Access Security in Practice."

Deployment Considerations

A securely designed and developed data access component 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. Developers and administrators, be aware of the following issues:

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

Firewall Restrictions

If you connect to SQL Server through a firewall, configure the firewall, client, and server. You configure the client by using the SQL Server Client Network Utility and you 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. For more information about port requirements, see the articles, TCP and UDP Port Assignments, and Security Considerations for Administrative Authority.

  • If your application uses distributed transactions, for example automated COM+ transactions, you might also 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.

For full configuration details, see the "Step 7. Ports" section in Chapter 18, Securing Your Database Server.

Connection String Management

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. Using external files to store connection strings is superior for system administration.

For increased security, the recommended approach is to use DPAPI to encrypt the connection string. This is particularly important if your connection string contains user names and passwords. Then, decide where to store the encrypted string. The registry is a secure location particularly if you use HKEY_CURRENT_USER, because access is limited to processes that run under the associated user account. An alternative for easier deployment is to store the encrypted string in the Web.config file. Both approaches were discussed in the "Configuration Management" section earlier in this chapter.

Login Account Configuration

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 along with the application.

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

See the "Authorization" section earlier in this chapter for the recommended authorization strategy for the application account in the database.

Logon Auditing

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.

For more information about how to configure SQL Server auditing, see Chapter 18, "Securing Your Database Server."

Data Privacy and Integrity on the Network

If you use SQL authentication to connect to SQL Server, ensure 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.

The use of IPSec or SSL to the database is recommended to protect sensitive application level data passed to and from the database. For more information, see Chapter 18, "Securing Your Database Server."


This chapter showed the top threats to data access code and highlighted the common vulnerabilities. SQL injection is one of the main threats to be aware of. Unless you use the correct countermeasures discussed in this chapter, an attacker could exploit your data access code to run arbitrary commands in the database. Conventional security measures such as firewalls and SSL provide no defense to SQL injection attacks. You should thoroughly validate your input and use parameterized stored procedures as a minimum defense.

Additional Resources

For more information, see the following resources:

  • For a printable checklist, see "Checklist: Securing Data Access" in the "Checklists" section of this guide.
  • For information on securing your developer workstation, see "How To: Secure Your Developer Workstation" in the "How To" section of this guide.
  • For information on using SSL with SQL Server, see "How To: Use SSL to Secure Communication with SQL Server 2000," in the "How To" section of "Microsoft patterns & practices Volume I, Building Secure ASP.NET Applications: Authentication, Authorization, and Secure Communication" at
  • For information on using IPSec, see "How To: Use IPSec to Provide Secure Communication Between Two Servers" in the "How To" section of "Microsoft patterns & practices Volume I, Building Secure ASP.NET Applications: Authentication, Authorization, and Secure Communication" at
  • For information on using DPAPI, see "How To: Create a DPAPI Library" in the "How To" section of "Microsoft patterns & practices Volume I, Building Secure ASP.NET Applications: Authentication, Authorization, and Secure Communication" at

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.

© Microsoft Corporation. All rights reserved.