none
SQL Server Migration Assistant for Access: a newer SNAC Provider is required for Link operations RRS feed

  • Question

  • Hi,

    I have successfully migrated an Access database to Azure SQL.

    However when I try to link the migrated tables in Access I get an error:

    A newer SNAC Provider is required for Link operations. Version 10.5 or higher is required.

    I have a newer Version installed on my computer: Microsoft SQL Server 2012 Native Client 11.3.

    Any ideas what else could cause this error?

    I am using latest Version of SSMA 8.4.0

    I am using Microsoft Office Access (Office365-edition)

    Thanks

    Kjetil 

    Sunday, September 22, 2019 11:33 AM

All replies

  •  Hi KjetilJansen,

    Your Microsoft Office Access is 64 bit(x64) or 32 bit(x86)?  The Microsoft SQL Server 2012 Native Client 11.3 that your installed is 32 bit or b4 bit? Please use select @@version to check your SQL server is 64 bit or 32 bit?  You need keep them using same version 32 bit or b4 bit.  After that , please try to install the same version of  Microsoft® SQL Server® 2012 Native Client from here. 

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Monday, September 23, 2019 2:30 AM
  • Hi Cathy,

    Many thanks for Your suggestions, very much appreciated,

    I have checked the following:

    Microsoft Access, 64 bit

    Microsoft Access Database Engine 2010, 64 bit

    SQL Server Native Client, 64 bit

    I have SQL Server Express 2017 installed on my computer, 64-bit

    Since I am connecting to the Azure SQL, I am not able to get a bit-Version from there:

    Microsoft SQL Azure (RTM) - 12.0.2000.8   Sep  9 2019 09:35:03   Copyright (C) 2019 Microsoft Corporation

    I have reinstalled the SNAC from Your link and then retried linking some tables.

    Unfortunately without success.

     

    Could there possibly be other uncompatible Components in use here that may cause this problem?

    Many thanks for Your help.

    Kjetil

    Monday, September 23, 2019 8:31 AM
  • Hello,

    Here's what SSMA does to detect SNAC version:

    1. In the registry, under HKLM\SOFTWARE\ODBC\ODBCINST.INI, find last key that contains with "SQL Server Native Client" in its name.
    2. Extract DLL path from the Setup value.
    3. Get Product version of the DLL from step 2.
    4. Use first two segments of the version and compare them to 10.5.

    With this, can you check what you have in registry under HKLM\SOFTWARE\ODBC\ODBCINST.INI? I have a suspicion that you have multiple drivers installed and SSMA just picks the wrong one. If that is the case - you can either uninstall older versions or temporary rename these keys so that they don't match "SQL Server Native Client".

    Thanks and regards,
    Alex.

    Monday, September 23, 2019 5:44 PM
  • Hi Alex, thanks for Your suggestions.

    I reviewed the path in the registry editor and this is what I found:

    (Sorry, could not post an image of screen yet as my account hasn't yet been verified)

    The path is not exact match With Your example but hopefully I am in the right Place. 

    Maybe this gives you some Insights. I don't have the knowledge to determine whether this is what to expect or not.

    Not sure if "SQL Server Native Client RDA 11.0" represents what is required by SSMA and what I have installed.

    The other one "SQL Server Native Client 11.0" referred to 32-bit Version which I renamed.

    I restarted the computer but got the same error when I retried to link the tables.

    Any further advice for the NeXT step?

    Many thanks

    Kjetil



    • Edited by KjetilJansen Tuesday, September 24, 2019 8:13 AM
    Tuesday, September 24, 2019 8:05 AM
  • Maybe best to wait until I can upload the screen image, as this post may not yet make sense without the image.
    Tuesday, September 24, 2019 8:14 AM
  • Hi,

    It might be that RDA driver is throwing it off. If it considers RDS driver to be the last one in the list - it will use the version from it, which might be versioned differently from the regular SNAC. Can you temporary rename the RDA key so that it doesn't contain "SQL Server Native Client" in it? Just remove a space or something, so it's easy to restore.

    The regular "SQL Server Native Client" in that path should not be 32-bit, as it 64-bit hive (assuming you are running 64-bit Windows). If you are using 32-bit version of SSMA, then you should be looking under HKLM\SOFTWARE\WOW6432Node\ODBC\ODBCINST.INI, as that's what SSMA will "see".

    Regards,
    Alex.

    Tuesday, October 8, 2019 5:20 AM
  • Hi Alex,

    Thanks again for Your suggestions.

    I renamed the SNAC and tried again. This time I didn't get the SNAC error and the linking process started.


    However the operation failed With the following log output:

    Linking tables...
    Analyzing metadata...
    Preparing table ERPM Connect SQL Master20190904.[tbl_ActivityTemplate]...
    Starting Phase #0
    Processing table '[tbl_ActivityTemplate]' in file 'ERPM Connect SQL Master20190904'... ...
    Linking & backing up table 'tbl_ActivityTemplate' from database 'ERPM Connect SQL Master20190904'...
    Failed to link tables.
     Errors: Failed to link tables.
    Trying to restore the original table name...
    Original table name restored.
    Linking tables skipped because the table backup failed.
    Operation failed.
     Errors: Linking tables skipped because the table backup failed.
    Link & back up complete.

    Any ideas here?

    best regards

    Kjetil


    KjetilJansen

    Tuesday, October 8, 2019 1:22 PM