SQL Server named instance with Visual Studio 2017 Installer project

Software:

1. SQL Server Express 2016
2. Visual Studio 2017

We've been trying without success to get a named instance of SQL Server Express 2016 installed as part of VS Installer Setup Project.

We've tried calling InstallNamedInstance() as follows with given results:

1. Run SQLEXPR_x64_ENU.exe with same commandline args from Administrator cmd window: Succeeds
2. Call InstallNamedInstance() from a Console app and run Console app from Administrator cmd window: Succeeds
3. Install custom action (Both Everyone and Just Me): Fails
4. BeforeInstall event (Both Everyone and Just Me): Fails

I notice that current user when msi runs is NT AUTHORITY\SYSTEM. Whenever it fails from Installer project, it fails with below message:

The account that is running SQL Server Setup does not have one or all of the following rights: the right to back up files and directories, the right to manage auditing and the security log and the right to debug programs. To continue, use an account with both of these rights. For more information, see http://msdn.microsoft.com/en-us/library/ms813696.aspx, http://msdn.microsoft.com/en-us/library/ms813959.aspx and http://msdn.microsoft.com/en-us/library/ms813847.aspx.

Is this a limitation of Installer project or am I missing something? Will we have better luck with AdvancedInstaller?

Note that Installer Project's pre-requisite is not working for us because we're having to create a named instance of SQL Server Express and we're not able to see how we can pass commandline arguments to pre-requisite.

private void InstallNamedInstance()
{
    // NOTE: Change below instance name to get unique instances (or uninstall previous instance)
    var InstanceName = "TFPICDATABASES2";
    var proc = new Process();
    // NOTE:
    //  1. Download "SQLServer2016-SSEI-Expr.exe" web installer from https://www.microsoft.com/en-us/download/details.aspx?id=54284
    //  2. Run the web installer and choose 3rd option "Download Media". This will give "SQLEXPR_x64_ENU.exe"
    proc.StartInfo.FileName = @"c:\temp\sql\SQLEXPR_x64_ENU.exe ";
    proc.StartInfo.Arguments = " /Action=Install";
    proc.StartInfo.Arguments += $" /INSTANCEID={InstanceName}";
    proc.StartInfo.Arguments += $" /InstanceName={InstanceName}";
    proc.StartInfo.Arguments += " /ROLE=AllFeatures_WithDefaults";
    proc.StartInfo.Arguments += " /QS";
    proc.StartInfo.Arguments += " /INDICATEPROGRESS=True";
    proc.StartInfo.Arguments += " /IAcceptSQLServerLicenseTerms=True";
    proc.StartInfo.WorkingDirectory = @"c:\temp\sql";

    WriteLog($"FielName: {proc.StartInfo.FileName}; Arguments: {proc.StartInfo.Arguments}; WorkingDir: {proc.StartInfo.WorkingDirectory}");

    proc.StartInfo.UseShellExecute = false;
    proc.OutputDataReceived += (s, e) => WriteLog($"Info: {e.Data}");
    proc.ErrorDataReceived += (s, e) => WriteLog($"Error: {e.Data}");

    var ok = proc.Start();
    // NOTE: Log files are in C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Log
    // Summary.txt gives log of latest installer run. It also creates one folder for each installer attempt
    // and gathers more detailed logs in those folders.
    proc.WaitForExit();
    WriteLog($"{proc.StartInfo.FileName} exited with {proc.ExitCode}");
    if (proc.ExitCode != 0)
    {
        throw new Exception($"SQL Server Express installation failed. Check log file for more details");
    }
}

Summary: In essence, the below states: 1) Disable the custom action to run the SQL Server setup.exe in your current MSI. 2) Create a basic WiX Burn Bundle to kick off the SQL Server setup.exe first, and then kick off your Visual Studio Installer Project-generated MSI afterwards. Or better yet, make the whole MSI in WiX as well. Commercial tools such as Advanced Installer and Installshield are viable options - they feature support for this that is built-in (features vary depending on version of prerequisite).

Burn Bundle-Mockup (inspiration, more inspiration):

Just to try to show how the WiX Burn markup works:

<?xml version="1.0" encoding="UTF-8"?>
<Wix xmlns="http://schemas.microsoft.com/wix/2006/wi" 
     xmlns:bal="http://schemas.microsoft.com/wix/BalExtension"
     xmlns:util="http://schemas.microsoft.com/wix/UtilExtension">

  <Bundle Name="MyCoolTestApp" Version="1.0.0.0" 
          Manufacturer="Someone" UpgradeCode="PUT-GUID-HERE">

    <BootstrapperApplicationRef Id="WixStandardBootstrapperApplication.RtfLicense" />

    <util:FileSearch Path="[WindowsFolder]System32\ucrtbase.dll" Variable="VCDISTINSTALLED"/>

    <Chain>

      <ExePackage SourceFile="vc_redist.x64.exe"
                  DetectCondition="VCDISTINSTALLED"
                  InstallCommand="/q /ACTION=Install"
                  RepairCommand="/q ACTION=Repair /hideconsole" />

      <MsiPackage SourceFile="ShortcutDesktop.msi" />

    </Chain>
  </Bundle>
</Wix>

Technical Cause: I am no expert on Visual Studio Installer Projects - it has to be said - every time. However, these projects have a number of limitations and quirks, as you have discovered. One of the quirks is that all custom actions run in deferred mode and in system context (running as LocalSystem) with no impersonation of the launching user. This is likely the cause of the problem seen - as you state yourself.

Though it is possible to post-process the MSI you get from the VS Installer Projects, it is better to eliminate the use of a custom action to kick off the SQL Server install. More details below. The post processing would involve changing custom action type from 3078 to 1030 so user impersonation is enabled - which also means the custom action does not run elevated by the way - and hence can only succeed if the whole MSI was launched elevated.

Note: Below I suggest to use WiX's Burn feature (Open Source), or an equivalent, capable commercial tool. WiX's Burn feature can be used with MSI files created by Visual Studio 2017 Installer project, or MSI files created by any other tool for that matter (also EXE files). You just plug the VS2017-generated MSI into the WiX Bundle (or the EXE file). WiX can obviously also create MSI files itself (that is what the framework is for). WiX quick start links.

MSI Technology Quirk: Kicking off other installers from MSI custom actions is not good practice. If the other installer is another MSI (and not just a non-MSI setup.exe), then it is not even possible to do so reliably due to technical limitations (no two MSI InstallExecuteSequences can run at the same time because of a mutex that is set during installation). In other words: concurrent MSI installations are forbidden and technically impossible.

Burn: Enter WiX's Burn feature - the downloader / bootstrapper / sequencer tool which runs package installations in sequence from its own wrapper setup.exe. It can install MSI files, EXE files, and other kinds of packages - one after the other without technical limitations like that of MSI's mutex. Serial, not parallel running.

SQL Server Install: You can kick of the SQL Server EXE installer via such a Burn bundle, and you can specify the parameters you list as command line parameters, instead of doing so in managed code (with the runtime requirements that entails). Then you kick off your main MSI afterwards from the same bundle.

Burn Crash Course: There is a learning curve for Burn. It is "fiddly" (it is code / markup - always fiddly), but it is very flexible. I want to add that Advanced Installer seems to have good support for SQL Server deployment, even if have never had the time to investigate properly in detail. Installshield can install EXE files and MSI files in sequence using its Suite projects feature (check the linked screen shot). Not sure of the overall SQL Server support.

Some Burn Sample Links:

• Bootstrapping
• How To: Install the .NET Framework Using Burn
• A nice sample of what Burn can do: https://github.com/frederiksen/Classic-WiX-Burn-Theme.
• My own "Hello World"-style Burn Bundle markup (with further links).
• Neil Sleightholm: http://neilsleightholm.blogspot.com/2012/05/wix-burn-tipstricks.html
• Burn allows you to write your own setup GUI application (advanced): https://github.com/rstropek/Samples/tree/master/WiXSamples/CustomBurnUI (more samples up one level)

Some Links:

• Wix Burn: How to stop Bootstrapper from installing itself
• Wix Burn helloworld example
• Comprehensive list of command line flags/options for Burn/bootstrapper in WiX
• Wix Burn - custom template
• Adding language transform .mst's to Burn Bundle Chain? (too many links)

I suspect your custom action fails under the SYSTEM account because of the errors mentioned in these articles:

• MSFT support - SQL Server installation fails if the Setup account doesn't have certain user rights
• How to resolve error Sql Server Setup Failed Setup Account Privileges while installing SQL Server

In Advanced Installer, the custom action that installs SQL server does not run under the system account, it runs under the account that started the installation (which must have admin credentials). The custom action (a dedicated exe launcher bundled by Advanced Installer) is configured to require admin elevation (which I suspect you cannot configure in VS), therefore the install process for SQL server is running elevated using a regular user account from the machine, not NT SYSTEM.