WIX database deployment, installation

Question

During my setup I give an option to use "windows authentication" OR "server authetication". Everything works fine and db gets installed as long as I do "server authentication" and provide db userId and password.

What do I need to do in order install db using "windows authentication" (in wix)? thanks

My product.wxs file:

<Product Id="*" Name="MySetup2" Language="1033" Version="1.0.0.0" Manufacturer="Hewlett-Packard Company" UpgradeCode="bf1da750-c2fe-4026-9d2b-9d291a61a8b5">
    <Package InstallerVersion="200" Compressed="yes" InstallScope="perMachine" />

    <Binary Id="success_bmp" SourceFile="$(sys.SOURCEFILEDIR)success.bmp" />
    <Binary Id="error_bmp" SourceFile="$(sys.SOURCEFILEDIR)error.bmp" />

    <MajorUpgrade DowngradeErrorMessage="A newer version of [ProductName] is already installed." />
    <MediaTemplate />

    <Feature Id="ProductFeature" Title="MySetup2" Level="1" ConfigurableDirectory="INSTALLDIR">
        <ComponentGroupRef Id="ProductComponents" />
        <ComponentGroupRef Id='MvcApp'/>
        <ComponentRef Id='SqlComponent' />
    </Feature>
    <Property Id="WIXUI_INSTALLDIR" Value="INSTALLDIR" />

    <Binary Id="CreateTableScript"  SourceFile="c:\Temp\MyDb.sql" />

    <!-- Specify UI -->
    <UIRef Id="MyWebUI" />

    <!-- .NET Framework 3.0 SP 1 must be installed -->
    <Property Id="FRAMEWORKBASEPATH">
        <RegistrySearch Id="FindFrameworkDir" Root="HKLM" Key="SOFTWARE\Microsoft\.NETFramework" Name="InstallRoot" Type="raw"/>
    </Property>

   <util:User Id="SQLUser" Name="[DATABASE_USERNAME]" Password="[DATABASE_PASSWORD]" />   
</Product>


<Fragment>
    <Directory Id="TARGETDIR" Name="SourceDir">
        <Directory Id="ProgramFilesFolder">
            <Directory Id="INSTALLDIR" Name="MySetup2" >
                <!--<Component Id="Web.config" Guid="2ED81B77-F153-4003-9006-4770D789D4B6">
                    <File Id="Web.config" Name="Web.config" Source="$(var.SolutionDir)MvcApplication4\Web.config" DiskId="1" KeyPath="yes" />
                    <util:XmlFile Id="AppSettingsAddNode" File="[INSTALLDIR]Web.config" Action="createElement" ElementPath="/configuration/appSettings" Name="add" Sequence="1" />
                    <util:XmlFile Id="AppSettingsKeyAttribute" Action="setValue" File="[INSTALLDIR]Web.config" ElementPath="/configuration/appSettings/add" Name="key" Value="AddedDuringInstall" Sequence="2" />
                    <util:XmlFile Id="AppSettingsValueAttribute" Action="setValue" File="[INSTALLDIR]Web.config" ElementPath="/configuration/appSettings/add" Name="value" Value="This text was added during installation." Sequence="3" />
                </Component>
                <Directory Id="binFolder" Name="bin">
                    <Component Id="MvcApplication4.dll" Guid="7FC6DA37-12E5-463d-8E7E-08F73E40CCF2">
                        <File Id="MvcApplication4.dll" Name="MvcApplication4.dll" Source="$(var.SolutionDir)MvcApplication4\Bin\MvcApplication4.dll" DiskId="1" KeyPath="yes" />
                    </Component>
                </Directory>-->

            </Directory>            
        </Directory>


        <Component Id="SqlComponent" Guid="C50999A0-02FD-42d5-9F65-7375318DD328">
            <sql:SqlDatabase Id="SqlDatabase" 
                            Database="[DATABASE_NAME]" 
                            Server="[DATABASE_SERVER]" 
                            CreateOnInstall="yes" 
                            DropOnUninstall="yes" 
                            User="SQLUser">
                <sql:SqlScript Id="CreateTableScript" ExecuteOnInstall="yes" BinaryKey="CreateTableScript" />
            </sql:SqlDatabase>  
        </Component>



    </Directory>

</Fragment>

<Fragment>
    <ComponentGroup Id="ProductComponents" Directory="INSTALLDIR">
        <!-- TODO: Remove the comments around this Component element and the ComponentRef below in order to add resources to this installer. -->
        <!-- <Component Id="ProductComponent"> -->
        <!-- TODO: Insert files, registry keys, and other resources here. -->
        <!-- </Component> -->
    </ComponentGroup>
</Fragment>

When I modified this section and removed "User" tag it works using windows authentication. How do I add this tag OR remove it (Condition) based on UI's selection.

        <sql:SqlDatabase Id="SqlDatabase" 
                        Database="[DATABASE_NAME]" 
                        Server="[DATABASE_SERVER]" 
                        CreateOnInstall="yes" 
                        DropOnUninstall="yes" 
                        User="SQLUser">

Solution 1

I solved it and created WIX Database Installer like so:

Product.wxs:

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

    <Product Id="*" Name="MySetup2" Language="1033" Version="1.0.0.0" Manufacturer="Hewlett-Packard Company" UpgradeCode="bf1da750-c2fe-4026-9d2b-9d291a61a8b5">
        <Package InstallerVersion="200" Compressed="yes" InstallScope="perMachine" />

        <Binary Id="success_bmp" SourceFile="$(sys.SOURCEFILEDIR)success.bmp" />
        <Binary Id="error_bmp" SourceFile="$(sys.SOURCEFILEDIR)error.bmp" />

        <MajorUpgrade DowngradeErrorMessage="A newer version of [ProductName] is already installed." />
        <MediaTemplate />

        <Feature Id="ProductFeature" Title="MySetup2" Level="1" ConfigurableDirectory="INSTALLDIR">
            <ComponentGroupRef Id="ProductComponents" />
            <ComponentRef Id='SqlComponent.IntegratedAuthentication' />
            <ComponentRef Id='SqlComponent.SQLAuthentication' />
        </Feature>
        <Property Id="WIXUI_INSTALLDIR" Value="INSTALLDIR" />

        <Binary Id="SqlScriptSQLAuthentication"  SourceFile="c:\Temp\MyDb.sql" />
        <Binary Id="SqlScriptIntegratedAuthentication"  SourceFile="c:\Temp\MyDb.sql" />

        <!-- .NET Framework 3.0 SP 1 must be installed -->
        <Property Id="FRAMEWORKBASEPATH">
            <RegistrySearch Id="FindFrameworkDir" Root="HKLM" Key="SOFTWARE\Microsoft\.NETFramework" Name="InstallRoot" Type="raw"/>
        </Property>
        <UIRef Id="MyWebUI" />
       <util:User Id="SQLUser" Name="[DATABASE_USERNAME]" Password="[DATABASE_PASSWORD]" />

        <Property Id='DATABASE_USERNAME'></Property>
        <Property Id='DATABASE_PASSWORD'></Property>
        <Property Id='DATABASE_NAME'></Property>
        <Property Id="DATABASE_SERVER">(local)</Property>
        <Property Id="DATABASE_LOGON_TYPE">DatabaseIntegratedAuth</Property>
        <Property Id="USEINTEGRATEDSECURITY" Secure="yes"></Property>
    </Product>


    <Fragment>
        <Directory Id="TARGETDIR" Name="SourceDir">
            <Directory Id="ProgramFilesFolder">
                <Directory Id="INSTALLDIR" Name="MySetup2" >
                </Directory>            
            </Directory>

            <Component Id='SqlComponent.SQLAuthentication' Guid='665D641C-3570-4b96-9CA5-2B4C12594A35' KeyPath='yes'>
                <Condition><![CDATA[USEINTEGRATEDSECURITY<>1]]></Condition>
                <sql:SqlDatabase Id='SqlDatabase.SQLAuthentication' Database='[DATABASE_NAME]' User='SQLUser' Server='[DATABASE_SERVER]' CreateOnInstall='yes' DropOnUninstall='yes' ContinueOnError='no' />
                <sql:SqlScript Id='SqlScriptSQLAuthentication' BinaryKey='SqlScriptSQLAuthentication' SqlDb='SqlDatabase.SQLAuthentication' ExecuteOnInstall='yes' />
            </Component>

            <Component Id='SqlComponent.IntegratedAuthentication' Guid='E5DF48AE-2338-4029-9FDF-8DAA6AD0216D' KeyPath='yes'>
                <Condition>USEINTEGRATEDSECURITY = 1</Condition>
                <sql:SqlDatabase Id='SqlDatabase.IntegratedAuthentication' Database='[DATABASE_NAME]' Server='[DATABASE_SERVER]' CreateOnInstall='yes' DropOnUninstall='yes' ContinueOnError='no' />
                <sql:SqlScript Id='SqlScriptIntegratedAuthentication' BinaryKey='SqlScriptIntegratedAuthentication' SqlDb='SqlDatabase.IntegratedAuthentication' ExecuteOnInstall='yes' />
            </Component>

        </Directory>

    </Fragment>

    <Fragment>
        <ComponentGroup Id="ProductComponents" Directory="INSTALLDIR">
            <!-- TODO: Remove the comments around this Component element and the ComponentRef below in order to add resources to this installer. -->
            <!-- <Component Id="ProductComponent"> -->
            <!-- TODO: Insert files, registry keys, and other resources here. -->
            <!-- </Component> -->
        </ComponentGroup>
    </Fragment>
</Wix>

Notice "Condition" operator based on Windows authentication vs. Server authentication.

Custom Action:

namespace CustomActions
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using System.Diagnostics;
    using System.Security.Principal;
    using Microsoft.Deployment.WindowsInstaller;
    using Microsoft.SqlServer.Management.Smo;
    using View = Microsoft.Deployment.WindowsInstaller.View;

    public static class CustomActions
    {
        #region Public Methods and Operators

        [CustomAction]
        public static ActionResult EnumerateSqlServers(Session session)
        {
            if (null == session)
            {
                throw new ArgumentNullException("session");
            }

            session.Log("EnumerateSQLServers: Begin");

            // Check if running with admin rights and if not, log a message to
            // let them know why it's failing.
            if (false == HasAdminRights())
            {
                session.Log("EnumerateSQLServers: " + "ATTEMPTING TO RUN WITHOUT ADMIN RIGHTS");
                return ActionResult.Failure;
            }

            ActionResult result;

            DataTable dt = SmoApplication.EnumAvailableSqlServers(false);
            DataRow[] rows = dt.Select(string.Empty, "IsLocal desc, Name asc");
            result = EnumSqlServersIntoComboBox(session, rows);

            session.Log("EnumerateSQLServers: End");
            return result;
        }

        [CustomAction]
        public static ActionResult VerifySqlConnection(Session session)
        {
            try
            {
                //Debugger.Break();

                session.Log("VerifySqlConnection: Begin");

                var builder = new SqlConnectionStringBuilder
                                  {
                                      DataSource = session["DATABASE_SERVER"],
                                      InitialCatalog = "master",
                                      ConnectTimeout = 5
                                  };

                if (session["DATABASE_LOGON_TYPE"] != "DatabaseIntegratedAuth")
                {
                    builder.UserID = session["DATABASE_USERNAME"];
                    builder.Password = session["DATABASE_PASSWORD"];
                }
                else
                {
                    builder.IntegratedSecurity = true;
                }

                using (var connection = new SqlConnection(builder.ConnectionString))
                {
                    if (connection.CheckConnection(session))
                    {
                        session["ODBC_CONNECTION_ESTABLISHED"] = "1";
                    }
                    else
                    {
                        session["ODBC_CONNECTION_ESTABLISHED"] = string.Empty;
                    }
                }

                session.Log("VerifySqlConnection: End");
            }
            catch (Exception ex)
            {
                session.Log("VerifySqlConnection: exception: {0}", ex.Message);
                throw;
            }

            return ActionResult.Success;
        }

        #endregion

        #region Methods

        private static ActionResult EnumSqlServersIntoComboBox(Session session, IEnumerable<DataRow> rows)
        {
            try
            {
                //Debugger.Break();

                session.Log("EnumSQLServers: Begin");

                View view = session.Database.OpenView("DELETE FROM ComboBox WHERE ComboBox.Property='DATABASE_SERVER'");
                view.Execute();

                view = session.Database.OpenView("SELECT * FROM ComboBox");
                view.Execute();

                Int32 index = 1;
                session.Log("EnumSQLServers: Enumerating SQL servers");
                foreach (DataRow row in rows)
                {
                    String serverName = row["Name"].ToString();

                    // Create a record for this web site. All I care about is
                    // the name so use it for fields three and four.
                    session.Log("EnumSQLServers: Processing SQL server: {0}", serverName);

                    Record record = session.Database.CreateRecord(4);
                    record.SetString(1, "DATABASE_SERVER");
                    record.SetInteger(2, index);
                    record.SetString(3, serverName);
                    record.SetString(4, serverName);

                    session.Log("EnumSQLServers: Adding record");
                    view.Modify(ViewModifyMode.InsertTemporary, record);
                    index++;
                }

                view.Close();

                session.Log("EnumSQLServers: End");
            }
            catch (Exception ex)
            {
                session.Log("EnumSQLServers: exception: {0}", ex.Message);
                throw;
            }

            return ActionResult.Success;
        }

        private static bool HasAdminRights()
        {
            WindowsIdentity identity = WindowsIdentity.GetCurrent();
            var principal = new WindowsPrincipal(identity);
            return principal.IsInRole(WindowsBuiltInRole.Administrator);
        }

        private static bool CheckConnection(this SqlConnection connection, Session session)
        {
            try
            {
                if (connection == null)
                {
                    return false;
                }

                connection.Open();
                var canOpen = connection.State == ConnectionState.Open;
                connection.Close();

                return canOpen;
            }
            catch (SqlException ex)
            {
                session["ODBC_ERROR"] = ex.Message;
                return false;
            }
        }

        #endregion
    }
}

MyWebUI.wxs

<?xml version="1.0" encoding="UTF-8"?>
<Wix xmlns="http://schemas.microsoft.com/wix/2006/wi">
    <Fragment>
        <EnsureTable Id='ComboBox'/>

         <!--The custom action DLL itself.-->
        <Binary Id="WebAppCA"
                SourceFile="C:\Temp\MvcApplication4a\CustomActions\bin\Debug\CustomActions.CA.dll" />

         <!--The custom action to enumerate the web sites and app pools into the 
         appropriate combo boxes.-->
        <CustomAction Id="EnumerateSqlServers"
                      BinaryKey="WebAppCA"
                      DllEntry="EnumerateSqlServers"
                      Execute="immediate"
                      Return="check" />

        <CustomAction Id="VerifySqlConnection"
              BinaryKey="WebAppCA"
              DllEntry="VerifySqlConnection"
              Execute="immediate"
              Return="check" />

         <!--Make sure the enumerate web sites and app pools custom action gets 
        called, but only called if we are doing and install.--> 
        <InstallUISequence>
            <Custom Action="EnumerateSqlServers"
                    After="CostFinalize"
                    Overridable="yes">NOT Installed</Custom>
        </InstallUISequence>


        <!-- MyWeb UI -->
        <UI Id="MyWebUI">

            <UIRef Id="WixUI_FeatureTree" />
            <UIRef Id="WixUI_ErrorProgressText" />

            <!-- Injection of custom UI. -->
            <Publish Dialog="LicenseAgreementDlg" Control="Next" Event="NewDialog" Value="DatabaseInformationDlg">LicenseAccepted = "1"</Publish>            
        </UI>

    </Fragment>
</Wix>

UIDialogs.wxs:

<?xml version="1.0" encoding="UTF-8"?>
<Wix xmlns="http://schemas.microsoft.com/wix/2006/wi">
    <Fragment>
        <UI>
            <!--Define the dialog to get the Server and Database name information from the user-->
            <Dialog Id="DatabaseInformationDlg" Width="370" Height="270" Title="[ProductName] [Setup]" NoMinimize="yes">

                <Control Id="ServerLabel" Type="Text" X="20" Y="62" Width="80" Height="25" NoPrefix="yes" Text="SQL Database:" />
                <Control Id="Server" Type="ComboBox" Height="16" Width="180" X="110" Y="60" Property="DATABASE_SERVER">
                    <ComboBox Property="DATABASE_SERVER">
                        <ListItem Text="[DATABASE_SERVER]" Value="[DATABASE_SERVER]" />
                    </ComboBox>
                    <Publish Property="LOGON_VALID" Value="0">1</Publish>
                </Control>

                <Control Id="DatabaseType" Type="RadioButtonGroup" X="20" Y="100" Width="290" Height="40" Property="DATABASE_LOGON_TYPE">
                    <RadioButtonGroup Property="DATABASE_LOGON_TYPE">
                        <RadioButton Value="DatabaseIntegratedAuth" X="0" Y="0" Width="290" Height="16" Text="Trusted (Windows Authentication)" />
                        <RadioButton Value="DatabaseAccount" X="0" Y="20" Width="290" Height="16" Text="Specify Username and Password (SQL Authentication)" />
                    </RadioButtonGroup>
                </Control>


                <!-- Login -->
                <Control Type="Text" Id="UsernameLabel" Width="50" Height="15" X="40" Y="150" Text="&amp;Login:">
                    <Condition Action="disable"><![CDATA[DATABASE_LOGON_TYPE <> "DatabaseAccount"]]></Condition>
                    <Condition Action="enable"><![CDATA[DATABASE_LOGON_TYPE = "DatabaseAccount"]]></Condition>
                </Control>
                <Control Id="Username" Type="Edit" X="110" Y="145" Width="180" Height="18" Property="DATABASE_USERNAME" Text="{80}">
                    <Condition Action="disable"><![CDATA[DATABASE_LOGON_TYPE <> "DatabaseAccount"]]></Condition>
                    <Condition Action="enable"><![CDATA[DATABASE_LOGON_TYPE = "DatabaseAccount"]]></Condition>
                    <Publish Property="LOGON_VALID" Value="0">1</Publish>
                </Control>

                <!-- Password -->
                <Control Type="Text" Id="PasswordLabel" Width="50" Height="15" X="40" Y="173" Text="&amp;Password:">
                    <Condition Action="disable"><![CDATA[DATABASE_LOGON_TYPE <> "DatabaseAccount"]]></Condition>
                    <Condition Action="enable"><![CDATA[DATABASE_LOGON_TYPE = "DatabaseAccount"]]></Condition>
                </Control>
                <Control Id="Password" Type="Edit" X="110" Y="170" Width="180" Height="18" Property="DATABASE_PASSWORD" Text="{80}" Password="yes" >
                    <Condition Action="disable"><![CDATA[DATABASE_LOGON_TYPE <> "DatabaseAccount"]]></Condition>
                    <Condition Action="enable"><![CDATA[DATABASE_LOGON_TYPE = "DatabaseAccount"]]></Condition>
                </Control>

                <Control Id="Test" Type="PushButton" X="40" Y="197" Width="100" Height="17" Text="Test Connection">
                    <Condition Action="disable"><![CDATA[DATABASE_SERVER = ""]]></Condition>
                    <Condition Action="enable"><![CDATA[DATABASE_SERVER <> ""]]></Condition>
                     <!--test connection--> 
                    <Publish Property="ODBC_SERVER" Value="[DATABASE_SERVER]" Order="1">1</Publish>
                    <Publish Property="ODBC_SERVER" Value="[ComputerName]" Order="1"><![CDATA[DATABASE_SERVER = "(local)"]]></Publish>
                    <Publish Property="ODBC_CONNECTION_STRING" Value="Driver=SQL Server;Server=[ODBC_SERVER],1433;Uid=[DATABASE_USERNAME];Pwd=[DATABASE_PASSWORD]" Order="2">1</Publish>
                    <Publish Event="DoAction" Value="VerifySqlConnection" Order="3">1</Publish>
                    <Publish Property="LOGON_VALID" Value="1" Order="4"><![CDATA[ODBC_CONNECTION_ESTABLISHED]]></Publish>
                    <Publish Property="LOGON_VALID" Value="0" Order="4"><![CDATA[NOT ODBC_CONNECTION_ESTABLISHED]]></Publish>
                    <Publish Property="LOGON_ERROR" Value="Unexpected Error" Order="4"><![CDATA[(NOT ODBC_CONNECTION_ESTABLISHED) AND (ODBC_ERROR = "")]]></Publish>
                    <Publish Property="LOGON_ERROR" Value="[ODBC_ERROR]" Order="4"><![CDATA[NOT ODBC_CONNECTION_ESTABLISHED]]></Publish>
                    <Publish Event="SpawnDialog" Value="InvalidLogonDlg" Order="5"><![CDATA[NOT ODBC_CONNECTION_ESTABLISHED]]></Publish>
                </Control>
                <Control Id="TestResult_Success" Type="Bitmap" X="210" Y="10" Width="24" Height="24" Text="success_bmp">
                    <Condition Action="hide"><![CDATA[LOGON_VALID <> 1]]></Condition>
                    <Condition Action="show"><![CDATA[LOGON_VALID = 1]]></Condition>
                </Control>
                <Control Id="TestResult_Failure" Type="Bitmap" X="210" Y="10" Width="24" Height="24" Text="error_bmp">
                    <Condition Action="hide"><![CDATA[LOGON_VALID = 1]]></Condition>
                    <Condition Action="show"><![CDATA[LOGON_VALID <> 1]]></Condition>
                </Control>

                <!-- Back button -->
                <Control Id="Back" Type="PushButton" X="180" Y="243" Width="56" Height="17" Text="&amp;Back">
                    <Publish Event="NewDialog" Value="LicenseAgreementDlg">1</Publish>
                </Control>
                <Control Id="Next" Type="PushButton" X="236" Y="243" Width="56" Height="17" Default="yes" Text="&amp;Next">
                    <Publish Property="DATABASE_LOGON_TYPE" Value="[DATABASE_LOGON_TYPE]">1</Publish>
                    <Publish Property="DATABASE_SERVER" Value="[DATABASE_SERVER]">1</Publish>
                    <Publish Property="DATABASE_USERNAME" Value="[DATABASE_USERNAME]"><![CDATA[DATABASE_LOGON_TYPE = "DatabaseAccount"]]></Publish>
                    <Publish Property="DATABASE_PASSWORD" Value="[DATABASE_PASSWORD]"><![CDATA[DATABASE_LOGON_TYPE = "DatabaseAccount"]]></Publish>
                    <Publish Property="DATABASE_USERNAME"><![CDATA[DATABASE_LOGON_TYPE <> "DatabaseAccount"]]></Publish>
                    <Publish Property="DATABASE_PASSWORD"><![CDATA[DATABASE_LOGON_TYPE <> "DatabaseAccount"]]></Publish>

                    <Publish Property="USEINTEGRATEDSECURITY" Value="0"><![CDATA[DATABASE_LOGON_TYPE = "DatabaseAccount"]]></Publish>
                    <Publish Property="USEINTEGRATEDSECURITY" Value="1"><![CDATA[DATABASE_LOGON_TYPE <> "DatabaseAccount"]]></Publish>

                    <Condition Action="disable"><![CDATA[LOGON_VALID <> 1]]></Condition>
                    <Condition Action="enable"><![CDATA[LOGON_VALID = 1]]></Condition>
                    <Publish Event="NewDialog" Value="DatabaseNameDlg">1</Publish>
                </Control>

                <Control Id="Cancel" Type="PushButton" X="304" Y="243" Width="56" Height="17" Cancel="yes" Text="Cancel">
                    <Publish Event="SpawnDialog" Value="CancelDlg">1</Publish>
                </Control>

                <Control Id="BannerBitmap" Type="Bitmap" X="0" Y="0" Width="370" Height="44" TabSkip="no" Text="WixUI_Bmp_Banner" />
                <Control Id="Description" Type="Text" X="25" Y="23" Width="280" Height="15" Transparent="yes" NoPrefix="yes">
                    <Text>Please enter a SQL instance and database name.</Text>
                </Control>
                <Control Id="BottomLine" Type="Line" X="0" Y="234" Width="370" Height="0" />
                <Control Id="Title" Type="Text" X="15" Y="6" Width="200" Height="15" Transparent="yes" NoPrefix="yes">
                    <Text>{\WixUI_Font_Title}SQL instance and database information.</Text>
                </Control>
                <Control Id="BannerLine" Type="Line" X="0" Y="44" Width="370" Height="0" />
            </Dialog>

            <!-- INVALIDE SQL LOGIN -->
            <Dialog Id="InvalidLogonDlg" Width="260" Height="105" Title="Invalid Logon">
                <Control Id="Return" Type="PushButton" X="102" Y="77" Width="56" Height="17" Default="yes" Cancel="yes" Text="OK">
                    <Publish Event="EndDialog" Value="Return">1</Publish>
                </Control>
                <Control Id="Text" Type="Text" X="48" Y="15" Width="194" Height="50" Text="[ODBC_ERROR]" />
                <Control Id="Icon" Type="Icon" X="15" Y="15" Width="24" Height="24" FixedSize="yes" IconSize="32" Text="WixUI_Ico_Exclam" />
            </Dialog>

            <!-- DATABASE NAME -->
            <Dialog Id="DatabaseNameDlg" Width="370" Height="270" Title="[ProductName] [Setup]" NoMinimize="yes">
                <!-- Connection String -->
                <Control Id="DatabaseStringLabel" Type="Text" X="45" Y="73" Width="100" Height="15" TabSkip="no" Text="Database name:" />
                <Control Id="DatabaseStringEdit" Type="Edit" X="45" Y="90" Width="220" Height="18" Property="DATABASE_NAME" Text="{100}" />

                <Control Id="DatabaseStringLabel123" Type="Text" X="45" Y="120" Width="100" Height="15" TabSkip="no" Text="Use Win: [USEINTEGRATEDSECURITY]--" />

                <!-- Back button -->
                <Control Id="Back" Type="PushButton" X="180" Y="243" Width="56" Height="17" Text="&amp;Back">
                    <Publish Event="NewDialog" Value="DatabaseInformationDlg">1</Publish>
                </Control>
                <Control Id="Next" Type="PushButton" X="236" Y="243" Width="56" Height="17" Default="yes" Text="&amp;Next">
                    <Publish Event="NewDialog" Value="CustomizeDlg">
                        <!--if settings are correct, allow next dialog-->
                        <![CDATA[DATABASE_NAME <> ""]]>
                    </Publish>
                </Control>
                <Control Id="Cancel" Type="PushButton" X="304" Y="243" Width="56" Height="17" Cancel="yes" Text="Cancel">
                    <Publish Event="SpawnDialog" Value="CancelDlg">1</Publish>
                </Control>
                <Control Id="BannerBitmap" Type="Bitmap" X="0" Y="0" Width="370" Height="44" TabSkip="no" Text="WixUI_Bmp_Banner" />
                <Control Id="Description" Type="Text" X="25" Y="23" Width="280" Height="15" Transparent="yes" NoPrefix="yes">
                    <Text>Please enter database configuration</Text>
                </Control>
                <Control Id="BottomLine" Type="Line" X="0" Y="234" Width="370" Height="0" />
                <Control Id="Title" Type="Text" X="15" Y="6" Width="200" Height="15" Transparent="yes" NoPrefix="yes">
                    <Text>{\WixUI_Font_Title}Database Settings</Text>
                </Control>
                <Control Id="BannerLine" Type="Line" X="0" Y="44" Width="370" Height="0" />
            </Dialog>

        </UI>
    </Fragment>
</Wix>

OUTPUT: a working db installer:

Solution 2

See: SqlDatabase Element (Sql Extension)

Remarks

The User attribute references credentials specified in a User element. If a user is not specified then Windows Authentication will be used by default using the credentials of the user performing the install to execute sql strings, etc.

Comments

[ShaneKm]

weird. having "User="SQLUser" while using windows authentication my setup fails. "Error - 2147217843: failed to create SQL database:..error details:unknown". HOWEVER, when I remove "User="SQLUser" from sql:SqlDatabase tag it works. ???

[ShaneKm]

so how do I remove User tag / or added it based on choice coming from UI?

[Christopher Painter]

Have you tried using a property like you did for Server and Database?

[ShaneKm]

you mean this: <sql:SqlDatabase Id="SqlDatabase" Database="[DATABASE_NAME]" Server="[DATABASE_SERVER]" CreateOnInstall="yes" DropOnUninstall="yes" User="[WIN_USER]"> ??. this throws an error.

[Christopher Painter]

Looks like the User attribute isn't formatted rather a reference to a User element. The user element however has Name and Password attributes that are formatted. I'm wondering if you name User@Name=[SQLUSERNAME] and have SQLUSERNAME null for the case of SSPI if it would all work out. I have to admit I've never actually done this in WiX because I use a combination of WiX and InstallShield where IS handles this all built in for me. The trick to understanding all this would be to build your example and look at it in ORCA and go from there.

[Phil]

Your custom action makes reference to "SmoApplication.EnumAvailableSqlServers(false)" but where is this? Could you possibly share this code snippet? Thanks!

[Phil]

Nevermind! Had to add a reference to the Microsoft.SqlServer.Smo.dll assembly that was in my SQL Server SDK folder.

[Erti-Chris Eelmaa]

Beautiful :-). By the way, I didn't understand the "admin" check in CustomAction. Why would that be needed to enumerate the list? And how would one achieve administrator rights?

[Jon]

To get this to work for me, with UAC, I had to remove return ActionResult.Failure from the EnumerateSqlServers method.

[LeoVannini]

Hey Man, I just wanted to let you know that your code posted here saved today. I wish I could give this example more than one point. Thanks again.

[FakeCaleb]

I found it better to use SqlDataSourceEnumerator.Instance.GetDataSources(); instead of SmoApplication.EnumAvailableSqlServers(false) if people have issues with this installing on multiple PCs