Microsoft KB Archive/209502

= How to store SQL database login IDs and passwords locally =

Article ID: 209502

Article Last Modified on 8/6/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition
 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q209502





For a Microsoft Access 97 version of this article, see 101084.

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access database (.mdb).



SUMMARY
Storing SQL database login IDs and passwords locally requires that the database administrator create a unique table on the server.



MORE INFORMATION
In Microsoft Access, when you link a SQL database table, you can choose whether you want Microsoft Access to store your login ID and password locally. If you do not, Microsoft Access prompts you for your login ID and password each time that you connect to the SQL database containing the table.

If you want Microsoft Access to store the connection information in your Microsoft Access database so that you do not have to type it each time, you can click to select the Save Password check box in the Link 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 they connect to a SQL database.

To disable the ability to store login IDs and passwords locally, your SQL database administrator must create a table called MSysConf in the SQL database. When a user connects to the SQL database, Microsoft Access looks for this table in the database and, if it finds it, queries the table. If the values in the table correctly specify that local storing of login IDs and passwords should be disabled, Microsoft Access does so, regardless of whether the Save Login ID And Password Locally check box is selected. If the table is not present or does not specify disabling of the feature, users can store login IDs and passwords 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, for example SMALLINT

chValue      VARCHAR(255)                              Yes

nValue       A data type that corresponds to a                 4-byte integer, for example INT           Yes

Comment      VARCHAR(255)                              Yes If the data source you are working with is case-sensitive, use the table and column names exactly as shown. All users must have permission to use the SELECT statement on this table and only the system administrator can have permission to use the DELETE statement on this table.

In ORACLE, there are a few things that are different. For instance, the word "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 is how 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 one row as follows:   Column name     Value    Explanation Config         101      This value indicates that this record determines password properties.

chValue        NULL     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.

Comment                 Enter your own comments here. NOTE: If you change the nValue value, you must restart Access before the change will actually take place.

