ADO.NET 2.0 Security Guidelines - Deployment Considerations

From Guidance Share

Jump to: navigation, search

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


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

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.

Personal tools