SQL Server 2000 SP3 Security Features and Best Practices: Secure Multi-tier Deployment

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:

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

Figure 4: Simple multi-tier system, indicating major points of vulnerability
See full-sized image.

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.

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.

Top of page

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:
See full-sized image.

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.

Top of page

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.

Top of page

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:

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

Figure 6:
See full-sized image.

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.

Top of page

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:

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

Configuring Terminal Services

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

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

Top of page

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:

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.

Top of page

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:
See full-sized image.

Top of page

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:

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

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.

Top of page

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.

Top of page

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.

Top of page

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:

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

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

To decrypt the encrypted data, do the following:

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.

Top of page

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.

Top of page

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.

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

Top of page

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.

Top of page

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.

Top of page

4 of 5