ADO.NET 1.1 Security Guidelines - Design Considerations

From Guidance Share

Jump to: navigation, search

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


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


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.

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.


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


Personal tools