Installing the SQL Server Migration Assistant 2008 for Access v4.2: FAIL and Workaround

Installing the SQL Server Migration Assistant v4.2 for Access from the download page mentioned in the Windows Azure and Cloud Computing Posts for 8/12/2010+ post’s Easily migrate Access data to SQL Azure item failed due to a problem with downloading the license file that’s required to run SSMA for Access.

Background

The following Overview, Requirements and Licensing information for SSMA for Access v4.2 is from the download page:

Overview

Microsoft SQL Server Migration Assistant (SSMA) for Access is a tool for migrating databases from Microsoft Access to SQL Server 2005, SQL Server 2008 and SQL Server 2008 R2. SSMA 2008 for Access also supports migrating to SQL Azure.

SSMA for Access converts Access database objects to SQL Server database objects, loads those objects into SQL Server, and then migrates data from Access to SQL Server. You can also link Access tables to SQL Server tables so that you can continue to use your existing Access front-end applications with SQL Server or SQL Azure.

SSMA for Access supports migration from Access 97 and higher to any edition of SQL Server.

Requirements for the SSMA for Access:

Supported Operating Systems: Windows 7; Windows Server 2003; Windows Server 2008; Windows Server 2008 R2; Windows Vista; Windows XP

Microsoft Windows Installer 3.1 or a later version.

The Microsoft .NET Framework version 2.0 or a later version. The .NET Framework version 2.0 is available on the SQL Server product media. You can also obtain it from the .NET Framework Developer Center Web site.

The ability to access the computer that hosts the target instance of SQL Server.

DAO provider version 12.0 or 14.0. You can install DAO provider from Microsoft Office 2010/2007 product or download it from Microsoft web site.

Microsoft SQL Server Native Client (SNAC) version 10.5 and above for migrating to SQL Azure. You can install SNAC from Microsoft SQL Server 2008 R2 product or download it from Microsoft web site (SQL Server 2008 R2 Feature Pack).

At least 1 GB RAM.

Licensing

SSMA is a FREE download, and is available for use by any customer or partner with no charge or any obligations. However, the product requires a license key for activation. The key can be obtained after quick and simple registration. Microsoft reserves the right to use an e-mail address or phone number provided during registration in order to contact a customer regarding his/her SSMA experience. To help you with the registration process, a License Key Required dialog box opens the first time that you start the SSMA program.

Problem Description

Following are the steps I took to reproduce the problem on several machine:

1. Download SSMA 2008 for Access 4.2.zip from the Download Center’s Microsoft SQL Server Migration Assistant for Access v4.2 page and save it to a compressed folder.

2. Click Run to execute the SSMA 2008 for Access 4.2.exe installer file from the compressed folder and open the initial Setup Wizard dialog.

Click Next.

3. If you don’t have required prerequisites installed, you are prompted to install them:

 

Click Download to open the Microsoft Access 2010 Runtime page, which installs the Access 14 DOA libraries.

Download the ZIP file, then extract and run the AccessRuntime2010.exe or AccessRuntime2010_x64.exe file (~175 MB) to install the prerequisite software.

4. Click Next, accept the end-user license agreement, click Next again to bypass the Usage Report Settings dialog and open the Choose Setup Type dialog:

5. Click Complete to open the Ready to Install dialog and click Install to begin installation.

6. When setup completes, click Finish to dismiss the Setup Wizard.

Note: Setup installs the executable and related files in the C:\Microsoft SQL Server Migration Assistant 2008 for Access folder and subfolders. The Sample Console Scripts subfolder contains four sample scripts that work with the SQL Server Migration Assistant 2008 for Access Command Prompt application.7. Setup installs a SQL Server Migration Assistant 2008 for Access node in the Start menu with the following items:

• Help
• SQL Server Migration Assistant 2008 for Access (32-bit)*
• SQL Server Migration Assistant 2008 for Access Command Prompt
• SQL Server Migration Assistant 2008 for Access
• ReadMe

* Only appears when you install SSMA on a 64-bit machine.

7. Click the  SQL Server Migration Assistant 2008 menu item for Access to open the License Management dialog:

Note: The license folder defaults to C:\Users\UserName\AppData\Roaming\Microsoft SQL Server Migration Assistant\a2ss-2008.

8. Click the license registration page link, sign in with your Windows Live ID, and complete any missing required information items.

9. Click Finish, Enable File Download in the warning bar at the top of Internet Explorer’s window and wait for a File download dialog, which never appears. Instead, the License Registration page refreshes.

Note: The problem occurs for me with Internet Explorer 8 on 32-bit machines running Vista Ultimate, as well as on 64-bit Windows Server 2008 R2 as a host OS and virtual client with Windows 7 Professional running as a guest OS.

It’s interesting to note that Lucas A. Romão apparently didn’t encounter this problem when writing Instalando o SSMA - SQL Server Migration Assistant for Access (in Brasilian Portuguese) on 8/15/2010, the same date as this post.

Workaround

To get a handle on the problem, I installed Eric Lawrence’s Fiddler2 and captured the session of previous steps 7 through 9. Here’s Fiddler2’s capture with the download request highlighted at #61 (click image for full size 1024px capture):

Manually executing the download request by double-clicking line #61 produced the expected result:

You can obtain the license file here, if you encounter the preceding problem:

Note: The file downloads with SSMA 2008 for Access 4.2.license as its name. After you save it to your C:\Users\UserName\AppData\Roaming\Microsoft SQL Server Migration Assistant\a2ss-2008, change its name to access-ssma.license as shown here:

 

Return focus to the License Management dialog and click Refresh License.

 

Note: If you receive a message that the license key failed to refresh, check the ssma.log file in the C:\Users\UserName\AppData\Roaming\Microsoft SQL Server Migration Assistant\a2ss-2008/logs folder. The following two errors result from failing to copy the license file to the correct folder and not changing its name to access-ssma.license:

Click OK to open SSMA 2008 for Access with the Migration Wizard active:

 

Update 8/21/2010: My Migrating a Moderate-Size Access 2010 Database to SQL Azure with the SQL Server Migration Assistant post (updated 8/21/2010) to the OakLeaf Systems blog A complementary Using the SQL Server Migration Assistant 2008 for Access post that describes migrating and linking an Access application to an SQL Azure file will be posted shortly.

Technorati Tags: SQL Server Migration Assistant,SSMA,SSMA v4.2,SSMA 2008,Microsoft Access,Microsoft Access 2010,Access 2010,SQL Azure,Azure