How to create a new user and use the new user login in a SQL script?

[JoeBig]

I'm currently using an installshield Basic MSI project to both allow new user creation (using the standard LogonInfoCreateUser dialog(s)) and also running some SQL scripts the call 'CREATE LOGIN' with this user information. The problem is that the SQL scripts fail because the user doesn't exist at the time the SQL scripts get executed. This works fine if the user uses an existing user account.

My question(s): a) when does the new user get created during the installation sequence? b) is it possible to simply move the SQL execution CAs to a point AFTER the new user is created c) how can I get this to work?

I've seen where someone suggested setting up a separate installer as a prereq to create the new user account but that would make the installer user experience less than desirable if they have to create a new user before they even get to the rest of the settings.

[hidenori]

Try scheduling your SQL script that will create a new user to run during login using the Script Execution setting on the Runtime tab in the SQL Scripts view and see if it works. Note that you need to login with an existing user account to run that SQL script creating a new user. You may want a separate connection for other SQL scripts to run using the new user account.

Hope that helps.

[JoeBig]

Thanks for the prompt reply!

I'll try that and let you know my results.

[JoeBig]

I get the same error and I can verify that after this dialog, the user is not yet created.

[hidenori]

Are you logging into the database server with an existing user account such as "sa"?

[JoeBig]

Either way (Window authentication or server authentication), it doesn't work. Same error message.

Don't wanna sound like a broken record but I can confirm that the SQL error is correct no matter where I run this script; the user account does not exist on the machine until AFTER the SQL scripts are run (appears to be at some point after InstallFinalize).

[hidenori]

Can you try the folowing steps and see if a new user account gets created?
[LIST=1]

Create a new Basic MSI project.

Go to the SQL Scripts view.

Add a new connection.

Attach the SQL script creating a new user account. For this test, use a hard-coded user name in the script. Please do not use text replacements.

Select the Run Script During Login check box for the Script Execution setting on the Runtime tab.

Build and run a release

On the SQLLogin dialog, provide a valid login information with an existing user account such as "sa".

Click the Next button and observe.

[JoeBig]

I tried this and it worked but that's not unexpected because I had to hardcode a username that already exists in the SQL script since I can't use the installer to create a new user account.

I question whether what we are trying to fix here are 2 separate things. Just to state it plainly, I want the installer to allow the user to either pick an existing user account on the system or create a new user account. The user account they selected (either existing or new) is then used via a text replacement in the following SQL script:

CREATE LOGIN [%%%IS_NET_API_LOGON_USERNAME%%%] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english];

The '%%%IS_NET_API_LOGON_USERNAME%%%' text is replaced with [IS_NET_API_LOGON_USERNAME].


This works fine when the user picks an existing user account in the installer but does not work when the user chooses to create a new user account in the installer. The error is a SQL error that states the user account doesn't exist (which is correct if I manually go look for the user account when this particular error occurs). I've noticed the new user account gets created after the SQL script is executed which obviously doesn't help me.

[hidenori]

Thank you for the details about your issue. It turned out a bug introduced in InstallShield 2011 that other InstallShiled custom actions do not work correctly if you include InstallShield SQL scripts support. I attached the InstallShield 2011 hotfix for this issue to this post. In order to apply the fix, you need to copy ISSQLSrv.dll to the C:\Program Files\InstallShield\2011\Redist\Language Independent\i386 folder and rebuild your setup. It is an unofficial fix as it is not fully tested by our QA team. So I would strongly recommend that you make a backup copy of the original file before applying it. Also, I filed the work order #IOA-000059643 so that it will be incorporated into the future versions of InstallShield.

Note that the ISNetApiInstall action creates a new user account, and it is sequenced before the ISSQLSeverInstall action which executes SQL scripts scheduled to run during install. So if you select the Run Script During Install option for your SQL script, it should now work as expected.

Best regards.

[JoeBig]

Thank you Mr. Yamanishi! This worked like a charm. I'm amazed to see this get cleared up so fast. Thanks again for getting this taken care of so quickly!