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 TechNet articles, "TCP and UDP Port Assignments," at http://www.microsoft.com/technet/prodtechnol/windows2000serv/reskit/tcpip/part4/tcpappc.asp , and "Security Considerations for Administrative Authority," at http://www.microsoft.com/technet/security/bestprac/bpent/sec2/seconaa.asp

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