Trouble with built in SQL Scripts and Windows Authentication

[#1]

Hi all,

Please forgive me if this has already been answered - I didn't find it in a search.

I'm using InstallShield 2009 (though the same behavior exists in V12).

I'm using the built in "SQL Scripts" functionality to launch an SQL script in my Basic MSI project. We are using Windows authentication to connect to the (MS SQL Server 2008 Enterprise) database. In this case using the default instance on SQL Server.

When the installer runs on the same machine as the database, everything works fine. When I run the installer on another machine, however, I get the following error:

Error 27502. Could not connect to Microsoft SQL Server 'DB02'. Login failed for user 'ISO\HOST02$'. (18456).

Of interest: 'DB02' is the server to which I am trying to connect. ISO is the domain both servers are on. HOST02 in the machine name of the server upon which the install is running.

I am running as the user "install". I have absolutely no idea why InstallShield is trying to connect as a user named after the host on which I'm running and not the user as which I'm running.

Any help would be greatly appreciated!

Robert

[hidenori]

I am wondering if you are able to connect to the server from the ODBC Data Source Administrator in the Control Panel. Please follow the steps below to see if you are getting the same error:

[LIST=1]

Open the Administrative Tools | Data Sources (ODBC) in the Control Panel

Click the Add button in the User DSN tab.

Select the "SQL Server" from the list and click the Finish button.

Provide a unique DSN name like "TestMyDSN" in the Name field, sepcify "DB02" in the Server field, and then click the Next button.

Select the "With Windows NT authentication using the network login ID." radiobutton, and make sure that "Connect to SQL Server to obtain default settings for the additional configuration options" check box is marked.

Click the Next button, and observe.

If the problem occurs, I think that you need to add a new login with the Windows user account to your SQL Server.

Hope that helps.

[#3]

Hello hidenori.

Thanks for the reply. Yes, creating the DSN worked fine. I can also connect just fine as ISO\install using SQL Server Management Studio. It really looks to be an internal Installshield issue. I don't have any control over what user it's trying to connect as when using Windows Authentication.

I'd hate to give up on this as we are trying to use as much built in functionality as possible (we are a big shop with dozens of large, complex, installers and suffer from the 10 ways to do the same thing syndrome).

Anyone from Acresso reading this forum? Is this a known issue? Am I wasting my time pursuing it? Please let me know and I'll go back to handling SQL scripts on my own.

Thanks,

Robert

[#4]

rjmpsmith wrote:
Hello hidenori.

Anyone from Acresso reading this forum? Is this a known issue? Am I wasting my time pursuing it? Please let me know and I'll go back to handling SQL scripts on my own.

Oops. Sorry Hidenori. I didn't notice that you actually are from Acresso. Didn't mean to slam you or the company. Just frustrated.


Thanks again,

Robert

PS - Forgot to mention that I'm using Windows 2003 Server 64 bit on both machines. If it helps I can test against 2008 as well.

[hidenori]

Don't worry about it. I'm looking into your issue right now. I will post information as soon as I figure it out.

Thank you for your patience and cooperation.

[hidenori]

Since our built-in SQL support is a 32-bit application, I am wondering if you are getting the same error when you run your setup on a 32-bit system. Also, InstallShield uses the SQL Server OLE DB provider by default to connect to Microsoft SQL Servers. Please follow the steps below to change it to use the the SQL Server Native Client OLE DB provider, and see if it makes any difference:

[LIST=1]

Go to the ISSQLDBMetaData table in the Direct Editor.

Change the value of the AdoDriverName column to "SQLNCLI".

Rebuild your setup.
Thank you.

[#7]

After changing the column in the table to 'SQLNCLI', I get the following error:

Error 27501. Could not connect to Microsoft SQL Server 'DB02'. Provider cannot be found. It may not be properly installed.

Ironically, we do install the 64 bit 2008 version of the native client driver just before we install the UI (which is what is broken). Perhaps it needs a reboot before the driver is available? Or perhaps the built in SQL support won't work with the 64 bit native client?

It will take me some time to set up a 32-bit test environment. Our product does have to support 64-bit, though. That's our primary platform.

Thanks,

Robert

[hidenori]

Please try it with SQLNCLI10 and see if it fixes the error. I think that the 2008 version installs the SQL Server Native Client 10.

[#9]

Well, that got me past the provider error and right back to my original error.

I also noticed a column in that table called "WinAuthentUserId", so I tried to use "ISO\install", but it's still trying to connect as "ISO\HOST02$" - and failing. Any idea what HOST02$ even is or why InstallShield wants to connect as that bogus user regardless of underlying connection provider?

[#10]

I tried on a 32-bit system and I am getting the same error.

[#11]

Switching to SQL Server authentication worked - but that's not an option for me.

So the problem seems to be with the Windows Authentication support. It exists on both 32 and 64 bit platforms. I can confirm that the problem exists in both V12 and 2009 versions of InstallShield.

Please let me know if you come up with anything, but I can't spend any more time on it for now - I'm going to go back to running the SQL scripts myself. Bummer.

[hidenori]

It is working on my side as I was able to connect with the Windows authentication from a setup built with a Basic MSI project on a 32-bit Windows XP machine to Microsoft SQL Server 2005 Express on a 64-bit Windows Server 2008 machine.

To verify if it is a project-specific issue, can you follow the steps below and see if you are getting the error when you have a chance?
[LIST=1]

Create a new Basic MSI project.

Add a feature and a component in the Setup Design view.

Add a connection in the SQL Scripts view.

Build the setup and run it on your 32-bit machine.

On the SQLLogin dialog, connect to your 64-bit SQL Server.

Observe.

I am also curious if you are able to connect to your 64-bit SQL Server from the Data Source administrator in the Control Panel on your 32-bit machine.

[#13]

Here is an interesting bit of information. After moving on, I am still having other problems with Windows authentication and Installshield.

I ran the MSI installer produced by Installshield and observed the proceedings using Process Explorer. I can see that the instance of msiexec that I launched is running as "ISO\installer", as expected.

However, it seems to launch other msiexec processes (threads?) to do internal stuff (command lines are unintelligible). These spawned threads are running as "NT Authority\system" or some such. I suspect this is why *nothing* which depends on windows authentication is working in my installer.

Any idea as to why this might be happening? And why this is not the case when running on the machine with the database installed? Hmmm... maybe I have the system account enabled for DB access on that one machine?

The behavior is not specific to a single server. I get the same behavior on every machine *except* the one with the database.

Any help would be appreciated.

Robert

[#14]

I found this through Google:

MSI package generated by VS2008 will run custom actions under SYSTEM account. This "feature" was implemented to work around Vista's UAC.

There is no way to change this behavior from VS2008, but you can adjust MSI package using ORCA tool (available in SDK).

Start ORCA tool, open MSI package, navigate to custom actions. Select action which you would like to run under user account and subtract 0x800 from Type. In my case, value generated by VS2008 is 3090, new value should be 1042.

Save MSI back. Done

Is the same true for InstallShield? If the custom actions are being run under the system account (and all evidence suggests that this is the case) that totally explains what I'm seeing. Obviously that's not going to work with any custom action (including yours) which needs to use windows authentication to a database.

If this is indeed the case, how do I force my custom actions to run as the same user who launched the installer? I don't seriously have to muck about with changing CA types in the direct editor do I???

Thanks,

Robert

[#15]

I think that I have this mystery solved.

Some research and experimentation has revealed that the 0x800 difference in the custom action type (mentioned above) comes down to the msidbCustomActionTypeNoImpersonate flag.

In the case of InstallShield this equates to the difference between selecting a custom action's In-Script Execution option to be "Deffered Execution" and "Deffered Execution in System Context".

When running in system context, you are running your CA as the system user. When not in system context, you are running them as ("impersonating")the user who launched your installer. Apparently running in system context is the suggested way to go for dealing with User Account Control in Vista and 2008.

So the long and short of it is that you simply cannot use windows authentication when in system context. I checked the settings for "ISSQLServerInstall" - the built in InstallShield CA which was originally running my DB scripts - and sure enough, it was set to "Deffered Execution in System Context". No chance it was ever going to work that way.

I'm going to give this another go after setting this to "Deferred Execution" and see what happens. I'll let you know the final result tomorrow.

Now I wonder what's going to happen when I run this against Windows 2008 with UAC enabled? Sigh.... no one ever said this job was going to be easy.

[#16]

rjmpsmith wrote:

I'm going to give this another go after setting this to "Deferred Execution" and see what happens. I'll let you know the final result tomorrow.

Yes. This solved the problem.

[Nick_Umanski]

I was getting this exact same problem when using SQL Server authentication and the fix was as described in an earlier post:

1. Go to the ISSQLDBMetaData table in the Direct Editor.
2. Change the value of the AdoDriverName column to "SQLNCLI".
3. Rebuild your setup.

I am using IS2008 - SQLNCLI worked, SQLNCLI10 did not.

Thankyou for the replies in this thread.