ACC: Storing SQL Database Login IDs and Passwords Locally

This article was previously published under Q101084
This article has been archived. It is offered "as is" and will no longer be updated.
Summary
Advanced: Requires expert coding, interoperability, and multiuser skills.

Storing SQL database login IDs and passwords locally requires that thedatabase administrator create a unique table on the server.
More information
In Microsoft Access, when you link (or attach in Microsoft Access 2.0 orearlier) a SQL database table, you can choose whether you want MicrosoftAccess to store your login ID and password locally. If you do not,Microsoft Access prompts you for your login ID and password each time thatyou connect to the SQL database containing the table.

If you want Microsoft Access to store the connection information in yourMicrosoft Access database so that you do not have to type it each time,you can click to select the Save Login ID And Password Locally check box inthe Attach Tables dialog box when you link the SQL database table.

For Microsoft SQL Server, Sybase SQL Server, and ORACLE Server databases,your SQL database administrator can choose to disable this feature,requiring all users to enter their login IDs and passwords each time theyconnect to a SQL database.

To disable the ability to store login IDs and passwords locally, yourSQL database administrator must create a table called MSysConf in theSQL database. When a user connects to the SQL database, MicrosoftAccess looks for this table in the database and, if it finds it,queries the table. If the values in the table correctly specify thatlocal storing of login IDs and passwords should be disabled, MicrosoftAccess does so, regardless of whether the Save Login ID And PasswordLocally check box is selected. If the table is not present or does notspecify disabling of the feature, users can store login IDs andpasswords locally.

The SQL database table MSysConf should have the following structure.
   Column name   Data type                                 Allows Null?   --------------------------------------------------------------------   Config        A data type that corresponds to a         No                 2-byte integer   chValue       VARCHAR(255)                              Yes   nValue        A data type that corresponds to a                 4-byte integer                            Yes   Comment       VARCHAR(255)                              Yes				

If the data source you are working with is case-sensitive, use the tableand column names exactly as shown. All users must have permission to usethe SELECT statement on this table and only the system administrator canhave permission to use the DELETE statement on this table.

In ORACLE, there are a few things that are different. For instance, theword "Comment" is reserved in ORACLE, so you will need to change it to"Comments." The other change relates to a difference in data types. Here ishow the table would appear in an ORACLE database:
   CONFIG          NUM(4,0)         NOT NULL   CHVALUE         VARCHAR(255)   NVALUE          NUM(5,0)*   COMMENTS        VARCHAR(255)				

Nvalue can have a precision of up to Num(9,0).

To disable password and login ID storage, the table should have only onerow as follows:
   Column name     Value    Explanation   --------------------------------------------------------------------   Config          101      This is the only valid value for Microsoft                            Access version 1.1.   chValue         NULL     This is reserved for future use.   nValue          0 or 1   Use 0 to prevent the password and login ID                            from being stored; use 1 to permit                            password and login ID storage as in                            version 1.0. The default is 1.   Comment                  Allow storage of passwords and login IDs in                            Microsoft Access.				
Properties

Article ID: 101084 - Last Review: 10/26/2013 13:58:00 - Revision: 3.0

  • Microsoft Access 1.0 Standard Edition
  • Microsoft Access 1.1 Standard Edition
  • Microsoft Access 2.0 Standard Edition
  • Microsoft Access 97 Standard Edition
  • kbnosurvey kbarchive kbhowto kbusage KB101084
Feedback