Secure Multi-tier Deployment

Updated : May 16, 2003

SQL Server is often deployed as part of a multi-tier system of applications that communicate among themselves over a network. The simplest version of such installations includes three layers:

• Web Server — User-facing Web application or client.

• Application Server — Business logic, pre-processing of batches prior to transmission to database; may include Web server (ASP.NET/IIS). This layer is also known as "the middle tier."

• Database Server — Database (SQL Server). This layer is sometimes called "the back end."

Figure 4 shows security vulnerabilities in a simple multi-tier system.

Figure 4: Simple multi-tier system, indicating major points of vulnerability

A full discussion of security for such complex systems is beyond the scope of this paper. For more information on n-tier deployment, see Building Secure ASP.NET Applications: Authentication, Authorization, and Secure Communication.

Master/Target Servers

In a master/target (MSX/TSX) relationship there must be at least one master server and at least one target server. The master server distributes jobs to its target servers. The master server stores the central (authoritative) copy of job definitions for jobs that run on its target servers. The target servers connect periodically to their master server to update their list of pending jobs. When configuring a server to operate as a master or target, observe the following best practices.

• MSX/TSX should only be deployed inside your corporate firewall. Never expose a server running as a master or a target directly to the Internet.

• Run the MSX SQL Server Agent as a Windows domain user without Windows administrator privileges. Enterprise Manager assigns the correct privileges when it designates an account as the SQL Agent service account.

  Note: If you include ActiveX® script or CmdExec jobs in your batches, SQL Agent must be a member of the Windows administrator group.

If an MSX server was upgraded from a version of SQL Server prior to SP3, it may retain some jobs owned by old SQL Server Agent probe accounts (<computer_name>_msx_probe_login). You should assign these jobs to new owners and manually remove the unneeded accounts.

SSL and IPSec

To defend the privacy and integrity of data transmitted across a network, you should use either IPSec encrypted communication channels or SSL connections to the database. Figure 5 shows the mapping of OSI layers to the TCP/IP stack.

Figure 5:

IPSec is a network-level encryption and authentication protocol designed to protect network traffic from address spoofing, eavesdropping, and session hijacking. Since it operates near the bottom of the OSI stack, it is invisible to SQL Server. No SQL Server-level configuration is required to use IPSec, but an understanding of public and private keys will be helpful in planning your deployment. See Public/Private Key Pairs.

SSL works at the boundary between the Session and Transport layers of the OSI model, allowing clients and servers to negotiate shared session keys. It provides encryption but not authentication. SSL communication is only possible between Application layers that have been specifically designed to use this protocol. Client applications running on top of the MDAC stack (version 2.6 or above) do not need to be SSL-aware, as this functionality is supplied by MDAC.

A best practice is to force connections that are capable of using SSL to do so. This is especially important when using Mixed-Mode Authentication. To force encryption on SSL-aware applications that connect to your server, apply an SSL certificate to the server and select the Force Protocol Encryption option on enabled protocols in the Server Network Utility. For more information about implementing SSL and IPSec, including additional configuration options, see How To: Use IPSec to Provide Secure Communication Between Two Servers and How To: Use SSL to Secure Communication with SQL Server 2000.

Authentication

Application tiers that exist within a single Windows domain should always use Windows authentication when communicating with each other.

Use Window Authentication

In this authentication mode, credentials are managed for you and 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 Server Authentication, then your connection contains username and password information. If an attacker exploits a source code disclosure vulnerability on the Web server, or manages to log in to the server, he is able to retrieve the connection strings. Similarly, anyone with a legitimate login to the server is able to view them. Secure connection strings using encryption.

Client and middle tier applications should connect to SQL Server using Windows Authentication whenever possible. When forced to use a less secure authentication mode, application designers should insure that SQL Server credentials are handled with appropriate care. All connections should be made with an account with the least privileges necessary.

Use Windows Authentication to Avoid Credential Management

Windows Authentication does not send credentials over the network. This account must be recognized on the database server by both Windows and SQL Server. It should be granted a SQL Server login with only those permissions needed to access the database. These precautions will limit the scope of damage if your system is compromised by hackers. The following examples illustrate a typical connection string that uses Windows Authentication.

Using the ADO.NET data provider for SQL Server:

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

Using 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 Server Authentication

If you must use SQL Server Authentication, make sure the credentials are not sent over an unencrypted channel in plain text. You must also encrypt the database connection string before storing it, because it contains credentials. To secure the connection string, use DPAPI. For more information, see "Storing Credentials" later in this paper.

To enable SQL Server to automatically encrypt the credentials sent over a network, install a server certificate on the database server. Alternatively, use an IPSec encrypted channel between Web and database server.

Connect Using a Least Privilege Account

Your application should connect to the database using a least privileged account. If you connect using Windows Authentication, 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 you use Windows Authentication or SQL Server Authentication to the database, the corresponding SQL Server login in the database should be assigned only those permissions required by your application.

Authorization

The authorization process establishes what permissions have been granted to a user. There are two lines of defense. Your data access code can perform authorization to determine whether or not to perform the requested operation. Once the calling identity or calling code has been authorized, a command is issued to the database. The database now performs authorization to restrict the capabilities of the SQL Server login used by the application.

With inadequate authorization, users may be able to alter the data of other users, and unauthorized users may be able to gain access to restricted data. To mitigate this threat, you should apply the defense-in-depth security principle (multiple levels of security) to your data access authorization strategy. Specifically:

• Restrict unauthorized callers.

• Restrict unauthorized code.

• Restrict the application in the database.

Figure 6 shows defense-in-depth of user data.

Figure 6:

Note that the data access code can use principal permission demands to authorize the calling principal or the calling code. Code identity demands are a feature of common language runtime code access security.

At the SQL Server level, you should create a least-privileged SQL Server login authorized only to execute stored procedures. Users should not be authorized perform Create, Read, Update, Delete (CRUD) operations directly on any table.

Restrict Unauthorized Callers

Middle tier applications should authorize users based on their role or identity prior to connecting to the back-end database. Role checks are usually implemented in the business logic of your application, but if you do not make 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 a member 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 below:

public void DisplayCustomerInfo(int CustId) 
{ 
  try 
  { 
    // Imperative principal permission role check to check 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 uses an explicit, programmatic role check to ensure that the caller is a manager.

public void DisplayCustomerInfo(int CustId) 
{ 
  if(!Thread.CurrentPrincipal.IsInRole("Manager")) 
  { 
    . . . 
  } 
} 

Restrict Unauthorized Code

By using ASP.NET code access security and specifically code identity demands, you can limit which other assemblies 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 an instance of the StrongNameIdentityPermission class to verify that calling assemblies have a strong name with a specified public key.

using System.Security.Permissions; 
. . . 
[StrongNameIdentityPermission(SecurityAction.LinkDemand, 
                              PublicKey="002...4c6")] 
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 

Because Web application assemblies are compiled dynamically, there is currently no way to give them strong names. 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) will then be able to call your component. Partial trust code however, will only able to call your data access component if it has been granted the custom permission.

Remote Administration

Administrators often need to be able to administer multiple servers. Make sure the requirements of your remote administration solution do not compromise security. The following should be considered best practices:

• Restrict the number of Windows administration accounts. This includes restricting the number of administration accounts as well as restricting which accounts are allowed to log on remotely.

• Restrict the tools. The main options for Windows are Internet Services Manager and Terminal Services. Web administration (using the IISAdmin virtual directory) is not recommended and this option is removed by IISLockdown.exe. Both Internet Services Manager and Terminal Services use Windows security. SQL Server Enterprise Manager can be used for remote SQL Server administration. If you are using SQL Server Authentication and need to store your credentials, you should configure Enterprise Manager to restrict access to a single Windows user by selecting Read/Store User Independent in the General tab.

• Restrict the computers that are allowed to administer the server. IPSec can be used to restrict which computers can connect to your Web server.

Securing Terminal Services

It is possible to use Microsoft Terminal Services securely, to remotely administer your database server.

Terminal Services is based on the proprietary Microsoft protocol known as Remote Desktop Protocol (RDP). RDP uses TCP 3389 port and supports two concurrent users.

Installing Terminal Services

1. In Control Panel, select Add or Remove Programs. Select the Add/Remove Windows Components option. You do not need to install the Terminal Services Licensing service for remote administration.

2. Configure Terminal Services for remote administration mode.

3. Remove the TsInternetUser account that is created during Terminal Services installation. This account is used to support anonymous Internet access to Terminal Services, which should not be enabled on a server.

Configuring Terminal Services

In the Administrative Tools program group, use the Terminal Services configuration MMC snap-in to configure the following:

1. Three levels (Low, Medium, and High) of encryption are available for connections to Terminal Services. Set the encryption to 128bit key. Note that the Windows high encryption pack should be installed on both the server and the client.

2. Configure the Terminal Services session to disconnect after idle connection time limit. Set it to end a disconnected session. Sessions disconnect after ten minutes if the user closes the Terminal Services client application without logging off.

3. Use the RDP permissions tab in the RDP dialog box to restrict access to Terminal Services. By default, all members of the Administrators group are allowed to access Terminal Services. If you do not want all members of the Administrators group to access Terminal Services, then remove the group and add individual accounts that need access. Note that the SYSTEM account must be in the list.

4. Use a secure VPN connection between the client and the server or an IPsec tunnel for enhanced security. This approach provides mutual authentication and the RDS payload is encrypted.

Copying Files over RDP

Terminal Services does not provide built-in support for file transfer. However, you can install the File Copy utility from the Windows 2000 Server Resource Kit to add file transfer functionality to the clipboard redirection feature in Terminal Services. For more information about the utility and installation instructions, see Microsoft Knowledge Base article 244732, "How To: Install the File Copy Tool Included with the Windows 2000 Resource Kit".

Stored Procedures vs. Dynamic SQL

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

• Restricting the application database user to execute only the specified stored procedures. There is no need to grant direct table access. For more information, see the discussion of ownership chaining earlier in this paper.

• Performing length and type checks on all items of input data passed to the stored procedure. Also, parameters cannot be treated as executable code.

If you cannot use parameterized stored procedures 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.

Use Separate Data Access Assemblies

If your application will be deployed in a multi-tier environment, you should avoid exposing data access logic directly to end-users. In ASP.NET, for example, data access logic should be deployed in its own assembly, separate from the business and presentation logic. Give the assembly a strong name, to reduce vulnerability to tampering. A strong name consists of the assembly's identity — its simple text name, version number, and culture information (if provided) — plus a public key and a digital signature. It is generated from an assembly file (the file that contains the assembly manifest, which in turn contains the names and hashes of all the files that make up the assembly), using the corresponding private key. Microsoft Visual Studio .NET® and other development tools provided in the .NET Framework SDK can assign strong names to an assembly. Assemblies with the same strong name are expected to be identical. The public key component of the strong name will also be useful for configuring code access security policy and granting specific permissions to the assembly, enabling data access methods and classes to authorize calling code.

Figure 7 depicts such a layered defense of a server. It performs principal-based authorization using principal permission demands on business components. It also uses code identity permission demands to authorize the code that calls data access logic.

Figure 7:

SQL Injection

SQL Injection is an attack in which malicious code is inserted into strings that are later passed to SQL Server for parsing and execution. Any client-side application that returns SQL statements exposes the server that trusts it to such injection attacks, because the server will execute all syntactically valid statements it receives. Injection is easiest when applications construct SQL statements from user input. It is also possible when the client passes user input to server-side stored procedures. The danger to your server is magnified if the application connects with an over-privileged account.

Example of SQL Script Injection

This ASP script builds a SQL query by concatenating hard-coded strings together with a string entered by the user:

var Shipcity; 
ShipCity = Request.form ("ShipCity"); 
var sql = "select * from OrdersTable where ShipCity = '" + ShipCity + "'"; 

The user is prompted to enter the name of a city. If she enters Redmond, the query assembled by the script looks like this:

select * from OrdersTable where ShipCity = 'Redmond' 

But what if the user enters the following?

'Redmond'; drop table OrdersTable--

In that case, the query assembled by the script looks like this:

select * from OrdersTable where ShipCity = 'Redmond';drop table OrdersTable-- 

The ';' character denotes the end of one query and the beginning of another. And the '--' character sequence means the rest of the current line is a comment, and should be ignored. The user has used your client code to inject a string into the statement ASP will return to SQL Server. When it processes this statement, SQL Server will first select all records in OrdersTable where ShipCity is Redmond. Then it will drop OrdersTable.

Preventing SQL Injection

So long as injected SQL code is syntactically correct, it will be impossible to programmatically detect tampering on the server side. You must therefore validate all user input on the client side, and force server-side type checking by calling parameterized stored procedures. Always validate user input by testing type, length, format, and range. Untested input can cause program errors, and may be used by hackers as a point of entry into your system. When implementing precautions against malicious input, consider the architecture and deployment scenarios of your application. Remember that programs designed to run in a secure environment can be copied to an insecure environment.

Validate All Input

The following suggestions should be considered best practices:

• Make no assumptions about the size, type, or content of the data received by your application. For example, evaluate:

  • How will your application behave if an errant, or malicious, user enters a 10-megabyte MPEG file where your application expects a postal code?

  • How will your application behave if a DROP TABLE statement is embedded in a text field?

• Test the size and data type of input, and enforce appropriate limits. This can help prevent deliberate buffer overruns.

• Test the content of string variables and accept only expected values. Reject entries containing binary data, escape sequences, and comment characters. This can help prevent script injection and can protect against some buffer overrun exploits.

• When working with XML documents, validate all data against its schema as it is entered.

• Never build Transact-SQL statements directly from user input.

• Use stored procedures to validate user input.

• In multi-tiered environments, all data should be validated before admission to the trusted zone. Data that does not pass the validation process should be rejected, and an error returned to the previous tier.

• Implement multiple layers of validation. Precautions you take against casually malicious users may be ineffective against expert hackers. The best practice is to validate input in the user interface, and then at all subsequent points at which it crosses a trust boundary.

  For example, data validation in a client-side application may prevent simple script injection; however, if the next tier assumes that its input has already been validated, any hacker capable of bypassing your client can have unrestricted access to your system.

• Never concatenate user input that is not validated. String concatenation is the primary point of entry for script injection.

• Do not accept the following strings in fields from which file names may be constructed: AUX, CLOCK$, COM1 through COM8, CON, CONFIG$, LPT1 through LPT8, NUL, and PRN.

When possible, reject input that contains the following potentially dangerous characters.

Input character	Meaning in Transact-SQL
;	Query delimiter
'	Character data string delimiter
--	Comment delimiter
/* ... */	Comment delimiters. Text between /* and */ is not evaluated by the server.
Xp_	Begins the name of catalog extended stored procedures such as xp_cmdshell.

Use Type-Safe SQL Parameters

The Parameters collection in SQL Server provides type checking and length validation. If you use the Parameters collection, input is treated as a literal value rather than executable code. An additional benefit of using the Parameters collection is that you can enforce type and length checks. Values outside of the range will trigger an exception. The following code fragment illustrates using 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 example, the @au_id parameter is treated as a literal value rather than executable code. This value is checked for type and length. If the value of @au_id does not conform to the specified type and length constraints, an exception will be thrown.

Use Parameterized Input with Stored Procedures

Stored procedures may be susceptible to SQL injection if they use unfiltered input. For example, the following code is vulnerable:

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

If you use stored procedures, you should use parameters as their input.

Use the Parameters Collection with Dynamic SQL

If you cannot use stored procedures, you can still use parameters, as shown below.

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; 

Filtering Input

Filtering input may also be helpful in protecting against SQL injection by removing escape characters, but due to the large number of characters that may pose problems it is not a reliable defense. The following snippet searches for the character string delimiter.

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

LIKE Clauses

Note that if you are using a LIKE clause, wildcard characters still need to be escaped:

s = s.Replace("[", "[[]"); 
s = s.Replace("%", "[%]"); 
s = s.Replace("_", "[_]"); 

Parameter Batching

It is a common misconception that parameters cannot be used when multiple SQL statements are concatenated for batched transmission to the server. In fact, parameters can be used, so long as parameter names are not repeated. That can be easily achieved by adding a number or some other unique value to each parameter name during SQL text concatenation.

Storing Credentials

Avoid storing credentials in any form. The best practice is to use only Windows Authentication and never handle credentials at all. If your application must connect with a system outside a trusted domain, however, credential management may become necessary. In this case, the best practice is to encrypt the credentials using the DPAPI and save them in a registry key that uses an ACL. You can use regedt32.exe to apply the following ACL to the key:

Administrators: Full Control 
Process Account: Read 

With DPAPI encryption, you avoid encryption key management issues. This is because the encryption key is managed by the platform and is tied to either a specific machine or a Windows user account. For more information on DPAPI, see Windows Data Protection.

You may need to create a managed wrapper class to handle DPAPI encryption. For details about how to build a managed wrapper class, see How To: Create a DPAPI Library.

Although potentially less secure than using a secured registry key, ASP.NET applications may need to store the encrypted string in web.config. In this case, use a custom <appSettings> name-value pair as shown below:

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

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"]; 
} 

If your application stores connection information in a UDL file, use NTFS permissions to restrict access. Use the following restricted ACL.

Administrators: Full Control 
Process Account: Read 

UDL files are not encrypted. They may contain credentials in plain text. If your application forces users to store credentials in a UDL you should redesign it to use more secure practices.

Persist Security Info Property

Never set the Persist_Security_Info property of OLE DB connection strings to "true" or "yes". When you include this 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" discards this information once the connection is made to the database. For more information, see Persist Security Info Property.

Encrypting User Data

If you store sensitive user-supplied data such as credit card numbers, encrypt it with a strong symmetric encryption algorithm such as Triple DES (3DES). Encrypt the 3DES encryption key using the Data Protection API (DPAPI), and store the encrypted key in a registry key with an ACL that grants access only to administrators and your application process account. The basic approach is outlined below.

At development time, perform the following tasks:

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. Secure the registry key with the following ACL:

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

To encrypt data for storage in the database, do the following:

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.

To decrypt the encrypted data, do the following:

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: Information encrypted in this way cannot be indexed.

Verify Passwords with a One-Way Hash

If you need to verify passwords at the server, consider storing only a hash of the password rather than the password itself. Hashing algorithms map binary values of arbitrary length to small binary values of fixed length. The resulting hash value, also known as a "one-way hash," is a compact representation of the data from which it was generated — a digital fingerprint. Storing the hash of a password is safer than storing the password itself, or even its encrypted equivalent, because hashing is fully deterministic in only one direction.

For more information, see Windows Data Protection, and Using Credential Management in Windows XP and Windows Server 2003.

Exception Management

Do not return unprocessed error codes to the user, as they may contain too much information about the structure and contents of your database and its authentication mechanisms. Without proper exception management, error conditions caused by misconfiguration, bugs in your code, or malicious input may reveal connection strings, database meta data, SQL code fragments, and raw data not intended for end users. Error messages may also include such information as software versions and configuration details. Such disclosures assist hackers in profiling your system but add little value for users.

Trap and Log Exceptions

Use try and catch commands around data access code to trap and log exceptions. Connection attempts that fail should always fail gracefully, logging appropriate information in a file secured with an ACL. In addition, connections should always be explicitly closed when they are no longer needed.

The following examples illustrate these best practices when using ADO.NET. Note that the type of exception generated by ADO.NET depends on the data provider.

• The .NET Framework Data Provider for SQL Server generates SqlException objects.

• The .NET Framework Data Provider for OLE DB generates OleDbException objects.

• The .NET Framework Data Provider for ODBC generates OdbcException objects.

This code uses the SQL Server .NET Framework data provider to trap exceptions of type SqlException:

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

Details of database access errors are exposed through the properties of the SqlException class. These include a message property that describes the error, a number property that uniquely identifies the type of error, a state property that contains additional information. This 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 a set of 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 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 
  try 
  { 
    conn.Open(); 
    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; 
    cmd.ExecuteNonQuery(); 
    // 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 
    LogException(sqlex); 
    // 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 ); 
  } 
  finally 
  { 
    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 ); 
  } 
} 

Close All Unneeded Database Connections

If an error occurs, it is essential to close database connections and release any other limited resources. Use finally blocks, or the C# using statement to ensure that connections are closed irrespective of whether an exception condition occurs. The next example illustrates the use of the finally block. The C# using statement can also be used as shown below:

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

Example: Secure Data Access Component

The following code shows how to retrieve a connection string from the registry and decrypt it using the managed DPAPI helper library provided in How To: Create a DPAPI Library on MSDN. It illustrates a sample implementation of a CheckProductStockLevel method that queries a products database for stock quantity. The code illustrates a number of the important security features for data access code discussed above.

public static int CheckProductStockLevel(string productCode) 
{ 
  int quantity = 0; 
  // (1) Code protected by try/catch block 
  try 
  { 
    // (2) Input validated with regular expression 
    Regex rex = new Regex("^[A-Za-z0-9]{12}$"); 
    if (rex.IsMatch(productCode) == false) 
      // Error messages should be retrieved from a resource assembly to 
      // assist localization. The localization code is omitted here for brevity 
      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 = 
               cmd.Parameters.Add("@ProductCode", 
                                  SqlDbType.VarChar,12).Value = productCode; 
      // Define the output parameter 
      SqlParameter retparm = cmd.Parameters.Add("@quantity", SqlDbType.Int); 
      retparm.Direction = ParameterDirection.Output; 
      conn.Open(); 
      cmd.ExecuteNonQuery(); 
      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( 
                                        @"Software\OrderProcessing\") 
                                        .GetValue("ConnectionString"); 
// 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 avoid system level information being returned 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 ensure it only contains characters in the range A-Z and 0-9, and that it 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 that aim to use up all available connections to the database. Similar functionality can be achieved by using a finally block.

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

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

6. Encrypted database connection string is stored in the registry. One of the most secure ways to store a database connection string is to encrypt it with DPAPI and store the encrypted cipher text in a registry key secured with an ACL. Depending on which process hosts the component, appropriate ACLs might be Administrators: Full Control; ASP.NET (or Enterprise Services process account): Read.

Code Access Security Considerations

Code Access Security (CAS) may be helpful in some deployment scenarios. Client software developed with .NET supports data access with CAS permission checks. Detailed requirements will vary with the implementation of your chosen ADO.NET managed data provider. If your data access code operates in a full trust environment, and, for example, is always called by Web applications configured for full trust, CAS permission demands issued by the managed data provider will always succeed.

However, if you want your data access code to support partial trust callers, you need to be aware of the permission requirements of the managed data provider that you use. For example, if the provider requires permissions that are not granted to a Medium trust Web application (which is a common configuration used by hosting companies), the permission demand will fail and a SecurityException will be thrown when you attempt to access the database. In this scenario, you need to isolate your data access code to encapsulate the additional permission demands. This requires you to place your data access code in its own assembly, and not in a Web application's presentation logic or associated code-behind files.

When you use the .NET Framework Data Provider for SQL Server to access SQL Server, the provider code demands the SqlClientPermission. Any data access component that uses this provider to communicate with SQL Server requires this permission. For more information about the SqlClientPermission and the permission requirements of the other data providers, see Version 1 Security Changes for the Microsoft .NET Framework.

Note: Any code that calls your data access component also requires these permissions unless you sandbox your data access assembly, because the permission demand walks the complete call stack.

Note: The trust level of a Web application is determined by its <trust> element configuration in web.config or machine.config.

SqlClientPermission can also be used to restrict the allowable range of name-value pairs that can be used on a connection string passed to the SqlConnection object. In the following code, the CheckProductStockLevel method has been enhanced with an additional security check to ensure that blank passwords cannot be used in the connection string.

[SqlClientPermissionAttribute(SecurityAction.PermitOnly, 
                              AllowBlankPassword=false)] 
public static int CheckProductStockLevel(string productCode) 
{ 
 . . . 
} 

Finally, because the code only requires read access to a specific registry key, the RegistryPermissionAttribute can also be added to ensure the code is not able to access any other area of the registry.

[RegistryPermissionAttribute(SecurityAction.PermitOnly, 
                             Read=@"HKEY_LOCAL_MACHINE\Software\OrderProcessing")] 
[SqlClientPermissionAttribute(SecurityAction.PermitOnly, 
                              AllowBlankPassword=false)] 
public static int CheckProductStockLevel(string productCode) 
{ 
 . . . 
} 

The following table shows the permissions that must be granted to your data access assemblies (and their callers if you do not sandbox the data access code) for each of the ADO.NET data providers.

ADO.NET Data Provider	Required CAS Permissions
SQL Server	SqlClientPermission (Supported by Medium trust Web applications.)
OLE DB	OleDbPermission (Currently has no effect; OLE DB provider on .NET Framework 1.0 and 1.1 requires Full Trust callers.)
Oracle	OraclePermission (Currently has no effect; Oracle provider on .NET Framework 1.0 and 1.1 requires Full Trust callers.)
ODBC	OdbcPermission (Currently has no effect; ODBC provider on .NET Framework 1.0 and 1.1 requires Full Trust callers.)

MSDE

SQL Server 2000 Desktop Engine (MSDE 2000) is a version of the SQL Server data engine designed for redistribution with client-side applications. It has the same security architecture and features as SQL Server. If you are using MSDE, the following additional guidance applies.

• When installing an instance of MSDE that will operate only as a local data store, you should disable the Server Net-Libraries. For more information, search for Microsoft Knowledge Base article 814130, "How to Secure Network Connectivity for SQL Server 2000 Local Databases".

• When distributing MSDE to your customers, you should use the Microsoft-supplied installer rather than merge modules.

• If your product includes MSDE, you should make this known to your customers. In the future, they may need to install or accept MSDE-specific software updates.

• Include security best practices in your product documentation.