Securing ADO.NET

Writing a secure ADO.NET application involves more than avoiding common coding pitfalls such as not validating user input. An application that accesses data has many potential points of failure that an attacker can exploit to retrieve, manipulate, or destroy sensitive data. It is therefore important to understand all aspects of security, from the process of threat modeling during the design phase of your application, to its eventual deployment and ongoing maintenance.

The .NET Framework provides many useful classes, services, and tools for securing and administering database applications. The common language runtime (CLR) provides a type-safe environment for code to run in, with code access security (CAS) to restrict further the permissions of managed code. Following secure data access coding practices limits the damage that can be inflicted by a potential attacker.

Writing secure code does not guard against self-inflicted security holes when working with unmanaged resources such as databases. Most server databases, such as SQL Server, have their own security systems, which enhance security when implemented correctly. However, even a data source with a robust security system can be victimized in an attack if it is not configured appropriately.

In This Section

Security Overview
Provides recommendations for designing secure ADO.NET applications.

Secure Data Access
Describes how to work with data from a secured data source.

Secure Client Applications
Describes security considerations for client applications.

Code Access Security and ADO.NET
Describes how CAS can help protect ADO.NET code. Also discusses how to work with partial trust.

Privacy and Data Security
Describes encryption options for ADO.NET applications.

Related Sections

SQL Server Security
Describes SQL Server security features from a developer's perspective.

Security Considerations
Describes security for Entity Framework applications.

Security
Contains links to topics describing all aspects of security in .NET.

Security Tools
.NET Framework tools for securing and administering security policy.

Resources for Creating Secure Applications
Provides links to topics for creating secure applications.

Security Bibliography
Provides links to external resources available online and in print.

See also

• ADO.NET
• ADO.NET Managed Providers and DataSet Developer Center

Security Overview

Securing an application is an ongoing process. There will never be a point where a developer can guarantee that an application is safe from all attacks, because it is impossible to predict what kinds of future attacks new technologies will bring about. Conversely, just because nobody has yet discovered (or published) security flaws in a system does not mean that none exist or could exist. You need to plan for security during the design phase of the project, as well as plan how security will be maintained over the lifetime of the application.

Design for Security

One of the biggest problems in developing secure applications is that security is often an afterthought, something to implement after a project is code-complete. Not building security into an application at the outset leads to insecure applications because little thought has been given to what makes an application secure.

Last minute security implementation leads to more bugs, as software breaks under the new restrictions or has to be rewritten to accommodate unanticipated functionality. Every line of revised code contains the possibility of introducing a new bug. For this reason, you should consider security early in the development process so that it can proceed in tandem with the development of new features.

Threat Modeling

You cannot protect a system against attack unless you understand all the potential attacks that it is exposed to. The process of evaluating security threats, called threat modeling, is necessary to determine the likelihood and ramifications of security breaches in your ADO.NET application.

Threat modeling is composed of three high-level steps: understanding the adversary’s view, characterizing the security of the system, and determining threats.

Threat modeling is an iterative approach to assessing vulnerabilities in your application to find those that are the most dangerous because they expose the most sensitive data. Once you identify the vulnerabilities, you rank them in order of severity and create a prioritized set of countermeasures to counter the threats.

For more information, see the following resources.

Resource	Description
The Threat Modeling site on the MSDN Security Developer Center	The resources on this page will help you understand the threat modeling process and build threat models that you can use to secure your own applications

The Principle of Least Privilege

When you design, build, and deploy your application, you must assume that your application will be attacked. Often these attacks come from malicious code that executes with the permissions of the user running the code. Others can originate with well-intentioned code that has been exploited by an attacker. When planning security, always assume the worst-case scenario will occur.

One counter-measure you can employ is to try to erect as many walls around your code as possible by running with least privilege. The principle of least privilege says that any given privilege should be granted to the least amount of code necessary for the shortest duration of time that is required to get the job done.

The best practice for creating secure applications is to start with no permissions at all and then add the narrowest permissions for the particular task being performed. By contrast, starting with all permissions and then denying individual ones leads to insecure applications that are difficult to test and maintain because security holes may exist from unintentionally granting more permissions than required.

For more information on securing your applications, see the following resources.

Resource	Description
Securing Applications	Contains links to general security topics. Also contains links to topics for securing distributed applications, Web applications, mobile applications, and desktop applications.

Code Access Security (CAS)

Code access security (CAS) is a mechanism that helps limit the access that code has to protected resources and operations. In the .NET Framework, CAS performs the following functions:

• Defines permissions and permission sets that represent the right to access various system resources.

• Enables administrators to configure security policy by associating sets of permissions with groups of code (code groups).

• Enables code to request the permissions it requires in order to run, as well as the permissions that would be useful to have, and specifies which permissions the code must never have.

• Grants permissions to each assembly that is loaded, based on the permissions requested by the code and on the operations permitted by security policy.

• Enables code to demand that its callers have specific permissions.

• Enables code to demand that its callers possess a digital signature, thus allowing only callers from a particular organization or site to call the protected code.

• Enforces restrictions on code at run time by comparing the granted permissions of every caller on the call stack to the permissions that callers must have.

To minimize the amount of damage that can occur if an attack succeeds, choose a security context for your code that grants access only to the resources it needs to get its work done and no more.

For more information, see the following resources.

Resource	Description
Code Access Security and ADO.NET	Describes the interactions between code access security, role-based security, and partially trusted environments from the perspective of an ADO.NET application.
Code Access Security	Contains links to additional topics describing CAS in the .NET Framework.

Database Security

The principle of least privilege also applies to your data source. Some general guidelines for database security include:

• Create accounts with the lowest possible privileges.

• Do not allow users access to administrative accounts just to get code working.

• Do not return server-side error messages to client applications.

• Validate all input at both the client and the server.

• Use parameterized commands and avoid dynamic SQL statements.

• Enable security auditing and logging for the database you are using so that you are alerted to any security breaches.

For more information, see the following resources.

Resource	Description
SQL Server Security	Provides an overview of SQL Server security with application scenarios that provide guidance for creating secure ADO.NET applications that target SQL Server.
Recommendations for Data Access Strategies	Provides recommendations for accessing data and performing database operations.

Security Policy and Administration

Improperly administering code access security (CAS) policy can potentially create security weaknesses. Once an application is deployed, techniques for monitoring security should be used and risks evaluated as new threats emerge.

For more information, see the following resources.

Resource	Description
Security Policy Management	Provides information on creating and administering security policy.
Security Policy Best Practices	Provides links describing how to administer security policy.

See also

• Securing ADO.NET Applications
• Security in .NET
• SQL Server Security
• ADO.NET Managed Providers and DataSet Developer Center

Secure Data Access

To write secure ADO.NET code, you have to understand the security mechanisms available in the underlying data store, or database. You also need to consider the security implications of other features or components that your application may contain.

Authentication, Authorization and Permissions

When connecting to Microsoft SQL Server, you can use Windows Authentication, also known as Integrated Security, which uses the identity of the current active Windows user rather than passing a user ID and password. Using Windows Authentication is highly recommended because user credentials are not exposed in the connection string. If you cannot use Windows Authentication to connect to SQL Server, then consider creating connection strings at run time using the SqlConnectionStringBuilder.

The credentials used for authentication need to be handled differently based on the type of application. For example, in a Windows Forms application, the user can be prompted to supply authentication information, or the user's Windows credentials can be used. However, a Web application often accesses data using credentials supplied by the application itself rather than by the user.

Once users have been authenticated, the scope of their actions depends on the permissions that have been granted to them. Always follow the principle of least privilege and grant only permissions that are absolutely necessary.

For more information, see the following resources.

Resource	Description
Protecting Connection Information	Describes security best practices and techniques for protecting connection information, such as using protected configuration to encrypt connection strings.
Recommendations for Data Access Strategies	Provides recommendations for accessing data and performing database operations.
Connection String Builders	Describes how to build connection strings from user input at run time.
Overview of SQL Server Security	Describes the SQL Server security architecture.

Parameterized Commands and SQL Injection

Using parameterized commands helps guard against SQL injection attacks, in which an attacker "injects" a command into a SQL statement that compromises security on the server. Parameterized commands guard against a SQL injection attack by ensuring that values received from an external source are passed as values only, and not part of the Transact-SQL statement. As a result, Transact-SQL commands inserted into a value are not executed at the data source. Rather, they are evaluated solely as a parameter value. In addition to the security benefits, parameterized commands provide a convenient method for organizing values passed with a Transact-SQL statement or to a stored procedure.

For more information on using parameterized commands, see the following resources.

Resource	Description
DataAdapter Parameters	Describes how to use parameters with a DataAdapter.
Modifying Data with Stored Procedures	Describes how to specify parameters and obtain a return value.
Managing Permissions with Stored Procedures in SQL Server	Describes how to use SQL Server stored procedures to encapsulate data access.

Script Exploits

A script exploit is another form of injection that uses malicious characters inserted into a Web page. The browser does not validate the inserted characters and will process them as part of the page.

For more information, see the following resources.

Resource	Description
Script Exploits Overview	Describes how to guard against scripting and SQL statement exploits.

Probing Attacks

Attackers often use information from an exception, such as the name of your server, database, or table, to mount an attack on your system. Because exceptions can contain specific information about your application or data source, you can help keep your application and data source better protected by only exposing essential information to the client.

For more information, see the following resources.

Resource	Description
Exception Handling Fundamentals	Describes the basic forms of try/catch/finally structured exception handling.
Best Practices for Exceptions	Describes best practices for handling exceptions.

Protecting Microsoft Access and Excel Data Sources

Microsoft Access and Microsoft Excel can act as a data store for an ADO.NET application when security requirements are minimal or nonexistent. Their security features are effective for deterrence, but should not be relied upon to do more than discourage meddling by uninformed users. The physical data files for Access and Excel exist on the file system, and must be accessible to all users. This makes them vulnerable to attacks that could result in theft or data loss since the files can be easily copied or altered. When robust security is required, use SQL Server or another server-based database where the physical data files are not readable from the file system.

For more information on protecting Access and Excel data, see the following resources.

Resource	Description
Security Considerations and Guidance for Access 2007	Describes security techniques for Access 2007 such encrypting files, administering passwords, converting databases to the new ACCDB and ACCDE formats, and using other security options.
Understanding the Role of Workgroup Information Files in Access Security	Explains the role and relationship of the workgroup information file in Access 2003 security.
Frequently Asked Questions About Microsoft Access Security for Microsoft Access versions 2.0 through 2000	Downloadable version of the Microsoft Access Security FAQ.

Enterprise Services

COM+ contains its own security model that relies on Windows NT accounts and process/thread impersonation. The System.EnterpriseServices namespace provides wrappers that allow .NET applications to integrate managed code with COM+ security services through the ServicedComponent class.

For more information, see the following resource.

Resource	Description
Role-Based Security	Discusses how to integrate managed code with COM+ security services.

Interoperating with Unmanaged Code

The .NET Framework provides for interaction with unmanaged code, including COM components, COM+ services, external type libraries, and many operating system services. Working with unmanaged code involves going outside the security perimeter for managed code. Both your code and any code that calls it must have unmanaged code permission (SecurityPermission with the UnmanagedCode flag specified). Unmanaged code can introduce unintended security vulnerabilities into your application. Therefore, you should avoid interoperating with unmanaged code unless it is absolutely necessary.

For more information, see the following resources.

Resource	Description
Interoperating with Unmanaged Code	Contains topics describing how to expose COM components to the .NET Framework and how to expose .NET Framework components to COM.
Advanced COM Interoperability	Contains advanced topics such as primary interop assemblies, threading and custom marshaling.

See also

• Securing ADO.NET Applications
• SQL Server Security
• Recommendations for Data Access Strategies
• Protecting Connection Information
• Connection String Builders
• ADO.NET Managed Providers and DataSet Developer Center

Secure Client Applications

Applications typically consist of many parts that must all be protected from vulnerabilities that could result in data loss or otherwise compromise the system. Creating secure user interfaces can prevent many problems by blocking attackers before they can access data or system resources.

Validate User Input

When constructing an application that accesses data, you should assume that all user input is malicious until proven otherwise. Failure to do so can leave your application vulnerable to attack. The .NET Framework contains classes to help you enforce a domain of values for input controls, such as limiting the number of characters that can be entered. Event hooks allow you to write procedures to check the validity of values. User input data can be validated and strongly typed, limiting an application's exposure to script and SQL injection exploits.

Important

You must also validate user input at the data source as well as in the client application. An attacker may choose to circumvent your application and attack the data source directly.

Security and User Input
Describes how to handle subtle and potentially dangerous bugs involving user input.

Validating User Input in ASP.NET Web Pages
Overview of validating user input using ASP.NET validation controls.

User Input in Windows Forms
Provides links and information for validating mouse and keyboard input in a Windows Forms application.

.NET Framework Regular Expressions
Describes how to use the Regex class to check the validity of user input.

Windows Applications

In the past, Windows applications generally ran with full permissions. The .NET Framework provides the infrastructure to restrict code executing in a Windows application by using code access security (CAS). However, CAS alone is not enough to protect your application.

Windows Forms Security
Discusses how to secure Windows Forms applications and provides links to related topics.

Windows Forms and Unmanaged Applications
Describes how to interact with unmanaged applications in a Windows Forms application.

ClickOnce Deployment for Windows Forms
Describes how to use ClickOnce deployment in a Windows Forms application and discusses the security implications.

ASP.NET and XML Web Services

ASP.NET applications generally need to restrict access to some portions of the Web site and provide other mechanisms for data protection and site security. These links provide useful information for securing your ASP.NET application.

An XML Web service provides data that can be consumed by an ASP.NET application, a Windows Forms application, or another Web service. You need to manage security for the Web service itself as well as security for the client application.

For more information, see the following resources.

Resource	Description
Securing ASP.NET Web Sites	Discusses how to secure ASP.NET applications.
Securing XML Web Services Created Using ASP.NET	Discusses how to implement security for an ASP.NET Web Service.
Script Exploits Overview	Discusses how to guard against a script exploit attack, which attempts to insert malicious characters into a Web page.
Basic Security Practices for Web Applications	General security information and links to further discussion,

Remoting

.NET remoting enables you to build widely distributed applications easily, whether the application components are all on one computer or spread out across the entire world. You can build client applications that use objects in other processes on the same computer or on any other computer that is reachable over its network. You can also use .NET remoting to communicate with other application domains in the same process.

Resource	Description
Configuration of Remote Applications	Discusses how to configure remoting applications in order to avoid common problems.
Security in Remoting	Describes authentication and encryption as well as additional security topics relevant to remoting.
Security and Remoting Considerations	Describes security issues with protected objects and application domain crossing.

See also

• Securing ADO.NET Applications
• Recommendations for Data Access Strategies
• Securing Applications
• Protecting Connection Information
• ADO.NET Managed Providers and DataSet Developer Center

Code Access Security and ADO.NET

The .NET Framework offers role-based security as well as code access security (CAS), both of which are implemented using a common infrastructure supplied by the common language runtime (CLR). In the world of unmanaged code, most applications execute with the permissions of the user or principal. As a result, computer systems can be damaged and private data compromised when malicious or error-filled software is run by a user with elevated privileges.

By contrast, managed code executing in the .NET Framework includes code access security, which applies to code alone. Whether the code is allowed to run or not depends on the code's origin or other aspects of the code's identity, not solely the identity of the principal. This reduces the likelihood that managed code can be misused.

Code Access Permissions

When code is executed, it presents evidence that is evaluated by the CLR security system. Typically, this evidence comprises the origin of the code including URL, site, and zone, and digital signatures that ensure the identity of the assembly.

The CLR allows code to perform only those operations that the code has permission to perform. Code can request permissions, and those requests are honored based on the security policy set by an administrator.

Note

Code executing in the CLR cannot grant permissions to itself. For example, code can request and be granted fewer permissions than a security policy allows, but it will never be granted more permissions. When granting permissions, start with no permissions at all and then add the narrowest permissions for the particular task being performed. Starting with all permissions and then denying individual ones leads to insecure applications that may contain unintentional security holes from granting more permissions than required. For more information, see Configuring Security Policy and Security Policy Management.

There are three types of code access permissions:

• Code access permissions derive from the CodeAccessPermission class. Permissions are required in order to access protected resources, such as files and environment variables, and to perform protected operations, such as accessing unmanaged code.

• Identity permissions represent characteristics that identify an assembly. Permissions are granted to an assembly based on evidence, which can include items such as a digital signature or where the code originated. Identity permissions also derive from the CodeAccessPermission base class.

• Role-based security permissions are based on whether a principal has a specified identity or is a member of a specified role. The PrincipalPermission class allows both declarative and imperative permission checks against the active principal.

To determine whether code is authorized to access a resource or perform an operation, the runtime's security system traverses the call stack, comparing the granted permissions of each caller to the permission being demanded. If any caller in the call stack does not have the demanded permission, a SecurityException is thrown and access is refused.

Requesting Permissions

The purpose of requesting permissions is to inform the runtime which permissions your application requires in order to run, and to ensure that it receives only the permissions that it actually needs. For example, if your application needs to write data to the local disk, it requires FileIOPermission. If that permission hasn't been granted, the application will fail when it attempts to write to the disk. However, if the application requests FileIOPermission and that permission has not been granted, the application will generate the exception at the outset and will not load.

In a scenario where the application only needs to read data from the disk, you can request that it never be granted any write permissions. In the event of a bug or a malicious attack, your code cannot damage the data on which it operates. For more information, see Requesting Permissions.

Role-Based Security and CAS

Implementing both role-based security and code-accessed security (CAS) enhances overall security for your application. Role-based security can be based on a Windows account or a custom identity, making information about the security principal available to the current thread. In addition, applications are often required to provide access to data or resources based on credentials supplied by the user. Typically, such applications check the role of a user and provide access to resources based on those roles.

Role-based security enables a component to identify current users and their associated roles at run time. This information is then mapped using a CAS policy to determine the set of permissions granted at run time. For a specified application domain, the host can change the default role-based security policy and set a default security principal that represents a user and the roles associated with that user.

The CLR uses permissions to implement its mechanism for enforcing restrictions on managed code. Role-based security permissions provide a mechanism for discovering whether a user (or the agent acting on the user's behalf) has a particular identity or is a member of a specified role. For more information, see Security Permissions.

Depending on the type of application you are building, you should also consider implementing role-based permissions in the database. For more information on role-based security in SQL Server, see SQL Server Security.

Assemblies

Assemblies form the fundamental unit of deployment, version control, reuse, activation scoping, and security permissions for a .NET Framework application. An assembly provides a collection of types and resources that are built to work together and form a logical unit of functionality. To the CLR, a type does not exist outside the context of an assembly. For more information on creating and deploying assemblies, see Programming with Assemblies.

Strong-naming Assemblies

A strong name, or digital signature, consists of the assembly's identity, which includes its simple text name, version number, and culture information (if provided), plus a public key and a digital signature. The digital signature is generated from an assembly file using the corresponding private key. The assembly file contains the assembly manifest, which contains the names and hashes of all the files that make up the assembly.

Strong naming an assembly gives an application or component a unique identity that other software can use to refer explicitly to it. Strong naming guards assemblies against being spoofed by an assembly that contains hostile code. Strong-naming also ensures versioning consistency among different versions of a component. You must strong name assemblies that will be deployed to the Global Assembly Cache (GAC). For more information, see Creating and Using Strong-Named Assemblies.

Partial Trust in ADO.NET 2.0

In ADO.NET 2.0, the .NET Framework Data Provider for SQL Server, the .NET Framework Data Provider for OLE DB, the .NET Framework Data Provider for ODBC, and the .NET Framework Data Provider for Oracle can now all run in partially trusted environments. In previous releases of the .NET Framework, only System.Data.SqlClient was supported in less than full-trust applications.

At minimum, a partially trusted application using the SQL Server provider must have execution and SqlClientPermission permissions.

Permission Attribute Properties for Partial Trust

For partial trust scenarios, you can use SqlClientPermissionAttribute members to further restrict the capabilities available for the .NET Framework Data Provider for SQL Server.

The following table lists the available SqlClientPermissionAttribute properties and their descriptions:

Permission attribute property	Description
Action	Gets or sets a security action. Inherited from SecurityAttribute.
AllowBlankPassword	Enables or disables the use of a blank password in a connection string. Valid values are true (to enable the use of blank passwords) and false (to disable the use of blank passwords). Inherited from DBDataPermissionAttribute.
ConnectionString	Identifies a permitted connection string. Multiple connection strings can be identified. Note: Do not include a user ID or password in your connection string. In this release, you cannot change connection string restrictions using the .NET Framework Configuration Tool. Inherited from DBDataPermissionAttribute.
KeyRestrictions	Identifies connection string parameters that are allowed or disallowed. Connection string parameters are identified in the form <parameter name>=. Multiple parameters can be specified, delimited using a semicolon (;). Note: If you do not specify KeyRestrictions, but you set KeyRestrictionBehavior property to AllowOnly or PreventUsage, no additional connection string parameters are allowed. Inherited from DBDataPermissionAttribute.
KeyRestrictionBehavior	Identifies the connection string parameters as the only additional parameters allowed (AllowOnly), or identifies the additional parameters that are not allowed (PreventUsage). AllowOnly is the default. Inherited from DBDataPermissionAttribute.
TypeID	Gets a unique identifier for this attribute when implemented in a derived class. Inherited from Attribute.
Unrestricted	Indicates whether unrestricted permission to the resource is declared. Inherited from SecurityAttribute.

ConnectionString Syntax

The following example demonstrates how to use the connectionStrings element of a configuration file to allow only a specific connection string to be used. See Connection Strings for more information on storing and retrieving connection strings from configuration files.

XML

<connectionStrings>  
  <add name="DatabaseConnection"   
    connectionString="Data Source=(local);Initial   
    Catalog=Northwind;Integrated Security=true;" />  
</connectionStrings>  

KeyRestrictions Syntax

The following example enables the same connection string, enables the use of the Encrypt and Packet Size connection string options, but restricts the use of any other connection string options.

XML

<connectionStrings>  
  <add name="DatabaseConnection"   
    connectionString="Data Source=(local);Initial   
    Catalog=Northwind;Integrated Security=true;"  
    KeyRestrictions="Encrypt=;Packet Size=;"  
    KeyRestrictionBehavior="AllowOnly" />  
</connectionStrings>  

KeyRestrictionBehavior with PreventUsage Syntax

The following example enables the same connection string and allows all other connection parameters except for User Id, Password and Persist Security Info.

XML

<connectionStrings>  
  <add name="DatabaseConnection"   
    connectionString="Data Source=(local);Initial   
    Catalog=Northwind;Integrated Security=true;"  
    KeyRestrictions="User Id=;Password=;Persist Security Info=;"  
    KeyRestrictionBehavior="PreventUsage" />  
</connectionStrings>  

KeyRestrictionBehavior with AllowOnly Syntax

The following example enables two connection strings that also contain Initial Catalog, Connection Timeout, Encrypt, and Packet Size parameters. All other connection string parameters are restricted.

XML

<connectionStrings>  
  <add name="DatabaseConnection"   
    connectionString="Data Source=(local);Initial   
    Catalog=Northwind;Integrated Security=true;"  
    KeyRestrictions="Initial Catalog;Connection Timeout=;  
       Encrypt=;Packet Size=;"   
    KeyRestrictionBehavior="AllowOnly" />  
  
  <add name="DatabaseConnection2"   
    connectionString="Data Source=SqlServer2;Initial   
    Catalog=Northwind2;Integrated Security=true;"  
    KeyRestrictions="Initial Catalog;Connection Timeout=;  
       Encrypt=;Packet Size=;"   
    KeyRestrictionBehavior="AllowOnly" />  
</connectionStrings>  

Enabling Partial Trust with a Custom Permission Set

To enable the use of System.Data.SqlClient permissions for a particular zone, a system administrator must create a custom permission set and set it as the permission set for a particular zone. Default permission sets, such as LocalIntranet, cannot be modified. For example, to include System.Data.SqlClient permissions for code that has a Zone of LocalIntranet, a system administrator could copy the permission set for LocalIntranet, rename it to "CustomLocalIntranet", add the System.Data.SqlClient permissions, import the CustomLocalIntranet permission set using the Caspol.exe (Code Access Security Policy Tool), and set the permission set of LocalIntranet_Zone to CustomLocalIntranet.

Sample Permission Set

The following is a sample permission set for the .NET Framework Data Provider for SQL Server in a partially trusted scenario. For information on creating custom permission sets, see Configuring Permission Sets Using Caspol.exe.

XML

<PermissionSet class="System.Security.NamedPermissionSet"  
  version="1"  
  Name="CustomLocalIntranet"  
  Description="Custom permission set given to applications on  
    the local intranet">  
  
<IPermission class="System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"  
version="1"  
AllowBlankPassword="False">  
<add ConnectionString="Data Source=(local);Integrated Security=true;"  
 KeyRestrictions="Initial Catalog=;Connection Timeout=;  
   Encrypt=;Packet Size=;"   
 KeyRestrictionBehavior="AllowOnly" />  
 </IPermission>  
</PermissionSet>  

Verifying ADO.NET Code Access Using Security Permissions

For partial-trust scenarios, you can require CAS privileges for particular methods in your code by specifying a SqlClientPermissionAttribute. If that privilege is not allowed by the restricted security policy in effect, an exception is thrown before your code is run. For more information on security policy, see Security Policy Management and Security Policy Best Practices.

Example

The following example demonstrates how to write code that requires a particular connection string. It simulates denying unrestricted permissions to System.Data.SqlClient, which a system administrator would implement using a CAS policy in the real world.

Important

When designing CAS permissions for ADO.NET, the correct pattern is to start with the most restrictive case (no permissions at all) and then add the specific permissions that are needed for the particular task that the code needs to perform. The opposite pattern, starting with all permissions and then denying a specific permission, is not secure because there are many ways of expressing the same connection string. For example, if you start with all permissions and then attempt to deny the use of the connection string "server=someserver", the string "server=someserver.mycompany.com" would still be allowed. By always starting by granting no permissions at all, you reduce the chances that there are holes in the permission set.

The following code demonstrates how SqlClient performs the security demand, which throws a SecurityException if the appropriate CAS permissions are not in place. The SecurityException output is displayed in the console window.

C#

using System;
using System.Data;
using System.Data.SqlClient;
using System.Security;
using System.Security.Permissions;
 
namespace PartialTrustTopic {
   public class PartialTrustHelper : MarshalByRefObject {
      public void TestConnectionOpen(string connectionString) {
         // Try to open a connection.
         using (SqlConnection connection = new SqlConnection(connectionString)) {
            connection.Open();
         }
      }
   }
 
   class Program {
      static void Main(string[] args) {
         TestCAS("Data Source=(local);Integrated Security=true", "Data Source=(local);Integrated Security=true;Initial Catalog=Test");
      }
 
      static void TestCAS(string connectString1, string connectString2) {
         // Create permission set for sandbox AppDomain.
         // This example only allows execution.
         PermissionSet permissions = new PermissionSet(PermissionState.None);
         permissions.AddPermission(new SecurityPermission(SecurityPermissionFlag.Execution));
 
         // Create sandbox AppDomain with permission set that only allows execution,
         // and has no SqlClientPermissions.
         AppDomainSetup appDomainSetup = new AppDomainSetup();
         appDomainSetup.ApplicationBase = AppDomain.CurrentDomain.SetupInformation.ApplicationBase;
         AppDomain firstDomain = AppDomain.CreateDomain("NoSqlPermissions", null, appDomainSetup, permissions);
 
         // Create helper object in sandbox AppDomain so that code can be executed in that AppDomain.
         Type helperType = typeof(PartialTrustHelper);
         PartialTrustHelper firstHelper = (PartialTrustHelper)firstDomain.CreateInstanceAndUnwrap(helperType.Assembly.FullName, helperType.FullName);
 
         try {
            // Attempt to open a connection in the sandbox AppDomain.
            // This is expected to fail.
            firstHelper.TestConnectionOpen(connectString1);
            Console.WriteLine("Connection opened, unexpected.");
         }
         catch (System.Security.SecurityException ex) {
            Console.WriteLine("Failed, as expected: {0}",
                ex.FirstPermissionThatFailed);
 
            // Uncomment the following line to see Exception details.
            // Console.WriteLine("BaseException: " + ex.GetBaseException());
         }
 
         // Add permission for a specific connection string.
         SqlClientPermission sqlPermission = new SqlClientPermission(PermissionState.None);
         sqlPermission.Add(connectString1, "", KeyRestrictionBehavior.AllowOnly);
 
         permissions.AddPermission(sqlPermission);
 
         AppDomain secondDomain = AppDomain.CreateDomain("OneSqlPermission", null, appDomainSetup, permissions);
         PartialTrustHelper secondHelper = (PartialTrustHelper)secondDomain.CreateInstanceAndUnwrap(helperType.Assembly.FullName, helperType.FullName);
 
         // Try connection open again, it should succeed now.
         try {
            secondHelper.TestConnectionOpen(connectString1);
            Console.WriteLine("Connection opened, as expected.");
         }
         catch (System.Security.SecurityException ex) {
            Console.WriteLine("Unexpected failure: {0}", ex.Message);
         }
 
         // Try a different connection string. This should fail.
         try {
            secondHelper.TestConnectionOpen(connectString2);
            Console.WriteLine("Connection opened, unexpected.");
         }
         catch (System.Security.SecurityException ex) {
            Console.WriteLine("Failed, as expected: {0}", ex.Message);
         }
      }
   }
}

You should see this output in the Console window:

Failed, as expected: <IPermission class="System.Data.SqlClient.  
SqlClientPermission, System.Data, Version=2.0.0.0,   
  Culture=neutral, PublicKeyToken=b77a5c561934e089" version="1"  
  AllowBlankPassword="False">  
<add ConnectionString="Data Source=(local);Initial Catalog=  
  Northwind;Integrated Security=SSPI" KeyRestrictions=""  
KeyRestrictionBehavior="AllowOnly"/>  
</IPermission>  
  
Connection opened, as expected.  
Failed, as expected: Request failed.  

Interoperability with Unmanaged Code

Code that runs outside the CLR is called unmanaged code. Therefore, security mechanisms such as CAS cannot be applied to unmanaged code. COM components, ActiveX interfaces, and Windows API functions are examples of unmanaged code. Special security considerations apply when executing unmanaged code so that you do not jeopardize overall application security. For more information, see Interoperating with Unmanaged Code.

The .NET Framework also supports backward compatibility to existing COM components by providing access through COM interop. You can incorporate COM components into a .NET Framework application by using COM interop tools to import the relevant COM types. Once imported, the COM types are ready to use. COM interop also enables COM clients to access managed code by exporting assembly metadata to a type library and registering the managed component as a COM component. For more information, see Advanced COM Interoperability.

See also

• Securing ADO.NET Applications
• Security in Native and .NET Framework Code
• Role-Based Security
• ADO.NET Managed Providers and DataSet Developer Center

Privacy and Data Security

Safeguarding and managing sensitive information in an ADO.NET application is dependent upon the underlying products and technologies used to create it. ADO.NET does not directly provide services for securing or encrypting data.

Cryptography and Hash Codes

The classes in the .NET Framework System.Security.Cryptography namespace can be used from your ADO.NET applications to prevent data from being read or modified by unauthorized third parties. Some classes are wrappers for the unmanaged Microsoft CryptoAPI, while others are managed implementations. The Cryptographic Services topic provides an overview of cryptography in the .NET Framework, describes how cryptograph is implemented, and how you can perform specific cryptographic tasks.

Unlike cryptography, which allows data to be encrypted and then decrypted, hashing data is a one-way process. Hashing data is useful when you want to prevent tampering by checking that data has not been altered: given identical input strings, hashing algorithms always produce identical short output values that can easily be compared. Ensuring Data Integrity with Hash Codes describes how you can generate and verify hash values.

Encrypting Configuration Files

Protecting access to your data source is one of the most important goals when securing an application. A connection string presents a potential vulnerability if it is not secured. Connection strings saved in configuration files are stored in standard XML files for which the .NET Framework has defined a common set of elements. Protected configuration enables you to encrypt sensitive information in a configuration file. Although primarily designed for ASP.NET applications, protected configuration can also be used to encrypt configuration file sections in Windows applications. For more information, see Protecting Connection Information.

Securing String Values in Memory

If a String object contains sensitive information, such as a password, credit card number, or personal data, there is a risk that the information could be revealed after it is used because the application cannot delete the data from computer memory.

A String is immutable; its value cannot be modified once it has been created. Changes that appear to modify the string value actually create a new instance of a String object in memory, storing the data as plain text. In addition, it is not possible to predict when the string instances will be deleted from memory. Memory reclamation with strings is not deterministic with .NET garbage collection. You should avoid using the String and StringBuilder classes if your data is truly sensitive.

The SecureString class provides methods for encrypting text using the Data Protection API (DPAPI) in memory. The string is then deleted from memory when it is no longer needed. There is no ToString method to quickly read the contents of a SecureString. You can initialize a new instance of SecureString with no value or by passing it a pointer to an array of Char objects. You can then use the various methods of the class to work with the string. For more information, download the SecureString Sample Application, which demonstrates how to use the SecureString class from.

See also

• Securing ADO.NET Applications
• SQL Server Security
• ADO.NET Managed Providers and DataSet Developer Center

 

Source/Reference

• https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/securing-ado-net-applications
• https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/security-overview
• https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/secure-data-access
• https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/secure-client-applications
• https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/code-access-security
• https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/privacy-and-data-security