Software Deployment

Yes, this will always be a manual install.

I was just planning on using the InstallShield SQL Login dialog and it properties.  It nicely browses for Servers and DBs on selected Server, but I just don't know how the verification works.

So, what I was planning to do is to pass the InstallShield SQL Login diaolg proerties to a Wix C# Custom Action that will attempt login then set a PUBLIC property accordingly.  The issue is that I'm always getting 0 returned (failure).  The same basic code I'm using in the Custom Action works fine from a Win Form app.  So I'm not sure what the issue is at this point.

I first thought that maybe it was not handling a "\" character in the Server name properly so I replaced a single with a double backslash before proceeding in the code, but that didn't help.

My session.log statements are not writing anything to the log either at this point so its tough to troubleshoot.  I'm using a try catch and even tried to return the exception message to the public property, but that didn't work either.

Here's the code of my current Custom Action.  I hope you or anyone else can help me out with it...

using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using Microsoft.Deployment.WindowsInstaller;

namespace SQLConnectionTest
{
    public class CustomActions
    {
        [CustomAction]
        public static ActionResult CheckSQLConnection(Session session)
        {
            session.Log("Begin SQL Connection Test");

            string srvName = session["IS_SQLSERVER_SERVER"];
            string dbName = session["IS_SQLSERVER_DATABASE"];
            string userName = session["IS_SQLSERVER_USERNAME"];
            string passWord = session["IS_SQLSERVER_PASSWORD"];
            string authMethod = session["IS_SQLSERVER_AUTHENTICATION"];
            string connString;

            // Handle special character(s) in Server Name
            // Single slash to double...
            srvName = srvName.Replace("\\", "\\\\");  //Didn't have any effect for me!
            
            if (authMethod == "0") // Windows Authentication
            {
                session.Log("SQL Connection using Windows Authentication.");
                connString = "Server=" + srvName +";Database=" + dbName + ";Integrated Security=True";
            }
            else // Server Authentication
            {
                session.Log("SQL Connection using Server Authentication");
                connString = "Data Source=" + srvName + ";Database=" + dbName + ";Persist Security Info=True;User ID=" + userName + ";Password=" + passWord;
            }

           
            try
            {
                SqlConnection sqlConn = new SqlConnection();
                string SqlConnStr = connString;

                if (sqlConn.State == ConnectionState.Closed)
                {
                    sqlConn.Open();
                    session.Log("SQL Connection Successful!");
                    session["SQLCONNVALID"] = "1";
                }
               
            }
            catch (Exception Ex)
            {           
               
                session.Log("SQL Connection Unuccessful: \r" + Ex.Message);
                session["SQLCONNVALID"] ="0";
            }

            return ActionResult.Success;
        }

    }
}

I'm no C# expert so hopefully there is an obvious problem that I'm missing.

Thanks for the reply EdT and any other help from anyone that may be headed my way!

I thought I found the problem as this was initially wrong, but corrected above...

string dbName = session["IS_SQLSERVER_DATABASE"]; (was previously PASSWORD).  I thought for sure that was it, but nope.

I also had the Windows Authentication backwards based on the SQL Login dialog.  It was set to 1, but corrected above to 0.  I thought for sure too that would do it as the connections strings were backwards, but nope.

After setting a property and displaying in another Custom Action I found the .open is failing with "The ConnectionString property has not been initialized."

DUH!!!!!!!  This would have helped...

sqlConn.ConnectionString = connString;

And was able to get rid of this...

string SqlConnStr = connString;

Whew!!!  That was killing me!!!