6

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.

0

2 Answers 2

3

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
  3. Open Access, select External Data->New Data Source->From Other Sources->ODBC Database
  4. Select Link to the data source by creating a linked table
  5. Select your ODBC driver file or source
  6. Enter credentials
  7. Select tables you want to link
  8. Delete Access tables and rename MySQL tables
  9. From Tables list, right click first Access table and delete
  10. Right-click corresponding MySQL table and rename to exactly match deleted Access table
  11. 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.

0
0

I suspect the issue is that in SQL Server Management Studio you need to map the database in the user login permissions, and then sign in. The process should then continue.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.