Moving Old ASP Sites to 2008 Server, IIS 7, and SQL 2008

It was time to move many sites from W2K3 Server (Windows 2003 Server) and SQL 2000 Server to a new W2K8 Server (Windows 2008 Server) and SQL 2008 Server.  And if I ever have to do it again, I will check with these notes.

The greatest secret in technical life (and may be wider) is to change before you have to.  Anticipate and change before circumstances force you.  For a long time I was planning this migration, but I didn’t expect to go through so many hurdles and error messages.  Luckily, I found solutions that seem logical and clean.  So, let’s go into step-by-step details.

COPY DATABASE AND SITES

– Backup old Databases on old server (SQL 2000);
– Copy BAK file to a new 2008 Server;
– Restore Databases on a new server (SQL 2008 R2);
– Copy Site files and directories to a new 2008 server;
– Using IIS Manager Add Application pointing to new copied directories.

INSTALL MISSING LEGACY FEATURES

Try opening the site.  You could see this error:

HTTP Error 404.3 – Not Found
The page you are requesting cannot be served because of the
extension configuration. If the page is a script, add a handler.
If the file should be downloaded, add a MIME map.

Under detailed Error Information you could see

Module StaticFileModule

To fix this error you need to install ASP using W2K8 Add Features and Roles  under Server Manager – Roles – Web Server (IIS).

Try opening the site. You now might get this error:

An error occurred on the server when processing
the URL. Please contact the system administrator.

Your WEB Server needs to send more information to your browser.  To do that you need to issue this command on your web Server:

C:\inetpub\AdminScripts\adsutil.vbs set w3svc/AspScriptErrorSentToBrowser true

You should see a positive reply back:

AspScriptErrorSentToBrowser     : (BOOLEAN) True

If you do not have directory called AdminScripts, or if command above doesn’t work and generates errors – you need to install IIS 6.0 Management Compatibility features (also called IIS 6 Scripting Tools) using W2K8 Add Features and Roles.

[2012-03-16 FR  20:48] If you do not want to install “IIS 6 Scripting Tools”, you can issue this command:

%windir%\system32\inetsrv\appcmd set config -section:asp -scriptErrorSentToBrowser:true

You should see this response:

Applied configuration changes to section “system.webServer/asp” for “MACHINE/WEB
ROOT/APPHOST” at configuration commit path “MACHINE/WEBROOT/APPHOST”

[2011-05-17 TUE – There is a way to achieve the same result without the script.  Please see Comment bellow.]
ADJUST YOUR SQL CONNECTION STRING

On old server I couldn’t make Integrated Security to work so I was passing a hard-coded user name and password. Not clean!  But on W2K8 Server I know Integrated Security works great.  So now I decided to change my connection string.  I generated the string using Visual Web Developer 2010 and pasted it into an appropriate file in my ASP site.

I refreshed the page and saw this error message:

Provider error '80040e21' Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

That error means you have some kind of a problem with your connection string.  I was able to fix this problem by replacing

Integrated Security=True; 

(valid for .NET SqlClient library) with

Integrated Security=SSPI;

(valid for OLEDB)
ADJUSTING SQL SECURITY

Next error you likely to see would be:

Microsoft OLE DB Provider for SQL Server error '80040e4d' Login failed for user 'NT AUTHORITY\IUSR'.

Another similar error that I saw at this stage was:

Microsoft OLE DB Provider for SQL Server error '80040e14'
Cannot open database "YourDB" requested by the login. The login failed.

To fix both of these errors, add user NT AUTHORITY\IUSR to SQL and map it to your D/B as Owner. You are now ready to run your old ASP site under W2K8 Server and SQL 2008.

There is one more very painful error –  80004005, when you have Trusted_Connection=yes; or Integrated Security=SSPI; in your connection string.  This error doesn’t tell you, what user is causing the problem:

Microsoft OLE DB Provider for SQL Server error '80004005'
Cannot open database "DB_Trades" requested by the login. The login failed.
/Include/CONN.ASP, line 25

To figure out, which user causes the login problem, you need to open IIS log.  Physical location of IIS logs on the disk you can check with IIS Admin – Sites – Logging.  IIS Logs are organized by side ID.  Time stamp in IIS logs is 5 hours ahead of EST zone. Read the corresponding log and right after the error you will see the user name, that is not shown by the browser. Now, all you have to do is to delete this user from SQL security, add it back and map this user to your site database.  Do this using SQL Server Management Studio.

If you backup your D/B under one name and restore it under a different name, all tables might carry an old D/B name prefix in their names.  If all of a sudden one of your tables is not recognized:

Microsoft OLE DB Provider for SQL Server error '80040e37' Invalid object name 'tMaster'.

Now you need to reset ownership of all SQL tables back to dbo:

DECLARE @old sysname, @new sysname, @sql varchar(1000) SELECT @old = 'OldOwnerName' , @new = 'dbo' , @sql = ' IF EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.TABLES WHERE QUOTENAME(TABLE_SCHEMA)+''.''+QUOTENAME(TABLE_NAME) = ''?'' AND TABLE_SCHEMA = ''' + @old + ''' ) EXECUTE sp_changeobjectowner ''?'', ''' + @new + '''' EXECUTE sp_MSforeachtable @sql

Example above quoted from http://weblogs.asp.net/owscott/archive/2004/01/30/SQL-Table-Ownership-Changes_2C00_-Quick-and-Easy.aspx.  This example works right out of the box with no modifications or adjustments needed.  Just change the name of your old undesirable owner in the script.

We have to use a different approach with Stored Procedures.  If ASP all-of-a-sudden can’t find them, it means that owner on these are somebody other than dbo:

Microsoft OLE DB Provider for SQL Server error '80040e14'
Could not find stored procedure 'qEvents - By Month - Print Only'.

How to List All Stored Procedures
SELECT name AS [Stores Procedure Name] FROM sysobjects WHERE (xtype = ‘p’) AND (name NOT LIKE ‘dt%’) ORDER BY name

I found several methods to change Stored Procedures ownership, but none of them actually worked. As a workaround, we can use one-by-one method:

(1) generate a list of all Stored Procedures (see a side note),
(2) dump it in Excel,
(3) surround each name with constant string, and
(4) paste back into SQL Query to look something like this:

EXEC sp_changeobjectowner 'alaev.qAddressTypes', 'dbo'
EXEC sp_changeobjectowner 'alaev.qContactTypes', 'dbo'
EXEC sp_changeobjectowner 'alaev.qEvents - By Month', 'dbo'
EXEC sp_changeobjectowner 'alaev.qEvents - By Month - Print Only', 'dbo'
EXEC sp_changeobjectowner 'alaev.qEventTypes', 'dbo'

 

SETTING UP SECURITY

If new “old” site requires special security permission, do them now.  After all, parts of your site are already working and might need to be protected.

Disable Anonymous access.

Try to keep security consistent throughout entire site.

[11/03/10  Wednesday    09:46 ] If you are moving a legacy application from an old server, it is possible that you were utilizing Basic Authentication.  If that is the case, you need to run it over SSL.  Here are some steps how to create a self-signed certificate on a W2K8 Server and assigned it to your site.

Open IIS Manager and go to – [Your Computer Name] – Server Certificates.  Click on Create Self-Signed Certificate… and under friendly name type your MACHINE name.  That is it. You are done.

Now create/modify a new SITE with binding to https (port 443) and assign this new SSL certificate (that you just created) to your new site.  Now try to access your new site with http and https.

You shouldn’t be able to assess you site over HTTP and you should see a warning or error, when trying to access you site over HTTPS.  Both IE and Firefox allow you to bypass these warnings.

There are two problems here.  First, the certificate is issued not by a trusting authority (you), and second, the certificate is issued for your local machine (MACHINE) for example, but you are trying to access it using domain name or IP address.   So this solution is good for a little internal home-grown applications, but is not good for rolling out to the public.

Next step – rewrite those ASP applications using ASP.NET, where form security is written for you already.  But that is another 2-3 years down the road.  The good news is that you data is now migrated to the latest database engine, and you can access it over secured protocol.