Securing MS Linked Tables Connection Strings During Migration


Microsoft Access stores all the connection strings for the respective linked tables in a system table called MSysObjects.  As seen below, the connection strings contain clear-text used id and password.  With the release for SSMA for Access 5.2, when creating link tables during migration, users will now have the option to not store the user id and password for the linked tables.

A new setting for linked tables can be found under the Project Settings menu.    By default, the Store user credentials setting is set to false, thus user id and password will not be persisted in the connection string of a linked table.  Switching the setting to true would provide the option to store the user id and password in the connection strings during the creation of linked tables.

It is important to note that after securing the connection string, MS Access users will have to enter the required user id and password whenever the linked tables are referenced in the MS Access Database application.  Below shows the prompt presented by MS Access.

 

 

 

Comments (1)

  1. Ankur Jain says:

    Hi Team,

    I am using SSMA to Access for a particular migration project to migrate all possible tables, views, linked tables, etc. I am using customized ConversionAndDataMigrationSample.xml, VariableValueFileSample.xml, ServersConnectionFileSample.xml files to provide all source, destination and variables to SSMA based on the requirement of my project. I executing SSMAforAccess.exe in cmd by passing the location of these xml files containing source, destination, variables details required to complete the migration. Migration works perfectly however i want to store a particular service account credentials in connection string to link my Access tables with SQL tables, this can be achieved by marking Linked Table property as “True” in Default project settings of SSMA GUI. It does work and stores the service account credentials in connection string when i directly create a project using SSMA GUI however whenever i try to achieve the same using xml files execution method, the connecting string in the linked tables generates without any service account credentials in it and which is an issue as per the requirements of the project. I am using the below node in ConversionAndDataMigration.xml schema XML file to map Linked Table property as “True” in Default project settings in XML.

    Also tried values like True, 1, yes, true, etc.

    I could be wrong here in choosing this node to store required user credentials in connection string. Could you please direct me to correct node or method to store service account credentials in connection string while migrating access data to SQL using CMD & XML files.

    Your prompt response would be really appreciable.

    Thanks in advance.

    Ankur Jain