Wednesday, February 8, 2012

Securing MS Linked Tables Connection Strings During Migration

Han posted Securing MS Linked Tables Connection Strings During Migration to the SQL Server Migration Assistant Team Blog on 2/8/2012:

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.