ClickOnce Deployment: Customizing SQL Server Express
 
Published: 26 Sep 2005
Unedited - Community Contributed
Abstract
ClickOnce Deployment, new to Visual Studio 2005, allows you to deploy your Windows Forms application to a web server from which a user can download and install the application. If SQL Server Express is included in the install, its authentication will default to the sa user with an unknown password. Terry Voss shows how to customize the installation of SQL Server Express.
by Terry Voss
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 35446/ 83

Introduction

ClickOnce Deployment is a setup capability that is built into your Windows Forms project. In Visual Studio 2005, select Project Properties from the Project menu, and click on the Publish tab. From there, after specifying a few things, you can publish to a web site or other location that can act as a setup server for your application. A web page is created that allows your project to be downloaded. If your application needs .NET Framework 2.0 to be installed, and maybe also SQL Server Express Edition, you can specify these prequisites since ClickOnce Deployment has the capability to build into the setup.exe file the instructions to install them after checking for what is already installed, and maybe after getting the end user's acceptance of a EULA. Without a basic understanding of how this bootstrapping works through manifest files, one gets the default install of Windows Authentication with a system administrator (sa) user with an unknown password. This article shows how to modify this. (For an introduction to ClickOnce Deployment and more information about the Bootstrapper, see the links provided at the end of this article.)

Selecting the Prerequisites

From the Project menu, choose Project Properties, select the Publish tab, and then click on the Prerequisites button to view the following dialog.

The only reason I see to download from a location other than the component vendor's web site is if you think the vendor’s site might be busy and your own web site would be more available.

Where are the Manifest Files?

In Windows Explorer, navigate to the following folder:

    [drive]\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bootstrapper\Packages\ 

Within this folder you will see these subfolders:

There is one folder for each application for which the Bootstrapper has defined manifest files. When the final version of Visual Studio 2005 is released, there will be additional options.

Inside the SqlExpress folder we see the following:

Manifest files are XML files, and there are two types of manifests. Product manifests and Package manifests have similar elements and attributes, but Product manifests are for the non-localization-specific files of the application, and Package manifests are for the localization-specific files. It happens that dotnetfx.exe is non-language specific, because there is a separate language pack to handle that aspect. Therefore, dotnetfx.exe is in a dotnetfx folder directly within the Packages folder, and has a product.xml manifest file. Contents for the en folder are shown below:

The install file for SQL Server Express, sqlexpr.exe, is in the en folder (English folder) below the SqlExpress folder, since that file is language-specific and associated with a package.xml manifest file. Note that in the SqlExpress folder we have the SqlExpressChk.exe file, which is an application that checks to see if SqlExpress is already installed on the target machine. This application is non-language-specific and therefore has a product.xml manifest file. You may examine both, but we primarily want to look at sqlexpr.exe's package.xml file to see if we can modify the install to use SqlAuthentication with a known sa password, which needs to be a strong password.

Listing 1: Package.xml Manifest File for Sqlexpr.exe

<?xml version="1.0" encoding="utf-8" ?>
<Package 
   xmlns="http://schemas.microsoft.com/developer/2004/01/bootstrapper" 
   Name="DisplayName" Culture="Culture" LicenseAgreement="eula.txt"
>
   <PackageFiles CopyAllPackageFiles="false">
      <PackageFile Name="sqlexpr.exe" HomeSite="SqlExprExe"
            PublicKey="3082010A0282010100CF38F7E1A4275EF8F0CCAACEFB3ADE3B6231460546BBF5B6051
            AD3B3ACC29F0F4C670828C44310F53B75797F6A91F4D633C861BFFA9190007AF0791D5D6870F690B
            29877B5031D2F9B9D9B75892F4A01417C9E7CCB87439BF49674999E98C1CF40575816F6C0D59216E
            52485718F9949ED557C65C91F380023C53EAB11D6296CC69EA0705B7DD537D4677720C306CE85F84
            E3480A035C41C53320157EFB128BD6C01E3AD40BC80A90949DB36E337F41D49AA2AA76BD019D3CC8
            E9DD686467A134AD64519A553B3E2782F2E35976B4CC6E81AB0D3D1249069ABCEFC696E3E4CFB024
            162DC07985D7E5CA74C27316B564CE198D8E0D11D718D3D2AC07F714DFFCF0203010001" />
      <PackageFile Name="eula.txt" />
   </PackageFiles>
   <Commands Reboot="Defer">
      <Command PackageFile="sqlexpr.exe" 
            Arguments="-q /norebootchk /qn reboot=ReallySuppress addlocal=all 
            instancename=SQLEXPRESS sapwd=sssaaa999 securitymode=SQL 
            SCCCHECKLEVEL=IncompatibleComponents:1 SQLAUTOSTART=1"
            EstimatedInstalledBytes="225000000" EstimatedInstallSeconds="420">
         <InstallConditions>
            <BypassIf Property="SQLExpressInstalled" Compare="ValueEqualTo" Value="0" />
            <BypassIf Property="VersionNT" Compare="VersionGreaterThanOrEqualTo" 
               Value="5.1" />
            <FailIf Property="AdminUser" Compare="ValueEqualTo" Value="false" 
               String="AdminRequired" />
            <FailIf Property="Version9x" Compare="ValueExists" String="InvalidPlatform" />
            <FailIf Property="VersionNT" Compare="VersionLessThan" Value="5.0.4" 
               String="InvalidPlatform2K" />
         </InstallConditions>
         <ExitCodes>
            <ExitCode Value="0" Result="Success" />
            <ExitCode Value="1641" Result="SuccessReboot" />
            <ExitCode Value="3010" Result="SuccessReboot" />
            <ExitCode Value="50037" Result="Fail" String="MissingMSXml" />
            <ExitCode Value="50251" Result="Fail" String="MissingMSXml" />
            <ExitCode Value="50198" Result="Fail" String="InsufficientHardware" />
            <ExitCode Value="50236" Result="Fail" String="InsufficientHardware" />
            <ExitCode Value="50222" Result="Fail" String="InvalidPlatformOSServicePacks" />
            <ExitCode Value="70003" Result="Fail" String="InvalidPlatformOSServicePacks" />
            <ExitCode Value="50247" Result="Fail" String="InvalidPaltformIE" />
            <ExitCode Value="50248" Result="Fail" String="InvalidPaltformIE" />
            <ExitCode Value="70004" Result="Fail" String="AnotherInstanceRunning" />
            <ExitCode Value="70032" Result="Fail" String="BetaComponentsFailure" />
            <ExitCode Value="70033" Result="Fail" String="InvalidPlatformArchitecture" />
            <DefaultExitCode Result="Fail" FormatMessageFromSystem="true" 
               String="GeneralFailure" />
         </ExitCodes>
      </Command>
   </Commands>
   <!-- Strings, removed from listing -->
</Package>

Within the Command element of the Commands element, there is an Arguments parameter. To this parameter I have added two arguments: sapwd=sssaaa999 (replace sssaaa999 with your password) and securitymode=SQL. The other arguments were already there. So it is as simple as that to add SqlAuthentication with a known password, except there are two gotchas that should be handled by the time the final version of Visual Studio 2005 is released.  Note that there are two Command elements inside the Commands element. They are identical and both relate to sqlexpr.exe. I found that if I didn’t add the two new arguments into both, the bootstrapper read the second command without the additional arguments, and created a default install anyway. If the second one was deleted it probably would work fine as a solution to this gotcha. Then, after testing this, I created an install that did not finish, which gave me an error:

    This app requires that your Windows be updated to: Microsoft.ReportViewer.Winforms

Until this problem is corrected in the final version of Visual Studio 2005, the workaround is to open the project's properties, select the Pubish tab, and click on the Application Files button. You will see two entries relating to ReportViewer that need their Publish Status changed to Include rather than the default status of Include(Auto).

Another thing of which to be aware is that if you turn versioning off (because you don't want a new version every time you publish), you may have to delete the five things that a publish puts on your website or other install server, to ensure your next publish takes over:
1) a folder named something like (for version one) 'myapp_1_0_0_0'
2) a file named 'setup.exe'
3) a file named 'publish.htm'
4) a file named 'myapp.application'
5) a file named 'myapp_1_0_0_0.application'

Summary

For the install of your Visual Studio 2005 Windows Forms application, using .NET Framework 2.0 and SQL Server Express, the user sees a fairly seamless install that looks like one smooth install with no reboots required.  It is impressive that two projects, the custom application and the publishing source website, can work together so automatically not just for the initial install, but for version upgrades as well. It is well worth the time to get to know this technology, since I feel ClickOnce Deployment will have a big impact, but also since manifest files are going to be used everywhere in future Microsoft products.

Links

Deploying Applications: User-Initiated Updates with ClickOnce (this is a PDF download that takes a while, but provides a valuable introduction to ClickOnce Deployment)

Use the Visual Studio 2005 Bootstrapper to Kick-Start Your Installation



User Comments

Title: plz tell me   
Name: Sajid
Date: 2009-01-17 3:45:22 AM
Comment:
hi i want to say that if i start that web setup project then after installation of the instanace it will again ask for installtion of tghe instance how we bypass it. plz reply soon
Title: Re: Small doubt in ClickOnce   
Name: Nilesh
Date: 2008-12-09 3:48:23 PM
Comment:
Hi Rohan,

check this link
http://www.codeproject.com/KB/install/RestoreDBthenInstall.aspx
Title: Small doubt in ClickOnce   
Name: Rohan
Date: 2008-08-19 10:53:45 PM
Comment:
Dear Terry,
Thanks for the great article. Just the solution I was looking for. I just have 2 small doubts about click-once deployment still.

1. If my application uses SQL Express, how can I include and attach my .mdf / .bak files with my project so that the database gets attached to the new instance of SQL.

2. Can I included 2 executing projects in a single click-once application. ie.. Can I have a Windows Form application and another Windows Service application install in a single instanc using the same click-once project ??

Thanks again for the article,
Regard,
Rohan Wadiwala
Title: ClickOnce Deployment: Customizing SQL Server Express Problem   
Name: Satish C
Date: 2007-08-03 4:04:47 AM
Comment:
Getting the following error while installation
Could you please tell whats the problem here?

++++++++++++++++++++++++++++++++++++++
Process exited with code 28001
Status of package 'Windows Installer 3.1' after install is 'InstallSucceeded'
Status of package 'SQL Server 2005 Express Edition' after install is 'InstallFailed'
++++++++++++++++++++++++++++++++++++++


Thanks in anticipation
Satish
Title: ClickOnce Deployment: Customizing SQL Server Express   
Name: Jei G
Date: 2006-09-05 9:41:48 AM
Comment:
Why can't I only set SECURITYMODE only without setting the sa password? I tried to exclude sapwd= attribute the sql express setup failed. Or is it possible to just disable sa login or supply a blank password?
Title: ClickOnce Deployment: Customizing SQL Server Express   
Name: Jei G
Date: 2006-09-05 9:36:07 AM
Comment:
This is definitely a better solution than Microsoft article that suggest modifying registry settings especially to those of us who regard the registry as sacred. Still, I wonder why Microsoft could not come up with a command line way, say through the sqlcmd utility, to change the authentication mode. Thanks
Title: Re: Jerry Kasten's Question   
Name: JLS
Date: 2006-03-24 3:38:28 PM
Comment:
> How does modifying the manifest on my machine affect
> the deployment if SQL Server Express is downloaded
> from Microsoft

The manifest gets compiled into the setup.exe that you (the developer) will deploy. It doesn't stay on your local machine. Then, during the install, setup.exe downloads the SQL Express package from MS if it needs it. The contents of the manifest control whether or not the package is downloaded and how it is launched but obviously (I hope) the actual package from MS is not modified.
Title: ClickOnce Deployment: Customizing SQL Server Express   
Name: Jerry Kasten
Date: 2006-03-20 8:07:29 AM
Comment:
How does modifying the manifest on my machine affect the deployment if SQL Server Express is downloaded from Microsoft, as you suggest?

Product Spotlight
Product Spotlight 





Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2021 ASPAlliance.com  |  Page Processed at 2021-04-13 11:51:33 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search