SSMA for Access – Fixing Migration Assistant Wizard Failure at Link Tables

ms accessodbcsql serversql-server-2017ssma

When using Sql Server Migration Assistant for Access with the Migration Wizard, all goes well converting an Access (2016) database to SQL Server Express (2017) until I get to the Link Tables step. Here the SQL Server connection dialogue appears:

sql server connection dialogue

I enter the same credentials used to create the tables. My understanding is that this dialogue appears to allow for using a different sql server login with Access, so probably nothing out of the ordinary here. But then I receive the following error:

error

Connection failed:
SQLState: '01000'
SQL Server Error: 772
[Microsoft][ODBC SQL Server Driver]
[DBNETLIB]ConnectionOpen (SECDoClientHandshake()).
Connection failed:
SQLState: '08001'
SQL Server Error: 18
[Microsoft][ODBC SQL Server Driver]
[DBNETLIB]SSL Security error

I've tried with and without encrypting the connection with the same error, so it doesn't seem like an SSL error.

I have four SQL Server ODBC drivers installed:

  • ODBC Driver 11 for SQL Server
  • ODBC Driver 13 for SQL Server
  • SQL Server
  • SQL Server Native Client 11.0

If I create a connections using the ODBC Data Source Administrator, I can produce the same error using the SQL Server driver. The other three work fine. So it seems that SSMA is using the SQL Server driver (oldest of the four) instead of the SQL Server Native Client, which is supposedly a dependency of SSMA.

Best Answer

I was able to work around the problem by doing the following:

  1. Allow the migration assistant to complete (with link converted tables failing)
  2. Manually add MySQL linked tables to Access
    1. Open Access, select External Data->New Data Source->From Other Sources->ODBC Database
    2. Select Link to the data source by creating a linked table
    3. Select your ODBC driver file or source
    4. Enter credentials
    5. Select tables you want to link
  3. Delete Access tables and rename MySQL tables
    1. From Tables list, right click first Access table and delete
    2. Right-click corresponding MySQL table and rename to exactly match deleted Access table
    3. Repeat for every table you want to re-map

Provided that you rename each MySQL table to match the deleted Access tables, all queries should work.

Related Question