Code Access Security and ADO.NET 

Windows security lets you control access to resources according to the role of the user. For example, membership in the Windows Administrators group gives system administrators unrestricted permissions, whereas a guest user has very limited access.

In the world of unmanaged code, most applications execute with the permissions of the user, making it difficult to apply restrictions to code. As a result, computer systems can be damaged and private data can be compromised when malicious or error-filled software is run.

By contrast, managed code executing in the .NET Framework includes a security mechanism called Code Access Security, which allows code to be trusted to varying degrees depending on the code's origin and other aspects of the code's identity. This reduces the likelihood that managed code can be misused.

ADO.NET and Code Access Security

When code is executed, it presents evidence that is evaluated by the Common Language Runtime (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 code is then granted a set of permissions based on this evidence. The application either runs properly or generates a SecurityException. The System.Security.Policy settings on the local computer determine which permissions the code receives. For more information, see Configuring Security Policy and Security Policy Management.

Permission sets are commonly separated into the following three categories:

Category	Description
Full trust	Indicates that the code has no restrictions and is used, by default, for the local computer and strong-named sources.
Partial trust	Contains a combination of permissions and restrictions appropriate for a particular zone.
Untrusted	Indicates zones having no permission set. Code may only use capabilities that require no permissions.

Code executing in the CLR cannot grant permissions to itself. For more information about how the runtime decides which permissions to grant, see Security in Native and .NET Framework Code.

Partial Trust in ADO.NET 2.0

In ADO.NET 2.0, the .NET Framework Data Provider for SQL Server (System.Data.SqlClient), 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.

Note

The .NET Framework Data Provider for SQL Server requires the Full Trust Security permission in order to open a SqlConnection with SQL Debugging enabled in SQL Server 2000. SQL Debugging for SQL Server 2005 does not utilize this class. See SQL Server 2005 Books Online for details.

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 Working with Connection Strings for more information on storing and retrieving connection strings from configuration files.

<connectionStrings>
  <add name="DatabaseConnection" 
    connectionString="Data Source=(local);Initial 
    Catalog=Northwind;Integrated Security=SSPI;" />
</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.

<connectionStrings>
  <add name="DatabaseConnection" 
    connectionString="Data Source=(local);Initial 
    Catalog=Northwind;Integrated Security=SSPI;"
    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.

<connectionStrings>
  <add name="DatabaseConnection" 
    connectionString="Data Source=(local);Initial 
    Catalog=Northwind;Integrated Security=SSPI;"
    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.

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

  <add name="DatabaseConnection2" 
    connectionString="Data Source=SqlServer2;Initial 
    Catalog=Northwind2;Integrated Security=SSPI;"
    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 Code Access Security Policy Tool (Caspol.exe), 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.

<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=SSPI;"
           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 code access security 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 code access security policy in the real world. When using code access security 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 performs. Starting with all permissions and then try to deny a specific permission, by contrast, is not secure, because there are many ways of expressing the same connection string. For example, if you start with all permissions and then deny the use of the connection string "server=someserver", you could still use "server=someserver.mycompany.com". By always starting with no permissions at all you ensure that there are no holes in the permission set.

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

Public Sub CreateOdbcParameter()
    Dim myParameter As New OdbcParameter("Description", OdbcType.VarChar, _
        11, ParameterDirection.Output, True, 0, 0, "Description", _
        DataRowVersion.Current, "garden hose")
    Console.WriteLine(myParameter.ToString())
End Sub

public void CreateOdbcParameter()
{
    OdbcParameter myParameter = new OdbcParameter(
        "Description", OdbcType.VarChar, 11, 
        ParameterDirection.Output, true, 0, 0, "Description",
        DataRowVersion.Current, "garden hose");
    Console.WriteLine(myParameter.ToString());
}

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.

See Also

Other Resources

Securing ADO.NET Applications
Security in Native and .NET Framework Code
Working with Connection Strings