SQL Database not being created...

[Steve]

Hello;
I have been trying for weeks to get InstallAware to create a SQL database using a SQL script without sucess.
The most luck I have had to date is by enabling the 'Create SQL database if absent' checkbox in InstallAware, in which case InstallAware creates an empty shell of a database, but none of the required tables are created.
If I run my SQL script manually against the SQLExpress 2005 server, the database is properly created, so I know that the script is good.
However, if I run the same script through InstallAware (please see screen capture), then nothing gets created.

Perhaps I am overlooking something simple, but I am starting to wonder if there might not be a bug that is preventing the database script from executing properly in InstallAware.

Any help is greatly appreciated.
Steve

File Attached:

create_script.JPG

[Gizm0]

You obviously don't provide any username and password to connect to the SQL Server. Trying putting the "sa" user and the sa password, so IA can actually connect to the database and create it. Also don't forget that IF the .mdf and .lgf of the database exist in the "Data" folder, even if the database is not attached, then it cannot be created. You have to delete them first and try again.

[Steve]

Thanks for the reply Gizm0,
You stated "...You obviously don't provide any username and password to connect to the SQL Server...".
Yes you are correct, and that is because I am using Windows Authentication NOT SQL Authentication. (SQL Authentication is not secure and even Microsoft strongly advises against it's use). With regard to using no user or password, as per the InstallAware documentation:
"If a user name is specified in the User field, SQL authentication will be used. If the User field is empty, Windows authentication will be used."

You also suggested "...that IF the .mdf and .lgf of the database exist in the "Data" folder, even if the database is not attached, then it cannot be created...". However you will notice the in the screen capture that you looked at, I drop the database prior to creating it just in case it exists. So this is not the cause of the problem either.

Just to reiterate the key points:
>I am also installing SQL Express 2005 in during the installation (thus this is not an connectivity problem).
>I am using Windows Authentication.
>I know that my SQL database creation script is good because I can create the database perfectly when I run the script manually in SQL Management Studio.
>Access to SQLExpress is not a problem because I am running locally (localhost)
>Out of desperation, when I enable the 'Create SQL database if absent' checkbox in InstallAware, an empty database gets created (missing all tables because the script does not run successfully).
>firewall is turned off
>simple file sharing is disabled
>installation target is an XP Pro workstation.

This is soooo frustrating. Can anybody tell me what am I doing wrong?

[MichaelNesmith]

I think using Windows Authentication might actually be the problem. Security settings and other variables seem to hurt connections more and more these days. The most successful connections are in fact done with Mixed Mode SQL Authentication, but you are aware of the additional security risk this carries.

I'd recommend to just try Mixed Mode SQL to see if that resolves the problem. Then, we can try to find out what is preventing Windows Authentication (or we'll know for sure that the problem is with Windows Authentication).

[Gizm0]

Thanks for the reply Gizm0,
You stated "...You obviously don't provide any username and password to connect to the SQL Server...".
Yes you are correct, and that is because I am using Windows Authentication NOT SQL Authentication. (SQL Authentication is not secure and even Microsoft strongly advises against it's use). With regard to using no user or password, as per the InstallAware documentation:
"If a user name is specified in the User field, SQL authentication will be used. If the User field is empty, Windows authentication will be used."

You also suggested "...that IF the .mdf and .lgf of the database exist in the "Data" folder, even if the database is not attached, then it cannot be created...". However you will notice the in the screen capture that you looked at, I drop the database prior to creating it just in case it exists. So this is not the cause of the problem either.

Just to reiterate the key points:
>I am also installing SQL Express 2005 in during the installation (thus this is not an connectivity problem).
>I am using Windows Authentication.
>I know that my SQL database creation script is good because I can create the database perfectly when I run the script manually in SQL Management Studio.
>Access to SQLExpress is not a problem because I am running locally (localhost)
>Out of desperation, when I enable the 'Create SQL database if absent' checkbox in InstallAware, an empty database gets created (missing all tables because the script does not run successfully).
>firewall is turned off
>simple file sharing is disabled
>installation target is an XP Pro workstation.

This is soooo frustrating. Can anybody tell me what am I doing wrong?

I didn't examine the whole screen, so if you do drop the database, then it's fine. Windows Authentication IS NOT a good option for FOREIGN connections. You can easily SPOOF it, if you trust only that kind of connection. Mixed mode is the best option. You can try enabling mixed mode at installation and disable it at the end of the script so you can actually connect to the database through IA.

[Steve]

Hi Michael,
Per your suggestion, I have installed SQL Express using both Mixed Mode and also Authenticated mode, both to no avail. The database is simply not being created with the the tables, etc that the script is supposed to create.
So to briefly summarize the status:
>I install SQL Express 2005 locally (localhost).
>I have attempted both Mixed and Windows Authentication modes.
>My SQL database creation script is good (I can create the database perfectly when I run the script manually in SQL Management Studio).
>Out of desperation, in either Mixed or Auth modes, when I enable the 'Create SQL database if absent' checkbox in InstallAware, an empty database gets created (again, missing all tables because the script does not run successfully).
>firewall is turned off
>simple file sharing is disabled
>installation target is an XP Pro workstation with SQL EXpress on it.

In your last post you said "I'd recommend to just try Mixed Mode SQL to see if that resolves the problem. Then, we can try to find out what is preventing Windows Authentication".
I would like to now take to up on your offer to find a resolution for this. Please let me know what you will require from me to proceed.

Thanks in advance,
Steve

[Gizm0]

Can you send me the SQL Script at info (at(@)) datafire (dot(.)) gr ?

I look into it for you and let you know..

EDIT: By looking at your screenshot again, i found the error..
DISABLE the option "Create Database If absent", because you have a CREATE DATABASE in your OWN script. You try to create the database TWICE, an error occurs and aborts the script.

[Steve]

Thanks for your reply Gizm0

You said:"EDIT: By looking at your screenshot again, i found the error..
DISABLE the option "Create Database If absent", because you have a CREATE DATABASE in your OWN script. You try to create the database TWICE, an error occurs and aborts the script."

However, this is not the problem. As previously discussed I only enabled the 'Create Database If absent' checkbox at a later time and only then out of desperation when it was obvious that IA would not create the database from my script. In other words, I have already tried this with and without the 'Create Database If absent' option.

Also, I have already confirmed that my script is not the problem because it runs perfectly from within SQL Query Analyser or SSMS. The problem is either something in my IA Project or IA itself.

Thanks again Gizm0 for your latest post. I can always count on you for a quick response.
Steve

Since I have been struggling with this particular problem for over three weeks now, I was really hoping that someone from InstallAware would assist by doing whatever is needed to determine if this is a IA bug and if not an IA bug, by confirming specifically where my error lies. It is quite disappointing and extremely frustrating to still be struggling with trying to create a functional installation after so many hours.

[Gizm0]

Mail me your script and i'll take a look into it and let you know how it goes.. my email is on the upper post.