Installing SQL Express

[rdeberry]

I am trying to create an install for my service that includes SQL Express 2005. I have the pre-requisite part working and it installs fine. My command line is "SECURITYMODE=SQL DISABLENETWORKPROTOCOLS=0 ADDLOCAL=SQL_Engine". I am trying to either a) attach a database or b) create the database and stored procedures. I have had no luck doing either. When the install gets to that point, it hangs. I cannot cancel (although it is responsive and asks me if I want to do so) and I end the process with task manager. I have the abort if script fails checked. I tried a simple create user query like this
"CREATE LOGIN [DHSUser] WITH PASSWORD=N'password', DEFAULT_DATABASE=[DHS_Main], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
EXEC sys.sp_addsrvrolemember @loginame = N'DHSUser', @rolename = N'sysadmin'
GO"

The server name is (local)\\SQLEXPRESS and the instance is MSSQL$SQLEXPRESS.

I have tried all kinds of combinations of instance name, authentication, sa with a password, etc. I cannot get any script to execute against the Sql engine. I installed the Studio Express manager so I could see what was configured and it is correct (network protocols are enabled). I have restarted the service manually a few times, rebooted the PC just in case, nada. This is the last hurdle for me and it is just frustrating the hell out of me.
Any ideas or things I can check?

[CandiceJones]

Hi,

Sorry to hear of your troubles! Could you post your script here (the setup script, its the .MIA file) and we'll see if we can find anything wrong with it!

Most likely the cause is misspecified host computer name, and/or the network resolution may be getting stuck due to firewalls on your system. I believe thats the only case in which the plugin may appear to hang.

[rdeberry]

I checked the error logs in the SQL logs directory. One of the files was 2+GB!!!. Must have been when I let it "finish" while I ate dinner. The smaller log files had error 17149 : the server could not load the ssl library. Not sure if this had something to do with the install hang.

Attached is my latest script.

[rdeberry]

Oops. It would help if I actually attached it

File Attached:

Application Server with SQL 2005 Express.mia

[CandiceJones]

Hmm, it seems to me this might be caused by a SQL Server misconfiguration. Can you try to install this missing library when you are installing SQL Server?

[rdeberry]

I don't know what library is missing. I am using the sqlexpress application requirement in the install script. It does install sql express with network protocols enabled. However, dynamic IP port is turned on, which means it picks a port at random. My service will try to connect to 127.0.0.1:1433. Other than that, it appears as if SQL express is installed correctly. I even added the sql express studio to the install and with that I can verify login/database creation. I connect to it using (local)\\SQLEXPRESS just like the install script. I have attached the databases manually, and added the one login I need manually, and all is well. I am trying to avoid having the end user attach the db and add a login. Simple to me, but confusing to an end user. I have the install with sql express as an option in my cd launch menu. The reason was that sqlexpress is listed as a pre-requisite and it would get installed everytime. My service just needs a sql 2000/2005 engine with TCP port 1433 open and of course my databases. I want the install engine to 1) detect if SQL 2005/express is already installed and if not install SQL express 2) add a login as sysadmin 3) attach my default database OR run sql scripts that create the database/tables/stored procedures. Number 1 works great. I just can't get a login created. I have tried including the MDAC 2.8 refresh as well. BTW all target machines are Windows XP, SP2.

[CandiceJones]

I am not a SQL expert, but since the error you quoted states "the server could not load the ssl library", I would suggest you click the Options button in the Application Runtimes view for SQL Express, and set the plug-in to install everything that is available, and see if that helps (remember to click the Generate Command Line button and then OK to save your changes, and rebuild your setup).

[CandiceJones]

Also...in the first script execute command, you are specifying the instance name MSSQL$SQLEXPRESS, I believe the correct form might be SQLEXPRESS here instead - MSSQL$SQLEXPRESS is the name of the service and I believe the name of the instance is different.

In the second script execute command, you are not specifying an instance at all, which might be causing the hang.

[Gizm0]

I don't know what library is missing. I am using the sqlexpress application requirement in the install script. It does install sql express with network protocols enabled.... Number 1 works great. I just can't get a login created. I have tried including the MDAC 2.8 refresh as well. BTW all target machines are Windows XP, SP2.

Well, the main reason you can't do this, is because SQL Express DOES NOT allow you to login with "sa" account using sql authentication and be able to create a login.
You have to change authentication mode to "Windows Authentication AND SQL"(The so called "mixed mode") so the "sa" user would be able to login and create the user you want.
Also don't forget to apply roles on the new user that you're creating.
A personal advice is that is better to install an instance of your database even if SQL Express is installed.It is much better and easier to manage your sql server later.Just provide an INSTANCENAME during the installation .You can simply uninstall it when you uninstall your product, without leaving any garbage databases/users etc on the "main" version of the SQL Express that was installed previously on the target machine.

[rdeberry]

Thanks Gizmo, I changed the instance I have tried about every combination of options to install SQL Express. One thing that I noticed is the SQL express options dialog (the second one) does not represent the command line. This confused me for a bit and I know I must have done the same install a few times even though I thought it was different. The command line textbox contains what will execute. Ignore the boxes when you go back and look at it.
Here is my command line now:
SECURITYMODE=SQL DISABLENETWORKPROTOCOLS=0 INSTANCENAME="DHS" ADDLOCAL=SQL_Engine

If ADDLOCAL=ALL, the script will fail everytime. There is something wrong in one of the cab modules.

I have verified that you can create a sa password with IA that will allow you to login using Studio Express Manager. I have executed my create login script from there as sa and as Windows user. Both accounts executed.

Here is what I am trying to accomplish with IA

1) Detect if SQL Express is already there
2) If not, install SQL Express
3) Configure the TCP Protocol enabled=True (DISABLENETWORKPROTOCOLS=0 does that AND other protocols)
4) Turn off Dynamic ports (default is 0 which is ON)
5) Configure the TCP port to 1433 (I know this is supposed to be default but it isn't. Mine keeps coming up as port 1029.)
6) Create or Attach default databases along with the tables/views/sproc
7) Create login for my service

Has anyone got something similar to work? I can manually do this in about 3 minutes.

I have looked everywhere for the command line options for the setup to SQL Express. The SQL Books Online are not much of a help. Anybody know of ALL the options to pass to the setup file?

Would it be easier to launch the Microsoft setup file and just use IA for all the pre-requisite stuff?
Or should I just write a .NET Wizard that will handle this?
Am I asking to much from the installer?

[CandiceJones]

I don't know if this is asking too much from other installers, but I think you should definitely be able to get this done in your InstallAware installer! I am not sure why you are getting an error when you are trying to install all features. That installation option has been tested to work on-site at InstallAware. The error you are getting might be related to missing software dependencies on your test system.

I also recommend using clean machines for testing to aid with reproducability of issues. If you can provide more specific information on the error messages, the exact setups you tried, I will do my best to assist further.

[rdeberry]

I hope I can get it done with InstallAware!! You are my last hope.

Here is how I am testing:

1) I started with a clean install of XP, no service packs. I did not let the machine connect to Internet or even Activate windows (have 25 days left)

2) I then booted with a Arconis Boot CD with True Image 9.0 loaded

3) I used their software to make two partitions.

4) In the "secured" zone, I made an image of the drive

5) I rebooted and installed Service Pack 2 from CD.

6) I made another image of that and put in on the second partition as well

7) I then run the install test. Install detects that I need the .Net 2.0 framework, IE 6, Windows Installer 3.1, and SQL Express just like my pre-requsites.

After 1 reboot, the install continues until I get through all pre-requisites. Total time is about 15 min. From there, the install finally gets to where it installs my software, installs and registers my service (perfect by the way) and then executes the SQL plugin.

When I want to test again, I boot with the CD and restore the XP, SP2 image. Total time there is about 20 min.

I have done this about 50 times so far.

I am currently making a new image. This one will start with basic XP and use Windows update to install all pre-requistes except SQL Express.

Since you have a script working that installs SQL Express, can you post it here so I can compare?

Thanks for your attention

[CandiceJones]

First off, I really recommend you use VMware (or Virtual PC) - it will save you the manual labor of preparing partitions, using manual imaging software, and so on. You will have a virtual computer running on your same desktop. You may even set "snapshots" on that virtual computer, so for example one snapshot would be plain XP, the other with SP2, the other with prereqs, the other with SQL, etc...

To create a working SQL install, just check the SQL runtime, and then click the Customize button, specify an instance name and a strong SA password; uncheck all features, click Generate Command Line; and that will take care of it.

[rdeberry]

Ok I am back on track. Not only do you need to provide a sa password but it must be a strong password. Here is my command line that works:

SECURITYMODE=SQL DISABLENETWORKPROTOCOLS=0 SAPWD="dhs$e700" ADDLOCAL=ALL

Note the password has letters, numbers, and a $.

Notice I left out the Instance name. Providing an Instance name does indeed install SQL Express, BUT the checlsql2005 script does not detect it and it tries to install it again. Does anyone know how to check for the Instance using IA?

Here is the error message from SQL if sa password is missing or weak:

Last Action : SAPasswordPolicyCheck
Error String : The sa password must meet SQL Server password policy requirements. For strong password guidelines, see Authentication Mode, in SQL Server Books Online.
Error Number : 28001

I still cannot get the sql script to execute.

Here is my script:

~InstallAware Clipboard Data~
~MS SQL Server~
~{746DAECD-14AE-4061-9A0E-C07CE8BF1DD9}~
~(local)\\SQLEXPRESS,,,sa,dhs$e700,,"$NEWLINE$CREATE LOGIN [DHSUser] WITH PASSWORD=N'eclipse', DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF$NEWLINE$GO$NEWLINE$EXEC sys.sp_addsrvrolemember @loginame = N'DHSUser', @rolename = N'sysadmin'$NEWLINE$GO",0,-1~
~mMSSQLIDE.dll\\mMSSQLExec.dll~

Cleaned up so you can read it better:
CREATE LOGIN [DHSUser] WITH PASSWORD=N'eclipse', DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
EXEC sys.sp_addsrvrolemember @loginame = N'DHSUser', @rolename = N'sysadmin'
GO

And here is the error message in the sql logs directory:

2006-01-19 16:49:20.10 Logon Error: 17194, Severity: 16, State: 1.
2006-01-19 16:49:20.10 Logon The server was unable to load the SSL provider library needed to log in; the connection has been closed. SSL is used to encrypt either the login sequence or all communications, depending on how the administrator has configured the server. See Books Online for information on this error message: 0x6d. [CLIENT: <local machine>]

If I login to SQL Express using the Management Sutdio as sa with the password above and execute the script, it does add the login. So the sa login is valid, the sql script is valid but it will not execute from the installer.

Is there a way to trap the error message from the sql script (I expect to see the same error as in the log)?

[CandiceJones]

In my opinion, you should first fix the instance issue. As Gizmo suggested you should create your own instance. As I have explained before, it is easy to check for a particular instance - just click Options in the Application Runtimes visual view after highlighting SQL Express and the first dialog which pops up is the service name for your SQL Express instance that you want InstallAware to check for. Be sure to specify the actual service name which is different from the instance name.