Tuesday, April 13, 2010

WebInspect's scan repository and SQL Server Express

Many HP WebInspect evaluations or anonymous trial downloads have been complicated by the Microsoft SQL Server Express component. In today's posting I will address pretty much everything you need to know to properly configure this database for WebInspect. Any time I refer to "SQL Express" here it should be understood as "Microsoft SQL Server 2008 Express Edition" and/or "Microsoft SQL Server 2005 Express Edition", and not any other versions. My original posting was delayed because WebInspect 8 SP2 had just added support for SQL 2008, but thus far it works the same and so here's your post.


By default, WebInspect 8.0 SP2 expects to use SQL Server Express (2008 or 2005) as its scan data repository. This is used in real-time to store the assessment findings. WebInspect expects this service to be running locally with the default named user instance of "SQLEXPRESS". SQL Express is not a true prerequisite that prevents the installation of WebInspect from completing, nor is it required to open the WebInspect UI and poke around. However the moment you try anything that touches the scan database, such as importing the Sample Scan or any other exported scan file, or kicking off a new assessment, you will discover this missing piece. The reason that this is not a hard installation requirement is that the user could instead use any full installation of SQL Server (2008 or 2005) that is at their disposal, whether it is installed on the localhost or elsewhere on their LAN. In such a situation one would not need SQL Express installed at all (see next blog entry for that).

At present, SQL Express 2005 is up to Service Pack 3, which is the preferred version for WebInspect 8, even though 2005 Express' Service Pack 2 will also work. For SQL Express 2008, you will want the Service Pack 1 (SP1) version. SQL Express has the benefits of being free and relatively lightweight on the machine, although it does have a natural 4 GB limitation on the amount of data stored per assessment. Obviously, switching to use a full SQL server would negate this 4 GB upper limit. Most assessments by WebInspect are less that 1 GB in size, so this would only be an issue with large enterprise sites, highly dynamic sites, and/or scans against such sites without optimized scan settings. Another benefit of using a database is that in the event of a crash or shutdown, the scan data is safe and the assessment can simply be Resumed once WebInspect is re-opened.


Once SQL Express is properly installed and running, there is no other care or feeding required in order to use it with WebInspect. So how to we get there?

1. Assuming a 32-bit OS, acquire the 32-bit version of the SQL Express install file, "SQLEXPR32.EXE". Do not use the installer that supports both 64-bit and 32-bit OS unless you are specifically using a 64-bit OS! I have also seen troubles when using the installer that includes SQL Studio Express. Just get the lean install for now.

2. Run the installation with the following caveats.

2a. Enable the box to "Hide the Advanced Installation Options". The options this would provide are generally not needed and can cause you trouble on your "default" installation. If you need to put SQL Express on a non-C: drive, you will need to use the Advanced settings, but keep your demands/settings simple.

2b. Enable any box asking to Create or Generate "Named User Instances". This is the crux of how SQL Express operates. Do not change the named user instance from "SQLEXPRESS".

2c. Enable any box asking to Add the Current User to the database administrators (dba).

3. You may start using SQL Express and WebInspect immediately after the installation, but a reboot is recommended to shake out any issues from the install.

When running, SQL Express will be represented by the Windows process, "sqlsrvr.exe". If you wish to interface directly with the SQL Express system, there is a CLI tool (SQLCLI.EXE) included with the installation or you may download and install the Microsoft SQL Server Management Studio Express for your version (2008 or 2005). The scan data held within the resulting WebInspect scan files (*.MDF) is visible using either tool, although certain columns or tables may be encoded/encrypted and there is no published database schema. HP's Application Security Center and its developers reserve the right to alter the database schema at any time in order to maintain and implement product features. You can still figure out a good quantity of the filed linkages by yourself, but just expect your assumptions to change over time and releases.

Scan Initialization Failed

By far the biggest error associated with WebInspect and SQL Express is the "Scan Initialization Failed" message. This is the nasty-gram you will receive if SQL Express is not configured correctly or even running, and it will occur only when you seek to interface with the database (importing, launch new scan, view the Manage Scans pane, et al). If you swear that the database is running properly, you can configure WebInspect to use SQL Express as if it were a "remote" database, i.e. full SQL Server installation, by filling in the (full) SQL Server option and pointing to the local "SQLEXPRESS" database by name. This works for verification, but not as a long-term solution as all of your scans will be stuffed inside that one database instance with a total upper limit of 4 GB. But if it works, then you know the problem is with your SQL Express Named User Instances and not the product itself. Below are the primary items to review for the Scan Initialization Failed message.

* Clear the appropriate SQL cache folder:

- XP: C:\Documents and Settings\%CURRENTUSER%\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS\

- Vista: C:\Users\%CURRENTUSER%\Appdata\Local\Microsoft\Microsoft SQL Server Data\SQLEXPRESS\

Erasing this cache folder and re-opening WebInspect will cause the folder to regenerate. This can be necessary even after you needed to correct something else, as this folder could be holding a corrupted form of the entries, preventing your fix to truly operate. You may need to Stop the SQL Server service and/or exit WebInspect before you will be allowed to delete this folder.

* Ensure you are not using Windows XP SP2 over RDP or Terminal Services. Microsoft had a known issue with this combination in that SQL Express would not know what Named User Instance to use for the remote user. Connecting once locally could fix this, as could a special Microsoft Support patch file. The best fix is to upgrade to XP SP3, since you will be better off in many other ways than running SP2.

Reference: SQL Express RDP Patch - http://support.microsoft.com/?id=896613 - "You cannot connect to Visual Studio SQL Server Express on a remote Windows XP Service Pack 2-based computer "

* (rare) Delete the Scans.XML file used by WebInspect to index the saved scans. This file will be be rebuilt when WebInspect re-opens. Assuming product defaults, this file is located in the following directory. If not using defaults, this file will be wherever you have defined your \ScanData\ folder under WebInspect's Application Settings screens ("Directories").

folder: C:\Documents and Settings\%CURRENTUSER%\Local Settings\Application Data\SPI Dynamics\WebInspect\7.0\ScanData\

Also, verify that there is a "version.txt" file accompanying the Scans.xml file. It can be created by hand by copying the current contents from another up-to-date ASC product installation or from the ASC Support team.

* Verify the user is logged on as a Local Administrator, and not using "Run As". Also rather rare, but it can happen on more secured networks.

* Verify that the "SQL Server (SQLEXPRESS)" Windows service is being run by the default service name, "NT AUTHORITY\NetworkService". This Initialization error can occur when a user attempts to configure the "Run As" a non-Local Administrative domain user or network service account such as "DomainName\SQLServer2005SQLBrowserUser", or if the NetworkService has been limited in some way by Group Policies. Switching to your own (Administrative) user account might correct or test this scenario.

*Even more rarely, the installation is done properly but the Named User Instances are not truly enabled during it, behind the scenes. Looking into the database will report that they are enabled, but then using this quick CLI process you can reset them to enabled. You can run the same two queries from the MS Studio Express as well, but they must be run separately, it will not correct the issue if they are run as one query command.

CLI Location: C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE

Steps for enabling named user instances:

1. Use the -S option to specify the local machine and the SQLEXPRESS default instance name.
2. Enter the first command followed by GO.
3. Enter the second command followed by GO.
4. Type "exit" to leave the program.

* Example:

sp_configure 'user instances enabled' ,1



No comments:

Post a Comment