SQL Instance Connectivity on Windows 10

[jdmufc]

Hi there

We have been struggling recently with intermittent faults updating SQL databases using our Install Aware package at client sites, spefically on Windows 10 machines. We use InstallAware Studio Admin X3. I have been doing some research and debugging and here is what I have found.

Using the sample called "SQL Server Connection" provided with X3 that I have built into a compressed single self installing exe...

I have 2 SQL Server 2014 instances on a test server that are called:
DEV-PC
DEV-PC\test
Both have the sa passwords set to the same text string. I can connect to both using the "sa" user using SQL Management Studio.

Using the sample exe mentioned above, on a Windows 7 machine, I can successfully connect to both SQL instances using the "sa" credentials.
If I use a Windows 10 machine I successfully connect to DEV-PC\test but I cannot connect to DEV-PC

It appears that if the SQL instance name does not contain the slash, the sample won't connect to the SQL Server instance on Windows 10.

Here is the code that I have tweaked (starting at approx line 68 in the sample) - obviously this works fine on Windows 7 but not on Windows 10:

Comment: Display connection progress dialog (testing the connection can take a few moments)
Display Dialog: sqlwait, use as progress dialog (non-modal)

Comment: Obtain the SQL Server machine name and instance name
Parse String $SELECTEDSERVER$ into Variables SERVERNAME and INSTANCENAME (Split at first occurrence of pattern)
if Variable INSTANCENAME Equals
Comment: An empty instance name indicates the default instance
Comment: Set Variable INSTANCENAME to SQLEXPRESS
end

Is this a known issue with X3 and Windows 10?

Thanks for your help...

John

[FrancescoT]

Dear John,

it may be an error with the sample eventually ... but of course not an issue with X3.

I'll check this and I'll let you know.

Regards

[jdmufc]

Thank you - looking forward to your thoughts!

Cheers,
John

[jdmufc]

Hi Francesco

I have not heard back from you on this point - have you had time to take a look for us plesae?
If you can confirm this is an issue in Studio Admin X3 (and Windows 10) but it has been fixed in the latest version, then we will look to upgrade.

Thanks, John

[FrancescoT]

Dear John,

Sorry for the delay ....anyway, I am not quite sure how you assigned your instance names exactly.

You said to have two distinct instances defined, where the first one you said is called "DEV-PC\test".
For what concerns this instance, I suppose; "DEV-PC" it's the server name (identified with the MACHINE name and typically with a local connection) and "test" is the EFFECTIVE instance name (eg. <SERVERNAME\INSTANCENAME>)

Then you said to have another instance called as "DEV-PC"  ... but here ...where is the instance name?

Regards

[jdmufc]

Hi Francesco

Thanks for your reply.

That is exactly my point - you don't have to have an instance name in SQL. The default instance can simply called the name of the server (in my case "DEV-PC"). I happen to have also set up a 2nd instance, a named instance, called "DEV-PC\test" for my testing.

The sample fails to connect to the default instance ("DEV-PC") in a Windows 10 environment but works fine when I use a named instance.
The sample works fine for both scenarios in Windows 7.

I can only assume that InstallAware Studio Admin X3 does not support the scenario of a default instance in Windows 10. Do you agree and, if so, does a later version support this scenario?

Looking forward to your response...

Thanks, John

[FrancescoT]

Dear John,

MS SQL Management Studio resolves default instance name internally, while the same is not Always possible when connecting using other third party tools.

That said and if you specified a default name instance while installing your SQL server, such instance name has to be specified to open a connection from InstallAware. Honestly, I found strange that you found that worked for both scenarios in Windows 7. At any rate I suggest you to try the following.

Typically MS SQL Server uses "MSSQLServer" as default instance name and if it's the same with your server ( ... it should be in this way), from InstallAware you should try to connect to that default instance as;

- "DEV-PC\MSSQLServer".

- https://msdn.microsoft.com/en-us/library/ms143531.aspx

let me know if this works.

Regards

[jdmufc]

Hi Francesco - thanks for your reply.

Unfortunately your suggestion didn't work but I do have an update for you which can close this topic...

I discovered that this is not directly Windows 10 related. When connecting to the SQL Server "DEV-PC" with no instance name using a Windows 10 machine ON the domain it works perfectly. It is only when the Windows 10 machine is OFF the domain that the failure happens. In that instance I have also discovered that if I change the SQL Server name from "DEV-PC" to "DEV-PC,1433" it works fine even if the PC is OFF the domain. I did some more research on these forums and found this one from several years ago suggesting you sometimes have to "force" the port number:

viewtopic.php?f=2&t=5175&p=19937&hilit=1433#p19937

I also found this one which is more recent where the user is suggesting a port number is required:

viewtopic.php?f=2&t=9625&p=37044&hilit=port+number+sql#p37044

So to summarise my test results:

Instance 1 - named instance
"DEV-PC\test" - works fine on Windows 10 machines ON and OFF the domain;

Instance 2 - default instance
"DEV-PC" - works fine on Windows 10 machine ON the domain
"DEV-PC,1433" - works fine on Windows 10 machine ON the domain

I will therefore update our standard package to try connecting without the port number by default but if there is a connection failure, try connecting with the port number 1433. I know this isn't 100% fool proof but it may eliminate some connectivity issues across our production estate.

Thanks for your assistance

John