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:
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:
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:
External Data->New Data Source->From Other Sources->ODBC Database
Link to the data source by creating a linked table
Provided that you rename each MySQL table to match the deleted Access tables, all queries should work.