[wix-users] Letting end user give userName and Panssword and test SQL server connection

Brian Enderle brianke at gmail.com
Mon Mar 21 06:59:11 PDT 2016


I do a SQL call within a CustomAction using the username and password that
the user entered into a screen during the install process.

Here is the screen to capture the user info:

    <UI>
      <Dialog Id="AcctInfoDlg" Width="370" Height="270"
Title="[ProductName] Setup" NoMinimize="yes">

        <!-- Back button takes us to Install Directory dialog -->
        <Control Id="Back" Type="PushButton" X="180" Y="243" Width="56"
Height="17" Text="!(loc.WixUIBack)">
          <Publish Event="NewDialog" Value="InstallDirDlg">1</Publish>
        </Control>

        <!-- Next button takes us to the Verify Ready dialog -->
        <!-- Next button enabled if SQL passwords match and checks
FoxProPath on click -->
        <Control Id="Next" Type="PushButton" X="236" Y="243" Width="56"
Height="17" Default="yes" Text="!(loc.WixUINext)">

          <!-- Check that SQL password is not blank and matches confirm SQL
password -->
          <Condition Action="disable"><![CDATA[(SQLPASSWORD = "" OR
CONFIRMSQLPASSWORD = "")]]></Condition>
          <Condition Action="disable"><![CDATA[(SQLPASSWORD <>
CONFIRMSQLPASSWORD)]]></Condition>
          <Condition Action="enable"><![CDATA[((SQLPASSWORD =
CONFIRMSQLPASSWORD) AND SQLPASSWORD <> "")]]></Condition>

        </Control>

        <!-- Cancel button takes us to the Cancel dialog -->
        <Control Id="Cancel" Type="PushButton" X="304" Y="243" Width="56"
Height="17" Cancel="yes" Text="!(loc.WixUICancel)">
          <Publish Event="SpawnDialog" Value="CancelDlg">1</Publish>
        </Control>

        <Control Id="Title" Type="Text" X="15" Y="6" Width="200"
Height="15" Transparent="yes" NoPrefix="yes" Text="{\WixUI_Font_Title} SQL
Account Information" />
        <Control Id="Description" Type="Text" X="25" Y="23" Width="280"
Height="15" Transparent="yes" NoPrefix="yes" Text="Please enter SQL
credentials and click Next to continue" />
        <Control Id="BannerBitmap" Type="Bitmap" X="0" Y="0" Width="370"
Height="44" TabSkip="no" Text="!(loc.InstallDirDlgBannerBitmap)" />
        <Control Id="BannerLine" Type="Line" X="0" Y="44" Width="370"
Height="0" />
        <Control Id="BottomLine" Type="Line" X="0" Y="234" Width="370"
Height="0" />

        <!-- SQL Information -->
        <Control Id="ServerLabel"             Type="Text" X="20"  Y="63"
Width="80" Height="13" Text="SQL Server:" />
        <Control Id="ServerEdit"              Type="Edit" X="110" Y="60"
Width="175" Height="18" Property="SQLSERVER" />
        <Control Id="UserLabel"               Type="Text" X="20"  Y="83"
Width="80" Height="13" Text="SQL Username:" />
        <Control Id="UserEdit"                Type="Edit" X="110" Y="80"
Width="175" Height="18" Property="SQLUSER" />
        <Control Id="PasswordLabel"           Type="Text" X="20"  Y="103"
Width="80" Height="13" Text="SQL Password:" />
        <Control Id="PasswordEdit"            Type="Edit" X="110" Y="100"
Width="175" Height="18" Text="{50}" Property="SQLPASSWORD" Password="yes"/>
        <Control Id="ConfirmSQLPasswordLabel" Type="Text" X="20"  Y="123"
Width="80" Height="13" Text="Confirm SQL Password:" />
        <Control Id="ConfirmSQLPasswordEdit"  Type="Edit" X="110" Y="120"
Width="175" Height="18" Text="{50}" Property="CONFIRMSQLPASSWORD"
Password="yes"/>

      </Dialog>
    </UI>




This screen uses the 'Password' option for the PasswordEdit and
ConfirmSQLPasswordEdit text boxes so the user sees *** instead of the
actual password.

The username and password are stored in session data which I use in a
CustomAction like so:


    public class CustomActions
    {
        [CustomAction]
        public static ActionResult ExecuteSQLCustomAction(Session session)
        {
            // Get SQL credentials
            DelphiaLibrary.Models.Database _dbInfo = new
DelphiaLibrary.Models.Database();
            _dbInfo.ServerName = session.CustomActionData["Server"];
            _dbInfo.DatabaseName = session.CustomActionData["Database"];
            _dbInfo.Username = session.CustomActionData["Username"];
            _dbInfo.Password = session.CustomActionData["Password"];
            _dbInfo.IntegratedSecurity = false;

            // 01_Create Database DC_UpsizingAbraToSage.sql
            FileInfo _file = new
FileInfo(session.CustomActionData["Directory"] + "01_Create Database
DC_UpsizingAbraToSage.sql");
            String _script = FileContentsToString(_file);
            session.Log(String.Format("*** dbInfo: {0}, {1}",
dbInfo.ServerName, dbInfo.Username));
            session.Log("*** Filename: " + _file.Name + "\n    Contents:\n"
+ _script);
            session.Log("*** Executing " + _file.FullName);

            try
            {
                // Execute the SQL command script
                using (SQLClass _sql = new SQLClass(dbInfo))
                {
                    session.Log("SQL Connection established to " +
_sql.Connection.Database);
                    _sql.ExecuteSqlScript(_script, null, false);
                }
            }
            catch (Exception ex)
            {
                session.Log("****ERROR in custom action SQL_Step1
(DC_UpsizingAbraToSage), executing '{0}': {1}", _file.Name, ex.Message);
                return ActionResult.Failure;
            }

            return ActionResult.Success;
        }
    }

Obviously 'dbInfo' is a model I created to store database info, but you can
see how I am getting the database info via the 'session' object.
ExecuteSqlScript(_script, null, false) is also a custom method that simply
wraps up a typical C# SQL call.

And to execute this custom action you need to set it up as follows (I put
the following in its own wxs file):

<Wix xmlns="http://schemas.microsoft.com/wix/2006/wi"
     xmlns:util="http://schemas.microsoft.com/wix/UtilExtension"
     xmlns:sql="http://schemas.microsoft.com/wix/SqlExtension">

<Fragment>

            <!-- Setup SQL User credentials -->
            <util:User Id="SQLUser" Name="[SQLUSER]"
Password="[SQLPASSWORD]" />

            <!-- Need to perform a CustomAction to execute SQL command
script with parameters -->
            <CustomAction Id="ExecuteSQLParameters"
                                    Return="check"
                                    Property="ExecuteSQL"

Value="Server=[SQLSERVER];Database=master;Username=[SQLUSER];Password=[SQLPASSWORD]"
/>

            <CustomAction Id="ExecuteSQL"
                                    BinaryKey="CustomAction.CA"
                                    DllEntry="ExecuteSQLCustomAction"
                                    Execute="deferred"
                                    Impersonate="no"
                                    Return="check" />

</Fragment>
</Wix>


And in Product.wxs add the following to the Product section which tells WiX
when to do the custom action:

    <InstallExecuteSequence>
      <!-- Only execute on install (not repair or uninstall) -->
      <Custom Action='ExecuteSQLParameters' Before='InstallFinalize'>NOT
Installed AND NOT REMOVE</Custom>
      <Custom Action='ExecuteSQL' After='ExecuteSQLParameters'>NOT
Installed AND NOT REMOVE</Custom>
    </InstallExecuteSequence>


I think you can add another qualifier to this section that would allow you
to only run the CustomAction when the user indicates that it should be run
(via a checkbox or some other identifier).


Brian

If you can't explain it simply, you don't understand it well enough.  -
Albert Einstein

On Mon, Mar 21, 2016 at 9:26 AM, John Cooper <JoCooper at jackhenry.com> wrote:

> It depends on where the information is stored.  For the products I
> support, it is always in an encrypted section of the web.config or
> app.config.  In that case, I use ConfigurationManager to read and parse the
> encrypted section and return the data.  I used to do that early in the MSI
> run, but now I'm switching to doing it directly in the managed bootstrapper.
>
> --
> John Merryweather Cooper
> Senior Software Engineer | Integration Development Group | Enterprise
> Notification Service
> Jack Henry & Associates, Inc.® | Lenexa, KS  66214 | Ext:  431050 |
> JoCooper at jackhenry.com
>
>
>
>
> -----Original Message-----
> From: wix-users [mailto:wix-users-bounces at lists.wixtoolset.org] On Behalf
> Of Rastogi, Utkarsh
> Sent: Monday, March 21, 2016 7:59 AM
> To: wix-users at lists.wixtoolset.org
> Subject: [wix-users] Letting end user give userName and Panssword and test
> SQL server connection
>
> The e-mail below is from an external source.  Please do not open
> attachments or click links from an unknown or suspicious origin.
>
> Hi All,
>
>  I need to setup a SQL connection based on SQL authentication process, So
> I need to get userName and password from end user and provide the end user
> with option to test check the connection.
>
> any suggestions  how/ what should i begin with?
>
>
> Thanks &  Regards,
>  Utkarsh Rastogi
>
> ____________________________________________________________________
> WiX Toolset Users Mailing List provided by FireGiant
> http://www.firegiant.com/
>
> NOTICE: This electronic mail message and any files transmitted with it are
> intended
> exclusively for the individual or entity to which it is addressed. The
> message,
> together with any attachment, may contain confidential and/or privileged
> information.
> Any unauthorized review, use, printing, saving, copying, disclosure or
> distribution
> is strictly prohibited. If you have received this message in error, please
> immediately advise the sender by reply email and delete all copies.
>
>
> ____________________________________________________________________
> WiX Toolset Users Mailing List provided by FireGiant
> http://www.firegiant.com/
>



More information about the wix-users mailing list