locked
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
  • Hi,

    You should *not* have "_" in your "SQL Server Native Client 11.0" key as that's the driver you want it to find.

    To get additional error details, go to Tools -> Global Settings -> Logging and switch all logs to Debug and grab the log file path here as well. Then try linking the tables, when error happens, close SSMA, so that it flushes all traces to the file and check out the log file for details error message/stack trace. Would appreciate if you could share a stack trace with us - either here or through Help -> Feedback within the tool.

    Regards,
    Alex.

    Tuesday, October 22, 2019 6:15 AM
  • Hi Alex,

    I tried the following:

    1. Renamed back to the original name: "SQL Server Native Client 11.0"

    2. Retried to link tables. I got the same error. From the debug log: 

    [Info: Gui] [17704/1] [2019-10-22 10.52.14]: MainMenu: press button - text:&Tools, name:Tools
    [Info: Gui] [17704/1] [2019-10-22 10.52.16]: MainMenu: press button - text:&Link Table, name:LinkTable
    [Info: Gui] [17704/1] [2019-10-22 10.52.29]: For your link tables to work with Azure SQL Database, you need to install a newer SNAC provider. For more information please click on this link (http://go.microsoft.com/fwlink/?LinkId=196940)

    Complete log:

    [Mandatory: Generic] [17704/4] [2019-10-22 10.38.19]: SQL Server Migration Assistant for Access v8.4.0
    [Mandatory: Generic] [17704/4] [2019-10-22 10.38.19]: Starting assembly: SSMAforAccess, Version=8.4.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
    Created: 08/28/2019 23:35:22
    Last Modified: 08/28/2019 23:35:22
    OS Version: Microsoft Windows NT 10.0.18362.0
    OS architecture: x64
    Processor architecture: AMD64
    Current culture: nb-NO
    UI culture: nb-NO
    [Info: Gui] [17704/1] [2019-10-22 10.41.38]: MainMenu: press button - text:&File, name:File
    [Info: Gui] [17704/1] [2019-10-22 10.41.41]: MainMenu: press button - text:&Open Project..., name:openProject
    [Debug: Converter] [17704/14] [2019-10-22 10.41.56]: Registering NodeConverter for literal: Microsoft.SSMA.Framework.Access.SqlServerConverter.NodeConverters.BooleanLiteralConverter
    [Debug: Converter] [17704/14] [2019-10-22 10.41.56]: Registering NodeConverter for full-eqv-level-condition: Microsoft.SSMA.Framework.Access.SqlServerConverter.NodeConverters.EqvLevelConditionConverter
    [Debug: Converter] [17704/14] [2019-10-22 10.41.56]: Registering NodeConverter for mod-expression: Microsoft.SSMA.Framework.Access.SqlServerConverter.NodeConverters.IntDivisionExpressionConverter
    [Debug: Converter] [17704/14] [2019-10-22 10.41.56]: Registering NodeConverter for alike-operator: Microsoft.SSMA.Framework.Access.SqlServerConverter.NodeConverters.AlikeOperatorConverter
    [Debug: Converter] [17704/14] [2019-10-22 10.41.56]: Registering NodeConverter for like-operator: Microsoft.SSMA.Framework.Access.SqlServerConverter.NodeConverters.AlikeOperatorConverter
    [Debug: Converter] [17704/14] [2019-10-22 10.41.56]: Registering NodeConverter for char-string: Microsoft.SSMA.Framework.Access.SqlServerConverter.NodeConverters.CharacterStringLiteralConverter
    [Debug: Converter] [17704/14] [2019-10-22 10.41.56]: Registering NodeConverter for column: Microsoft.SSMA.Framework.Access.SqlServerConverter.NodeConverters.ColumnConverter
    [Debug: Converter] [17704/14] [2019-10-22 10.41.56]: Registering NodeConverter for add-level-operator: Microsoft.SSMA.Framework.Access.SqlServerConverter.NodeConverters.ConcatOperatorConverter
    [Debug: Converter] [17704/14] [2019-10-22 10.41.56]: Registering NodeConverter for foreign-key: Microsoft.SSMA.Framework.Access.SqlServerConverter.NodeConverters.ForeignKeyConverter
    [Debug: Converter] [17704/14] [2019-10-22 10.41.56]: Registering NodeConverter for full-expression: Microsoft.SSMA.Framework.Access.SqlServerConverter.NodeConverters.FullExpressionConverter
    [Debug: Converter] [17704/14] [2019-10-22 10.41.56]: Registering NodeConverter for full-or-condition: Microsoft.SSMA.Framework.Access.SqlServerConverter.NodeConverters.FullExpressionConverter
    [Debug: Converter] [17704/14] [2019-10-22 10.41.56]: Registering NodeConverter for full-and-condition: Microsoft.SSMA.Framework.Access.SqlServerConverter.NodeConverters.FullExpressionConverter
    [Debug: Converter] [17704/14] [2019-10-22 10.41.56]: Registering NodeConverter for full-unary-condition: Microsoft.SSMA.Framework.Access.SqlServerConverter.NodeConverters.FullExpressionConverter
    [Debug: Converter] [17704/14] [2019-10-22 10.41.56]: Registering NodeConverter for full-atomic-condition: Microsoft.SSMA.Framework.Access.SqlServerConverter.NodeConverters.FullExpressionConverter
    [Debug: Converter] [17704/14] [2019-10-22 10.41.56]: Registering NodeConverter for identifier: Microsoft.SSMA.Framework.Access.SqlServerConverter.NodeConverters.IdentifierConverter
    [Debug: Converter] [17704/14] [2019-10-22 10.41.56]: Registering NodeConverter for index: Microsoft.SSMA.Framework.Access.SqlServerConverter.NodeConverters.IndexConverter
    [Debug: Converter] [17704/14] [2019-10-22 10.41.56]: Registering NodeConverter for unique-constraint: Microsoft.SSMA.Framework.Access.SqlServerConverter.NodeConverters.IndexConverter
    [Debug: Converter] [17704/14] [2019-10-22 10.41.56]: Registering NodeConverter for div-level-operator: Microsoft.SSMA.Framework.Access.SqlServerConverter.NodeConverters.IntDivisionOperatorConverter
    [Debug: Converter] [17704/14] [2019-10-22 10.41.56]: Registering NodeConverter for primary-key: Microsoft.SSMA.Framework.Access.SqlServerConverter.NodeConverters.KeyConstraintConverter
    [Debug: Converter] [17704/14] [2019-10-22 10.41.56]: Registering NodeConverter for add-expression: Microsoft.SSMA.Framework.Access.SqlServerConverter.NodeConverters.ModExpressionConverter
    [Debug: Converter] [17704/14] [2019-10-22 10.41.56]: Registering NodeConverter for orderby-clause: Microsoft.SSMA.Framework.Access.SqlServerConverter.NodeConverters.OrderByConverter
    [Debug: Converter] [17704/14] [2019-10-22 10.41.56]: Registering NodeConverter for power-expression: Microsoft.SSMA.Framework.Access.SqlServerConverter.NodeConverters.PowerConverter
    [Debug: Converter] [17704/14] [2019-10-22 10.41.56]: Registering NodeConverter for table: Microsoft.SSMA.Framework.Access.SqlServerConverter.NodeConverters.TableConverter
    [Debug: Converter] [17704/14] [2019-10-22 10.41.56]: Registering NodeConverter for unparsed: Microsoft.SSMA.Framework.Access.SqlServerConverter.NodeConverters.UnparsedConverter
    [Debug: Converter] [17704/14] [2019-10-22 10.41.58]: Registering PostProcessors for manager  from assembly Microsoft.SSMA.Framework.Access.SqlServer, Version=8.4.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35...
    [Debug: Converter] [17704/14] [2019-10-22 10.41.58]: PostProcessor registered: Microsoft.SSMA.Framework.Access.SqlServerConverter.PostProcessors.DateTimeLiteralPostProcessor
    [Debug: Converter] [17704/14] [2019-10-22 10.41.58]: PostProcessor registered: Microsoft.SSMA.Framework.Access.SqlServerConverter.PostProcessors.DistinctrowPostProcessor
    [Debug: Converter] [17704/14] [2019-10-22 10.41.58]: PostProcessor registered: Microsoft.SSMA.Framework.Access.SqlServerConverter.PostProcessors.ErrorPropagationPostProcessor
    [Debug: Converter] [17704/14] [2019-10-22 10.41.58]: PostProcessor registered: Microsoft.SSMA.Framework.Access.SqlServerConverter.PostProcessors.LikePostProcessor
    [Debug: Converter] [17704/14] [2019-10-22 10.41.58]: PostProcessor registered: Microsoft.SSMA.Framework.Access.SqlServerConverter.PostProcessors.TableChildCommentPostProcessor
    [Debug: Converter] [17704/14] [2019-10-22 10.41.58]: PostProcessor registered: Microsoft.SSMA.Framework.Access.SqlServer.PostProcessors.AzureNotSupportedObjectsPostProcessor
    [Debug: Converter] [17704/14] [2019-10-22 10.41.58]: PostProcessor registered: Microsoft.SSMA.Framework.Access.SqlServerConverter.PostProcessors.ExpressionListItemPostProcessor
    [Debug: Converter] [17704/14] [2019-10-22 10.41.58]: PostProcessor registered: Microsoft.SSMA.Framework.Access.SqlServerConverter.PostProcessors.DuplicateColumnsNamesPostProcessor
    [Debug: Converter] [17704/14] [2019-10-22 10.41.58]: PostProcessor registered: Microsoft.SSMA.Framework.Access.SqlServerConverter.PostProcessors.SelectStatementWithOrderByPostProcessor
    [Debug: Converter] [17704/14] [2019-10-22 10.41.58]: PostProcessor registered: Microsoft.SSMA.Framework.Access.SqlServerConverter.PostProcessors.TopClausePostProcessor
    [Info: Gui] [17704/1] [2019-10-22 10.43.10]: ToolBar: press button - text:Reconnect to Azure SQL Database, name:reconTarget
    [Info: Collector] [17704/4] [2019-10-22 10.43.18]: Connection to Azure SQL Database established successfully.
    Connection string: Data Source=*******.database.windows.net;Initial Catalog=******;Integrated Security=False;User ID=*********;Pooling=False;Encrypt=True;TrustServerCertificate=False;Application Name="SSMA for Access"
    [Info: Gui] [17704/1] [2019-10-22 10.43.18]: Connection to Azure SQL Database established successfully.
    Connection string: Data Source=********.database.windows.net;Initial Catalog=***********;Integrated Security=False;User ID=*********;Pooling=False;Encrypt=True;TrustServerCertificate=False;Application Name="SSMA for Access"
    [Info: Gui] [17704/1] [2019-10-22 10.52.14]: MainMenu: press button - text:&Tools, name:Tools
    [Info: Gui] [17704/1] [2019-10-22 10.52.16]: MainMenu: press button - text:&Link Table, name:LinkTable
    [Info: Gui] [17704/1] [2019-10-22 10.52.29]: For your link tables to work with Azure SQL Database, you need to install a newer SNAC provider. For more information please click on this link (http://go.microsoft.com/fwlink/?LinkId=196940)
    [Error: Generic] [17704/1] [2019-10-22 10.52.29]: For your link tables to work with Azure SQL Database, you need to install a newer SNAC provider. For more information please click on this link (http://go.microsoft.com/fwlink/?LinkId=196940)
    [Info: Gui] [17704/1] [2019-10-22 10.52.34]: MainMenu: press button - text:&Tools, name:Tools
    [Info: Gui] [17704/1] [2019-10-22 10.52.36]: MainMenu: press button - text:&Global Settings, name:GlobalSettings
    [Debug: Collector] [17704/1] [2019-10-22 10.53.10]:
    SELECT 1

    Hope this helps in investigation.

    Many thanks

    Kjetil


    KjetilJansen

    Tuesday, October 22, 2019 11:42 AM