Mark Berry July 15, 2010
For the last several years, I’ve been running Liberum Help Desk on SQL 2000 under IIS 6. With my recent upgrade to Small Business Server 2008, I needed to move the Help Desk to SQL 2005 Express and IIS 7. Here are my notes on the process.
Update November 26, 2012: today I upgraded from SBS 2008 to Server 2012 Essentials with SQL 2012 Express and IIS 8.0 using mostly the same procedure. See special notes in this color.
Prepare IIS 7
Liberum Help Desk is a classic ASP application, so to run it under IIS 7 or IIS 8, as explained in this Technet article, you’ll need to enable the following roles under IIS 7:
- Request Filtering
- ISAPI Extensions
It turns out those are already enabled on SBS 2008 and Server 2012 Essentials, so I didn’t have to do anything here.
Set Up an Active Directory User
Add a new user in Active Directory that the web site can use for accessing the database, for example DOMAIN\HelpDeskUser. Create a complex password and set it to Never Expires. I left mine in the default Domain User group.
Create and Configure the Web Site
1. Copy the Help Desk web site files from your old web server to your new server.
2. Open IIS Manager and create a new web site pointing to the path where you just copied the Help Desk files. The Connect as button here only controls access to the physical path. I was able to leave it as the default (Application user – pass-through authentication).
3. Still in IIS Manager, click on Application Pools in the left pane, then right-click on the new application pool created for your web site and choose Advanced Settings.
3.1. Change the .NET Framework Version to No Managed Code.
3.2. Under Process Model > Identity, click on the small button and change the logon from Network Service to the Custom account that you created earlier (DOMAIN\HelpDeskUser).
4. Go back to the web site and double-click on ASP to open the ASP properties:
Set Enable Parent Paths to True:
In the right pane, under Actions, click Apply to save the change.
5. Go back to the web site again and double-click on Authentication to open the Authentication properties.
Under IIS 8, I got a strange message at this point:
So it doesn’t like the only line in my applications’s web.config file:
After reviewing this StackOverflow answer, my understanding is that I can’t change that setting at the application level because it’s set to
overrideModeDefault="Deny" at the applicationHost.config level. But why is the web.config file even there? There is no need to turn off Windows Authenticaion at the application level; it is already off at the server level. So I simply renamed web.config to web.config.old, restarted the site, and web back in to Authentication. No more error and I was able to proceed as follows.
Right-click on Anonymous Authentication, choose Edit, and change the Anonymous user identity to use the Application pool identity.
If you leave this set to IUSER, your database logins won’t work!
6. Open the firewall (usually TCP port 80) so your site will be accessible.
Update 7/21/2010: Follow the steps in this post to allow your custom user to update the IIS persistent cache: Template Persistent Cache Error with Classic ASP under IIS 7.
Under Server 2012, when I checked the permissions on C:\Inetpub\inetpub\temp\ASP Compiled Templates, there were no permissions assigned at all:
Strange as that seems, since the site is working without them, I did not make any changes there.
Move the Database
I spent a lot of time on with this step because it’s very difficult to move a database containing full-text index configuration if the FTTEXT path has changed.
This is much improved in SQL 2005 and 2012. All I had to do was a backup and restore, and the full-text index came over as well. No need to remove the catalogs, or to re-create the indexes.
My HelpDesk database is relatively small, so the index generates in under 30 seconds. It should be much easier to just drop the full-text index on SQL 2000 before backing up the database for moving to SQL 2005. Although not the route I followed, I’ll document what (I think) is necessary to do that.
1. In SQL 2000 Enterprise Manager, right-click on the Full-Text Catalogs and Remove All Catalogs. Skip this when migrating from SQL 2005 to 2012.
2. Back up the HelpDesk database to a file. Copy the file to your new server.
3. On your new server, if you haven’t already, install a new instance of Microsoft SQL Server 2005 Express Edition with Advanced Services Service Pack 2 (download) or SQL Server 2012 Express Edition with Advanced Services with Service Pack 1 (download). The Advanced Services edition is required for full-text search capabilities (which is a pretty awesome feature in a free product!). If, like me, you do not notice the optional Full-Text Search option during the initial installation, you’ll have to go back and do a Change installation to add that feature:
4. You’ll also need Microsoft SQL Server Management Studio Express if that wasn’t included in your install(download).
5. Open SQL Management Studio. Under Security > Logins, create a new login that maps to the Active Directory user you created earlier (DOMAIN\HelpDeskUser).
6. Restore the HelpDesk database backup you created just above. On 2012, I chose to rename the files and put the full-text index in the FTData folder:
7 Under HelpDesk > Security > Users, create a new user that matches the Active Directory user you created earlier (again DOMAIN\HelpDeskUser). If it already has a user by that name, delete it and re-add it to make sure the user links to the new domain. (I am moving to a new domain with the same name as my previous domain.) Assign the db_datareader and db_datawriter roles to the user.
8. In your Help Desk web site, edit settings.asp with a text editor. Because I installed a named instance of SQL 2005 Express, I had to change the Application(“SQLServer”) line. I was already using SQL Server with integrated security, so the Application(“DBType”) did not change.
9. At this point you should be able to log in to the Help Desk. Go to Administration > Configure Site and change your SMTP Server.
Note JMail 4.5 installed fine but I get an object error when trying to send a test mail from the Help Desk. I I enabled 32-bit applications as suggested in this thread, but that kills the site (Service Unavailable) and the OWA component of Exchange 2007 Service Pack 3 raises errors in the event log. I’m still working on this issue.
Update 7/23/2010: Since 32-bit JMail is giving IIS with Exchange 2007 SP3 fits, I thought I’d check whether one of the other mail components supported by Liberum Help Desk is available in a 64-bit version. Success: I installed the 64-bit version of ASPEmail from Persits Software, changed the Help Desk configuration to use ASPEmail, and voila!, I’m sending email.
Update 7/26/2010: An extra setting is required in Exchange 2007 to allow ASPEmail to anonymously send mail outside the network. See the bottom of this article.
With 2012, I installed the latest 64-bit free version of Persits’ ASPEmail. Then, because I am no longer running Exchange, and because my mail server requires authentication (which the free version of ASPEmail does not support), I had to set up the server’s native SMTP service, then configure it as a smarthost to Office 365.
Note You can set up SMTP as a Windows 2012 “Feature.” For more information, Search Server Manager help for “SMTP”. Start the Internet Information Services (IIS) 6.0 Manager application to access SMTP configuration options.
Set Up Full-Text Indexing
Skip this section if you’re upgrading from 2005 to 2012!
If you plan to use full-text indexing, you’ll need to create the index. Some UI elements are missing from the Express edition, so it’s not quite as easy as it is under SQL 2000. Here are the basics.
1. Open SQL Management Studio. Right-click on the HelpDesk database and choose Properties, then Files. Check the Use full-text indexing box. If the box is grayed out, you may need to install the optional Full-Text Search feature—see above.
2. Open a new query and use a T-SQL command to create a full-text catalog. For example:
CREATE FULLTEXT CATALOG HelpDesk_Problems_FullTextIndex
You can check for catalogs with this command:
SELECT * FROM sysfulltextcatalogs
3. Back in the main SQL Management Studio window, expand the HelpDesk database and the list of Tables. Right-click and choose Refresh (so the Management Studio will see the catalog you just created). Then right-click on the problems table and choose Design. Right-click in the design window and choose Fulltext Index. In the Full-text Index dialog, click on the Add button. Click the little button next to Columns and choose the title, description, and solution columns. Leave the other fields with their default values.
Click on Close, then exit the Design view, confirming that you do want to save changes to the problems table. At this point, you can go back into Design view, open the Full-text index dialog, and check whether population (crawl) is complete—mine took 10 seconds:
Back Up without the Full-Text Index
SQL 2005 Express is VSS-aware, so I’m counting on my daily server backups to include the HelpDesk database for disaster recovery. However I also want to be able to create a backup manually that does not include the full-text index, in case the backup needs to be restored elsewhere. To that end, I created the Back Up HelpDesk script which you are free to try and modify at your own risk. Note that this approach assumes the database uses the Simple recovery model. It seems that otherwise, log backups are required to fully remove the full-text catalog.
Update November 12, 2013 I decided to create daily backups as well using a scheduled task and the sp_BackupDatabases stored procedure provided in MSKB 2019698. Note that in order for the task to run, the Windows user under which you run the scheduled task must be defined as a SQL Login with appropriate permissions on the server or database. I created a special user for these kinds of system tasks and assigned it the sysadmin role on the SQL server.