Home > Articles > Data > SQL Server

  • Print
  • + Share This
From the author of

Generating the .iss File

Okay, what's next? Well, because we are accepting all these parameters from the user(s) of our script, we need to do something with them. In this section of the script, the .iss file (that we recorded earlier) will be written out to disk.

As you know, the .iss file contains a lot of information, and it can be pretty complex to get it right (as we saw in the first article). So the way that I find is the best (although maybe not the easiest) for getting the .iss file right is to use the good ol' copy-and-paste method.

Open the .iss file (which you recorded in the first article) in any text editor (Notepad is as good as any), and copy the entire contents into a new text document. At the beginning of every line, paste the text (echo. Then at the end of every line (well, almost every line), paste the text ) >> %ISSFile% (except on the very first line, where you will use only a single greater-than sign: >). I have provided a snippet of what the entry in your new document should look like in Listing 3.

Listing 3—Sample of the Combination of the .iss File within the Batch File.

    (echo [InstallShield Silent])              > %ISSFile%
    (echo Version=v5.00.000)                >> %ISSFile%
    (echo File=Response File)                >> %ISSFile%
    (echo.)                         >> %ISSFile%
    (echo [File Transfer])                 >> %ISSFile%
    (echo OverwriteReadOnly=NoToAll)            >> %ISSFile%
    (echo.)                         >> %ISSFile%
    (echo [DlgOrder])                    >> %ISSFile%
    (echo Dlg0=SdWelcome-0)                 >> %ISSFile%
    (echo Count=15)                     >> %ISSFile%
    (echo Dlg1=DlgMachine-0)                >> %ISSFile%
    (echo Dlg2=DlgInstallMode-0)              >> %ISSFile%
    (echo Dlg3=DlgAdvanced-0)                >> %ISSFile%
    (echo Dlg4=SdRegisterUser-0)              >> %ISSFile%

If you look at the first line, you will see it has only a single greater-than (>) sign, which creates the SQL2K.iss file. The double greater-than signs (>>) append the other lines to the .iss file.

NOTE

You may have noticed the following line:

 (echo.)      >> %ISSFile%

NOTE

This will insert a blank line into the generated .iss file; that is, it creates a space between sections within the file. For example, the generated file will look like the following:

[InstallShield Silent]
Version=v5.00.000
File=Response File

[File Transfer]

NOTE

By inserting blank lines into the .iss file, we format the document to make it easier to read and separate the sections from each other.

The next step in our process is to now put the variables in place of the values that have been specified in the .iss file. Take a look at the variables we have (refer to Listing 1), and replace the hard-coded values in the file, with the variable. For example, in the file you are editing, you will see a line similar to the following:

 (echo szDir=%PROGRAMFILES%\Microsoft SQL Server\MSSQL)   >> %ISSFile%

You should replace the value %PROGRAMFILES%\Microsoft SQL Server\MSSQL with %SQLPath%. The line will now look like this:

 (echo szDir=%SQLpath%)                   >> %ISSFile%

NOTE

You may have a value in your .iss file which is C:\Program Files\Microsoft SQL Server\MSSQL rather than %PROGRAMFILES%\Microsoft SQL Server\MSSQL. If this is the case, replace the file path value with the variable. Sometimes, SQL Server .iss files generate actual paths rather than using the system-wide variables such as %PROGRAMFILES%. This is generally the case if you install to different locations rather than the Program Files directory.

Table 1 shows the lines you should replace and the values they should be replaced with.

Table 1[em]Values to Replace in the .iss File with Dynamic Variables

Old Value

New Value

szName=Your Company Name

szName=%CompanyName%

InstanceName=MSSQLSERVER

InstanceName=%InstanceName%

szDir=%PROGRAMFILES%\Microsoft SQL Server\MSSQL

szDir=%SQLpath%

szDataDir=%PROGRAMFILES%\Microsoft SQL Server\MSSQL

szDataDir=%SQLDataPath%

collation_name= SQL_Latin1_General_CP1_CI_AS

collation_name=%SQLCollation%

TCPPort=1433

TCPPort=%TCPPort%


NOTE

In Table 1, I purposely didn't give the string values for the SvcActName, SvcActDom, and SvcActPwd sections of the .iss file. This is because (as you will see shortly), we need to be a little tricky about how we write out the .iss file for this particular section.

Once you have completed editing the file, you can then take all the information and paste it into your batch file, right under the parameter-checking section (that is, after all the IF statements). The code you have pasted in will look similar to that shown in Listing 4.

Listing 4—Echoing Out the .iss to SQL2K.iss with the Parameters We are Passing in.

    if "%InstanceName%"=="MSSQLSERVER" goto :DefaultInstance
        (echo Setting InstanceName to "MSSQLSERVER$%InstanceName%")
        (echo Setting InstanceName to "MSSQLSERVER$%InstanceName%") >> %Logfile%
        set InstanceName=MSSQLSERVER$%InstanceName%
    :DefaultInstance

    set ISSFile=%ISSDir%\SQL2K.iss
    REM Build the auto-answer file for the setup program.
    (echo Building auto-answer file.)
    (echo Building auto-answer file.)   >> %LogFile%

    (echo [InstallShield Silent])       > %ISSFile%
    (echo Version=v5.00.000)         >> %ISSFile%
    (echo File=Response File)         >> %ISSFile%
    (echo.)                  >> %ISSFile%
    (echo [File Transfer])          >> %ISSFile%
    (echo OverwriteReadOnly=NoToAll)     >> %ISSFile%
    (echo.)                  >> %ISSFile%
    (echo [DlgOrder])             >> %ISSFile%
    (echo Dlg0=SdWelcome-0)          >> %ISSFile%
    (echo Count=15)              >> %ISSFile%
    (echo Dlg1=DlgMachine-0)         >> %ISSFile%
    (echo Dlg2=DlgInstallMode-0)       >> %ISSFile%
    (echo Dlg3=DlgAdvanced-0)         >> %ISSFile%
    (echo Dlg4=SdRegisterUser-0)       >> %ISSFile%
    (echo Dlg5=SdLicense-0)          >> %ISSFile%
    (echo Dlg6=DlgClientServer-0)       >> %ISSFile%
    (echo Dlg7=DlgInstanceName-0)       >> %ISSFile%
    (echo Dlg8=SetupTypeSQL-0)        >> %ISSFile%
    (echo Dlg9=DlgServices-0)         >> %ISSFile%
    (echo Dlg10=DlgSQLSecurity-0)       >> %ISSFile%
    (echo Dlg11=DlgCollation-0)        >> %ISSFile%
    (echo Dlg12=DlgServerNetwork-0)      >> %ISSFile%
    (echo Dlg13=SdStartCopy-0)        >> %ISSFile%
    (echo Dlg14=SdFinish-0)          >> %ISSFile%
    (echo.)                  >> %ISSFile%
    (echo [SdWelcome-0])           >> %ISSFile%
    (echo Result=1)              >> %ISSFile%
    (echo.)                  >> %ISSFile%
    (echo [DlgMachine-0])           >> %ISSFile%
    (echo Type=1)               >> %ISSFile%
    (echo Result=1)              >> %ISSFile%
    (echo.)                  >> %ISSFile%
    (echo [DlgInstallMode-0])         >> %ISSFile%
    (echo Type=32)              >> %ISSFile%
    (echo Result=1)              >> %ISSFile%
    (echo.)                  >> %ISSFile%
    (echo [DlgAdvanced-0])          >> %ISSFile%
    (echo AdvType=4)             >> %ISSFile%
    (echo Result=1)              >> %ISSFile%
    (echo.)                  >> %ISSFile%
    (echo [SdRegisterUser-0])         >> %ISSFile%
    (echo szName=%CompanyName%)        >> %ISSFile%
    (echo Result=1)              >> %ISSFile%
    (echo.)                  >> %ISSFile%
    (echo [SdLicense-0])           >> %ISSFile%
    (echo Result=1)              >> %ISSFile%
    (echo.)                  >> %ISSFile%
    (echo [DlgClientServer-0])        >> %ISSFile%
    (echo Type=2)               >> %ISSFile%
    (echo Result=1)              >> %ISSFile%
    (echo.)                  >> %ISSFile%
    (echo [DlgInstanceName-0])        >> %ISSFile%
    (echo InstanceName=%InstanceName%)    >> %ISSFile%
    (echo Result=1)              >> %ISSFile%
    (echo.)                  >> %ISSFile%
    (echo [SetupTypeSQL-0])          >> %ISSFile%
    (echo szDir=%SQLpath%)          >> %ISSFile%
    (echo Result=301)             >> %ISSFile%
    (echo szDataDir=%SQLDataPath%)      >> %ISSFile%
    (echo.)                  >> %ISSFile%
    (echo [DlgServices-0])          >> %ISSFile%
    (echo Local-Domain=61680)         >> %ISSFile%
    (echo AutoStart=15)            >> %ISSFile%
    (echo SQLDomain=MSFT)           >> %ISSFile%
    (echo SQLDomainAcct=ServiceSQL)      >> %ISSFile%
    (echo SQLDomainPwd=097f738fb19d058f27b5) >> %ISSFile%
    (echo AgtDomain=MSFT)           >> %ISSFile%
    (echo AgtDomainAcct=ServiceSQL)      >> %ISSFile%
    (echo AgtDomainPwd=097f738fb19d058f27b5) >> %ISSFile%
    (echo Result=1)              >> %ISSFile%
    (echo.)                  >> %ISSFile%
    (echo [DlgSQLSecurity-0])         >> %ISSFile%
    (echo LoginMode=2)            >> %ISSFile%
    (echo szPwd=097f738fb19d058f27b5)     >> %ISSFile%
    (echo Result=1)              >> %ISSFile%
    (echo.)                  >> %ISSFile%
    (echo [DlgCollation-0])          >> %ISSFile%
    (echo collation_name=%SQLCollation%)   >> %ISSFile%
    (echo Result=1)              >> %ISSFile%
    (echo.)                  >> %ISSFile%
    (echo [DlgServerNetwork-0])        >> %ISSFile%
    (echo NetworkLibs=-268431361)       >> %ISSFile%
    (echo TCPPort=%TCPPort%)         >> %ISSFile%
    (echo TCPPrxy=Default)          >> %ISSFile%
    (echo NMPPipeName=\\.\pipe\sql\query)   >> %ISSFile%
    (echo Result=1)              >> %ISSFile%
    (echo.)                  >> %ISSFile%
    (echo [SdStartCopy-0])          >> %ISSFile%
    (echo Result=1)              >> %ISSFile%
    (echo.)                  >> %ISSFile%
    (echo [License-0])            >> %ISSFile%
    (echo LicenseMode=%LicenseMode%)     >> %ISSFile%
    (echo LicenseLimit=%LicenseLimit%)    >> %ISSFile%
    (echo Result=1)              >> %ISSFile%
    (echo.)                  >> %ISSFile%
    (echo [SdFinish-0])            >> %ISSFile%
    (echo Result=1)              >> %ISSFile%
    (echo bOpt1=0)              >> %ISSFile%
    (echo bOpt2=0)              >> %ISSFile%

The couple of last pieces of work that we need to do before we have finished the process of writing out the .iss file, is to manage what happens if the user selects to use a service account rather than the LocalSystem account to run SQL Server under, and the security mode that SQL Server is going to run under (Mixed Mode or Windows Authentication).

First let's take a look at what happens if the user of our script specifies the account that the SQL Service, is going to run under.

To do this, we need to extend out the logic for the Service Account that we looked at earlier (when checking for the existence of variables). So, in the [DlgServices-0] section in code, we need to replace the code that is similar to Listing 5 with the code in Listing 6.

Listing 5—Section to Replace in the Code.

    (echo [DlgServices-0])          >> %ISSFile%
    (echo Local-Domain=61680)         >> %ISSFile%
    (echo AutoStart=15)            >> %ISSFile%
    (echo SQLDomain=MSFT)           >> %ISSFile%
    (echo SQLDomainAcct=ServiceSQL)      >> %ISSFile%
    (echo SQLDomainPwd=097f738fb19d058f27b5) >> %ISSFile%
    (echo AgtDomain=MSFT)           >> %ISSFile%
    (echo AgtDomainAcct=ServiceSQL)      >> %ISSFile%
    (echo AgtDomainPwd=097f738fb19d058f27b5) >> %ISSFile%
    (echo Result=1)              >> %ISSFile%
    (echo.)                  >> %ISSFile%

Listing 6—Replace Code in Listing 5 with Code in Listing 6.

    (echo [DlgServices-0])          >> %ISSFile%
    REM If we are not running under LocalSystem then set up the service account
    REM with the domain name and password
    if not "%SvcActName%"=="LocalSystem" (
        (echo Local-Domain=61680)     >> %ISSFile%
        (echo AutoStart=15)        >> %ISSFile%
        (echo SQLDomain=%SvcActDom%)    >> %ISSFile%
        (echo SQLDomainAcct=%SvcActName%) >> %ISSFile%
        (echo SQLDomainPwd=%SvcActPwd%)  >> %ISSFile%
        (echo AgtDomain=%SvcActDom%)    >> %ISSFile%
        (echo AgtDomainAcct=%SvcActName%) >> %ISSFile%
        (echo AgtDomainPwd=%SvcActPwd%)  >> %ISSFile%
    ) else (
        (echo Local-Domain=3855)      >> %ISSFile%
        (echo AutoStart=15)        >> %ISSFile%
    )
    (echo Result=1)              >> %ISSFile%
    (echo.)                  >> %ISSFile%

To set the security mode of SQL Server, we basically use the same logic to decide which of the security modes we use. Take a look at the code listed in 7, and replace it with code listed in Listing 8.

Listing 7—Section to Replace in the Code.

    (echo [DlgSQLSecurity-0])         >> %ISSFile%
    (echo LoginMode=2)            >> %ISSFile%
    (echo szPwd=097f738fb19d058f27b5)     >> %ISSFile%
    (echo Result=1)              >> %ISSFile%
    (echo.)                  >> %ISSFile%
    (echo.)                  >> %ISSFile%

Listing 8—Replace Code in Listing 7 with Code in Listing 8.

    (echo [DlgSQLSecurity-0])         >> %IISFile%
    REM If we are not using MixedMode authentication we set the LoginMode=1 
    REM else the LoginMode is 2 with a password for the "sa" account
    if "%MixedMode:~0,1%"=="N" (
        (echo LoginMode=1)         >> %IISFile%
    ) else (
        (echo LoginMode=2)         >> %IISFile%
        (echo szPwd=%SQLsaPwd%)       >> %IISFile%
    )
    (echo Result=1)              >> %IISFile%
    (echo.)                  >> %ISSFile%

By putting in this very simple logic, we can now run SQL Server under the LocalSystem or a specific Service Account, and to decide which type of security mode SQL will use. Neat, huh?

  • + Share This
  • 🔖 Save To Your Account