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.