Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Dec 24, 2007

SQL Server 2005 Express Edition - Part 13 - ClickOnce Deployment and Security

By Marcin Policht

In the most recent installments of our series dedicated to SQL Server 2005 Express Edition, we have been exploring one of its more distinguishing and popular features known as the user instance model. As we pointed out, this functionality is often employed by .NET applications to provide easily configurable and transferable data storage. In particular, its advantages become apparent when combined with ClickOnce technology, used to automate installations and updates of managed (i.e. .NET-based) code. While we have already documented relevant ClickOnce publishing parameters and described a sample scenario demonstrating their practical use, in order to fully understand all aspects of this deployment methodology, it is important to become familiar with security mechanisms that affect its outcome. The main purpose of this article is to present their overview.

Even though the new approach to software distribution frequently eliminates the need for granting administrative privileges to end-users (which is typically required when installing non-managed code), their attempts to launch ClickOnce applications published on remote Web servers or file shares might still be hindered by predicaments resulting from restrictions imposed by Code Access Security.

Code Access Security is a protective mechanism built into .NET Framework (incorporated into its Common Language Runtime). Its primary goal is to control the range of operations that the managed code is allowed to perform on the local computer. This is accomplished by associating any sensitive or potentially intrusive action (such as reading from or writing to a local disk, interacting with other software, accessing the registry, or printing) with granular permissions that can be individually granted to any .NET application that might require them. Note that this is different from the traditional approach, in which the ability to interact with static objects, such as file system or registry entries is evaluated by comparing their Access Control List with the security context in which the code is running (determined by the account used to trigger its execution). Instead, the Common Language Runtime determines whether a particular action is permitted by taking into consideration the following three factors:

  • application manifest - an XML-formatted document included with source installation files, specifying privileges required by the code to properly execute (forming so-called permission set). It is the responsibility of a developer to make sure that the resulting set actually matches the intended use and complies with "the least privilege" principle. While this approach can potentially expose your system to ill effects of either malicious or sloppy programming (if requested permissions are excessive), its goal was to provide full disclosure of such information (with the expectation that software that operates without elevating its security context would be in higher demand).
  • code group - defined on a target computer, consists of an identifying property of .NET code, such as its origin (source Security Zone or Web site's URL) or an inherent characteristic (hash, publisher, strong name, etc.) and arbitrarily chosen permission set. Characteristics of an application to be executed (obtained during its initial invocation) are compared against definitions of local code groups and, once the match is found, its respective permission set is established (this might be different from a list of permissions expected by the application as specified in its manifest - more about this shortly).
  • .application executable (.NET equivalent of .exe file) - similar to the application manifest, this XML-formatted file resides in the published location. In addition to describing deployment options (assigned via the Publish section of the project's Properties dialog box) or providing the application version and relative location of the manifest file, it includes a digital signature, which uniquely identifies the publisher. If this publisher is considered trusted by the end user (i.e. its certificate has been added to the trusted publisher store on the target computer), the Common Language Runtime (or, more specifically, its subsystem known as trust manager) will automatically allow the application to run with all the privileges it might need.

You can define and configure code groups and their respective permission sets on your system using the .NET Framework 2.0 Configuration console (for more information regarding using this utility, refer to our earlier article). Its Runtime Security Policy node allows you to access Enterprise, Machine, and User level settings. In particular, there are several predefined Machine code groups, including Internet_Zone, Restricted_Zone, LocalIntranet_Zone, Restricted_Zone, Trusted_Zone, and My_Computer_Zone entries, with membership based on the application origin (you can either modify these or define others, better suiting your preferences - according to such criteria as hash, publisher, site, strong name, or URL). For the purpose of our discussion, we will rely on this default setup, since it matches options you will find in the Security section of a project's Properties window in Visual Basic 2005 Express Edition. Selecting the "Enable ClickOnce Security Settings" checkbox within its interface will allow you to choose between full and partial trust requirements for the current application. "Full trust" option indicates that end-users will need to grant your code unrestricted access to their systems (which is likely to raise security concerns, especially in the case of Web-based installation). On the other hand, if you want to assign a partial trust, you must determine the most limited permissions that still meet the application's requirements (this process can be automated in the full-fledged edition of Visual Studio 2005 by using the Permission Calculator utility). To grant or revoke individual permissions, choose an entry in the "Zone your application will be installed from" listbox (Internet, Local Intranet, or Custom) and modify content of the "Permission required by the application" table.

For example, if you plan to distribute our sample database application via the Web with the partial trust setting, you need to ensure its ability to communicate with the locally stored database. Since these actions correspond to IOPermissions and SQLClientPermissions entries, they would need to be added to the default permission set associated with Internet or Local Intranet default zone settings, depending on the location of your deployment Web server (installation via a network share is by default categorized as originating from "Local Intranet" zone, while local file system path setup is automatically fully trusted). Once you publish the application, your final selection will be recorded in the application manifest.

If an application specifies a permission set that is more extensive than the one granted based on its code group association in its manifest, a user attempting to launch it will be prompted to confirm whether its execution should proceed. This will ensure that privilege elevation takes place only following explicit acknowledgement. If both permissions sets match (for example, if the installation is performed from a remote IIS server and the code can operate within Low Trust limits or a Full Trust code is launched from the local computer) then no privilege elevation is required and the application runs uninterrupted (although there are additional caveats to consider, which we will discuss next). If it turns out that the permission set defined in the manifest is too limited and does not take into account some actions invoked by the code, users will be presented with a warning dialog box informing them about an illegal operation violating local security policy.

It is possible to override the default behavior imposed by a trust level for an individual application by creating more granular rules based on its characteristics, such as a digital signature, publisher, hash, or strong name. For example, when you initiate install of our sample application via the Web or a remote file share, you will probably receive a Security Warning dialog box informing you that its publisher can not be verified (since we have not signed it using a certificate from a trusted source) and asking you to decide whether you want to proceed. (Note that if you click on the "More information..." link in the dialog box, you will be presented with other information regarding the application risk factors, such as the zone it originated from, an indication whether a permanent or temporary installation will take place, or its need for increased level of access to local resources). Your confirmation will automatically update the user-specific list of trusted applications (which you can verify by reviewing the content of the User's Trusted Applications subnode under the Runtime Security Policy node in the .NET Framework 2.0 Configuration console), so any subsequent launches (even if performed via the Web link) will continue without any interruptions. You could accomplish the same result by signing the application manifest using a certificate from a CA trusted by your clients (which is typically done from the Signing section of the project's Properties window in the Visual Basic 2005 Express Edition interface). Another possibility involves copying source files to the local computer (directly or via removable media), which, at deployment time, would associate the application with the My_Computer_Zone code group and its Full Trust permission set. Keep in mind that a ClickOnce application retains its security zone association determined by the location from which it originated even after its installation (i.e. when being launched using a shortcut in All Programs menu).

Finally, note that you can prevent the ability to elevate privileges for a specific Web site (by adding it to the Restricted Zone) or disable default privilege elevation behavior (according to which a user is given authority to approve code execution when presented with the Security Warning dialog box) altogether, by modifying entries residing under the HKLM\Software\Microsoft\.NETFramework\Security\TrustManager\PromptingLevel registry key. For specifics regarding this configuration, refer to the "Modifying TrustManager Configuration" section of the MSDN article Security Considerations for ClickOnce Deployments. If you are looking for more information on other topics we discussed here, check the Code Access Security and Trusted Application Deployment Overview sections on the MSDN Web site.

This concludes our discussion of ClickOnce deployment characteristics in the context of SQL Server 2005 Express Edition. In the next installment of our series, we will turn our attention to its replication capabilities.

» See All Articles by Columnist Marcin Policht

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM