Cannot connect to local SQL 2008 SP1 during install

[wichan]

Hi,

We're running InstallShield 2010 premier sp1. We deploy SQL server 2008 SP1 during the install and use the embedded SQL Scripts to attach DB to the named instance. On most of the machines it works fine, but I have 2 that's just refusing to connect to the newly installed instance. I've attached screenshot of what the user sees, and the following is what's showing in the log:

1: Attempting to establish the SQL connection... Server: localhost\eyeq, Database: master
1: ADO Error: Number: -2147467259 Description: [DBNETLIB][ConnectionOpen (Connect()).]Specified SQL server not found. Source: Microsoft OLE DB Provider for SQL Server SQL State: 08001 Native Error: 6
Error 27502.Could not connect to Microsoft SQL Server 'localhost\eyeq'. [DBNETLIB][ConnectionOpen (Connect()).]Specified SQL server not found. (6)


At that point, installer stops and wait for the user before rolling back, so I have a chance of seeing what's wrong with the SQL engine. What I've found is that the instance is working fine, just like all the other machine that InstallShield can connect and attach DB no problem.

I've been searching high and low with no result. Some say it's the name "localhost", some say it's the protocols enabled on the SQL engine (which we've enable shared memory, named pipe, and TCP), some say it's the SQL Browser service. I've checked all of them and they're fine.

I'm running out of ideas, any help would be greatly appreciated!

[hidenori]

Is the instance of Microsoft SQL server 2008 SP1 installed via a setup prerequisite? If so, is the problem reproducible when you close the error message and run the setup again without uninstalling the SQL server 2008 SP1? Also, have you tried connecting to .\eyeq or MACHINENAME\eyeq (where MACHINENAME is your local machine name)?

[wichan]

Hi,

Thanks for the quick response!

SQL server was NOT installed via prerequiste, it's done inside an InstallShield script within the UserInterview section. Reason we're doing that is we have a requirement saying SQL should be removed when we uninstall. These 2 parts works fine for most of the machines, except for 2.

The problem is reproducible on the 2 machines. We have a rollback action on that SQL install, and removes the SQL server if installation fails. After checking the SQL 2008 setup log under their program files, I can see that both install and uninstall of it was fine, result 0.

While the error was showing up on the user's screen about the error (before the rollback), I was able to hit the server with SQLCMD. Here're the commands that I can get a valid prompt:

sqlcmd -S localhost\eyeq
sqlcmd -S .\eyeq
sqlcmd -S \eyeq

I'll have to make some changes to the scripts if I were to leave the SQL engine behind. Let me know if that's needed.

Thanks!

William

[hidenori]

I am wondering if the SQL engine is up and running when InstallShield attempts to connect to the server. Please check manually if the SQL engine service is still running when the error 27502 is poped up.

[wichan]

That's exactly what I did. Those commands were ran on that target machine while I can see the error message. That's the only time I can do it as after clicking on the OK, the rollback action will take place and SQL will be removed.

[hidenori]

Can you try changing InstallShield to use the SQL Native Client driver by following the steps below, and see if it makes any difference?

[LIST=1]

Go to the ISSQLDBMetaData table in the Direct Editor.

Find the "MSSQLServer" row.

Change the AdoDriverName column from "sqlolddb" to "sqlncli".

Change the DsnODBCName column from "{SQL Server}" to "{SQL Native Client}"

Rebuild your setup.
Note that the SQL Native Client driver must exist on the machine that your setup is running. If you want to use the SQL Server Native Client 10.0 driver, you need to specify "sqlncli10" and "{SQL Server Native Client 10.0}".

[wichan]

Thanks! I'll try that and let you know.

[wichan]

Ok, I've build a version of the installer that had the changes, the error message changed a bit but still failing. This is what we're seeing in the log:

1: Attempting to establish the SQL connection... Server: localhost\eyeq, Database: master
1: ADO Error: Number: -2147467259 Description: SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. Source: Microsoft SQL Server Native Client 10.0 SQL State: 08001 Native Error: -1
1: ADO Error: Number: -2147467259 Description: A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. Source: Microsoft SQL Server Native Client 10.0 SQL State: 08001 Native Error: -1
1: ADO Error: Number: -2147467259 Description: Login timeout expired Source: Microsoft SQL Server Native Client 10.0 SQL State: HYT00 Native Error: 0
Error 27502.Could not connect to Microsoft SQL Server 'localhost\eyeq'. Login timeout expired (0)

Again, all sqlcmd commands listed before were working at the point we're seeing that error popup.

[hidenori]

I am wondering if you can connect to the server through ODBC drivers. Please try the steps below when the error 27502 is poped up:
[LIST=1]

Launch the Administrative Tools | Data Sources from the Control Panel.

Click Add. The Create New Data Source panel should be displayed.

Select “SQL Server” and click Finish. The Create a New Data Source to SQL Server wizard should be displayed.

Type “Test” in the Name field and “localhost\eyeq” in the Server field.

Click Next. The Authentication panel of the Create a New Data Source to SQL Server wizard should be displayed.

Select your authentication type and provide a valid Login ID and password if you choose SQL authentication.

Make sure the “Connect to SQL Server to obtain default settings for the additional configuration options.” box is checked.

Click Next, and see if you can proceed to the next dialog, or receive an error.

[wichan]

Nice! I think we're getting somewhere, at least with a different message! However I'm getting different messages for the same settings.... Note that all testing done were using "sa" account with SQL server authentication

At first, I've started with default settings just like you suggested. This is what I get:
[CODE]
Connection Failed:
SQLState: '01000'
SQL Server Error: 53
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
[/CODE]

Then I figure I should see what's the settings screen, I see that TCP was selected. So I select Named Pipes and leave the rest as default. This is what I get:
[CODE]
Connection Failed:
SQLState: '01000'
SQL Server Error: 53
[Microsoft][ODBC SQL Server Driver][Named Pipes]ConnectionOpen (Connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][Named Pipes]SQL Server does not exist or access denied.
[/CODE]

Now I changed the pipe name to one that I know works (I tried this from the prompt before) "\\.\pipe\mssql$eyeq\sql\query". This WORKS!!!

Next I tried Multiprotocol, I assume this is the default when I don't specify protocol at sqlcmd. This also works.

For some reason, I went back to the TCP settings, figure I should get a screenshot of the setting screen. When I try to connect, this is what I get (notice this is different then the very first screen):
[CODE]
Connection Failed:
SQLState: '01000'
SQL Server Error: 11001
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (Connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 6
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied.
[/CODE]

What ever I've tried, I couldn't get back the same error message that I had before.

Then I tried using ".\eyeq" as TCP server name, which I was able to connect. Using "\eyeq" as server name also works on TCP protocol.

[hidenori]

Are you able to connect to ".\eyeq", "(local)\eyeq", or "\eyeq" from InstallShield? If you are using the Windows Authentication, do you get a different result?

[wichan]

Wow! Interesting, it working with ".\eye"! Of course, now our app runs into the same problem as InstallShield...:confused:

[hidenori]

I am curious if you can connect to that instance in question remotely from a network computer.

[wichan]

Unlikely... While checking firewall settings from before, I've found that box have Windows Firewall up with no exception. At the same time, other boxes seems to be configured with another firewall that allows for 1434 connection, but no one listening on the other side. I was using the PortQry in finding this.