SQL Database Connection Test...

[Superfreak3]

Hi all,

I have to add a SQL credentials dialog to the install and test that the entered information is valid and a connection can be made to the database. I've found the SQL Login dialog, but only after adding a connection in the SQL Scripts view. Is this the right way to go about this.

It looks as though General tab of the SQL connection is just an area to set defaults for the Login dialog when displayed. Requirements is used for setting Database product requirements and the Advanced doesn't seem to need any changes.

Is this the right way to get at the Login dialog? Also, I believe the connection is verified by an ISSWLServerValidate action from the dialog's next button. Is that true?

After trying, I entered credentials and the dialog seemed to be passed without issue (good connection?). Then I thought I would alter the db name, which generated an ugly error that the database could not be CREATED due to permissions. That scares me!!

Should I be creating my own dialog just ot check the connection? I'm guessing I could use ISSWLServerValidate for the check. Does anyone know what kind of error/message is returned if entered info is invalid? Just wondering if I might have to create a friendly Spawned dialog of warning.

Any pointers are appreciated!!

[Superfreak3]

It appears that I can change the connection check behavior by modifying the ISSQLDBMetaData Table. I think I cleared the CreateDBCmd, CreateTableCmd, etc fields and the fearsome messages went away when I change the login information to invalid data. I'm not sure if this has any pitfalls, but it appears to work. I just want to test access to the database, not modify it in any way.

Let me know if anyone sees issues.

Thanks!

[ch_eng]

Hello,
I use the following code in an InstallScript Only project to test connecting to an existing SQL database:

// user needs to check the login credentials to the server
SdShowMsg( szTestingConnection, TRUE );
nCheckLogin = SQLRTConnectDB( szConnection, pObjDBConnect->svDatabase, pObjDBConnect->svServer, FALSE, pObjDBConnect->svUserID, pObjDBConnect->svUserPWD );
// SQLRTInitialize2() should have already been called but it seems to need a call here as well.
if nCheckLogin = SQL_ERROR_NOT_INITIALIZED then
	SQLRTInitialize2();
	nCheckLogin = SQLRTConnectDB( szConnection, pObjDBConnect->svDatabase, pObjDBConnect->svServer, FALSE, pObjDBConnect->svUserID, pObjDBConnect->svUserPWD );
endif;

HTH

[Superfreak3]

I'm trying to move away from the canned SQL connection verification action via the SQL Login dialog, opting for a C# WiX Custom Action. The only problem is I can't get that to work properly either. If I put similar code in a Win Form app, it connects and detects invalid information as well without problem, but in the CA, no go.

Here's my code if anyone can lend a hand...

[CODE]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");

// Change the Windows Installer Property names to match the SQL Login dialog.
string srvName = session["IS_SQLSERVER_SERVER"];
string dbName = session["IS_SQLSERVER_PASSWORD"];
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("\\", "\\\\");

if (authMethod == "1") // 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;
}

}
}[/CODE]

My session.log statements aren't writing anything to the log either so I can't log to troubleshoot.

I also put a replace in there to handle a single backslash in the server name, but that had no effect.

Thanks in advance for any help!!

[ch_eng]

Hello,
While I can't help with the C# part, I can add additional InstallScript that may be of use. I haven't had to do this from scratch in so long (copy-pasta nowadays) that I didn't add the other code that may help in your case. The custom "ADODB_RunCode_GetValue" function below just runs a simple query looking for a specific field in our database to verify we are able to connect. It returns the ADODB error code in svError if there was a problem.

//***NOTE*** in InstallScript Only, "SQLRTConnectDB" does not actually verify that the user/password is correct
//*** basically, "SQLRTConnectDB" only verifies that the installshield SQL dll interface has been initialized
if nCheckLogin = ISERR_SUCCESS then
	// when nCheckLogin = ISERR_SUCCESS, only know that the installshield SQL dll was initialized - nothing more
	// now manually have to see if the DB engine exists and if the user/pass combo is valid
	// common ADODB errors that could happen:
	// -2147467259:  SQL Server does not exist or access denied.
	// -2147217843:  Login failed for user ''
	
	// look for the current DB version:
	pObjDBConnect->svADODBConnection = "Provider=sqloledb;server=" + pObjDBConnect->svServer + ";database=" + pObjDBConnect->svDatabase + ";uid=" + pObjDBConnect->svUserID + ";pwd=" + pObjDBConnect->svUserPWD + ";";
	bSQLCodeRanOK = ADODB_RunCode_GetValue( pObjDBConnect->svADODBConnection, szDBVersionCheck, svDBVersion, svError );
	SdShowMsg( szTestingConnection, FALSE );
	Disable( HOURGLASS );

	if !bSQLCodeRanOK then
		if StrFind( svError, "-2147467259" ) >= 0 then
			strSQLResults = "Error -2147467259: SQL Server does not exist or access denied.";
		elseif StrFind( svError, "-2147217843" ) >=0 then
			// login failed
			strSQLResults = "Error -2147217843: Login failed for user '" + pObjDBConnect->svUserID + "'.";
		else
			strSQLResults = "An error occurred:\n\n" + svError;
		endif;
	else
		strSQLResults = "SQL Login Successful! Database version found: " + svDBVersion;
	endif;
endif;

HTH

[Superfreak3]

And you just used your CA in place of the SQL Login verification action from the SQL Login dialog or did you create your own dialog as well?

I like the SQL Login dialog since the server and database browse capabilities are built in so I don't have to reinvent the wheel. I guess you could say I'm sort of reinventing things with the verification, but I just can't figure out how to get the out-of-the-box verification to work.

[ch_eng]

We started from a customized version of the SQLServerSelectLogin(Ex)(2) dialog but added a lot more functionality (adding a radio button for Install vs Patch a database, created separate logins for a DBA (to create our database) vs a local sql login that our apps would use, and allowed the user to specify the (usually) remote path to place the actual .mdf/.ldf files). The source for the original dialog can be found on your computer in a directory similar to:

C:\Program Files (x86)\InstallShield\2014\Script\Isrt\src\SQLServerSelectLoginDlg.rul

For patch installs, we have a custom dialog that uses some of the same functionality (reads the SQL connection string, parses the parts into the form and requires the user to verify the connection or change it before the patch will continue).

IMHO database connection dialogs still have a long way to go in InstallShield. We have been customizing them for at least 7+ years to meet the requirements of our customers.

HTH

[Superfreak3]

Yes, I agree that there is more work to do on InstallShield's part with regard to dialogs such as these.

I thought I found the problem as this was initially wrong...

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 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!!!

Thanks for the replies and the guidance!!

[ch_eng]

Glad you got it working!