Testing A SQL Database Connection with InstallShield 2015... Issues and Questions...
I hope someone can help me. I have to test a connection to a SQL database to ensure we'll have connection for the web app that is being installed. I thought it would be easy in that InstallShield should have some 'canned' SQL Login dialogs. Out of the box I didn't see any SQL dialogs but found that if I add a connection in the SQL Scripts view, I get the dialogs.
I added the minimum database requirement (SQL 2008 R2) and added the SQL Login dialog to my UI. At first test I could see the Servers on the network, entered user name and password and selected the available databases from the catalog dropdown on the dialog. It appeared OK at first, but a message was soon displayed that the database couldn't be CREATED due to permissions. WHAT? I don't want to create the database, I just want to see that I can log into it. After some digging I found some commands in the ISSQLDBMetaData that appeared to Create, Insert, etc.
I removed those entries and tried again, now it seemed all was OK, however, I can now click the Next button and proceed to the next dialog after only selecting the server. No user name/pswd or database and the verification passes. Even when all info is entered, but I enter garbage for the database name - the verification appears to pass.
I can't figure it out.
I'm wondering if I should just create my own dialog and Custom Action to get the job done here. I'm not even sure the existing SQL dialogs I'm playing with are even suited for what I'm trying to do.
Any guidance here would be greatly appreciated!
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...
public class CustomActions
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"];
// 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;
SqlConnection sqlConn = new SqlConnection();
string SqlConnStr = connString;
if (sqlConn.State == ConnectionState.Closed)
session.Log("SQL Connection Successful!");
session["SQLCONNVALID"] = "1";
catch (Exception Ex)
session.Log("SQL Connection Unuccessful: \r" + Ex.Message);
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!!!