🚀 go-pugleaf

We had a similar problem a number of years back with early versions of
Access talking to Sybase 4.9.  A workaround was to create the MSysConf table
in the master database with the '101' entry.


Ross MacDougall
rmacdougal@shl.com

Here is the workaround (from the Microsoft TechNet CD)

====
PSS ID Number: Q101084
Article last modified on 10-04-1994
PSS database name: ACCESS
1.00 1.10 2.00
WINDOWS
 --------------------------------------------------------------------
The information in this article applies to:
 - Microsoft Access versions 1.0, 1.1, and 2.0
 ---------------------------------------------------------------------
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 version 1.0, if you attach 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 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 you do not have to type it each time,
you can select the Save Login ID and Password Locally check box in the
Attach Tables dialog box when you attach the SQL database table.
This feature is also present in Microsoft Access version 1.1. For
Microsoft SQL Server, Sybase SQL Server, and ORACLE Server databases,
however, your SQL database administrator can now 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
   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
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 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.
Additional reference words: 1.00 1.10 2.00
KBCategory: kbusage
KBSubcategory: ObcOthr
=============================================================================
Copyright Microsoft Corporation 1994.


====

 ----------
From: MSSQL-L
To: Multiple recipients of list MSSQL-L
Subject: MS SQL Server/ODBC/Trumpet Winsock
Date: Wednesday, May 03, 1995 10:57AM

I am the SQL Server DBA at the University of Nebraska.  We have a difficult
situation right now implementing SQL Server access to Windows clients who
use Trumpet Winsock and tcp/ip for access.

Background:
We upgraded to NTS v. 3.5 and SQL Server v. 4.21a and ODBC v. 2.0.
We have written a customized application in VB using ODBC connectivity.
This application is successful using NetBIOS, Novell IPX and ODI, and
MS TCP/IP winsock.

Problem:
Many, many of our end-users use Trumpet winsock.  The UNO campuses has a
site
license to distribute this software to all departments.  We have no success
using this software with our VB/ODBC application.

Trace Results:
We traced the problem using an Analyzer product and found the following
situation.

1)  Using Winsock -- ODBC-client apparently sends a request to the DB:
SELECT nValue FROM MSysConf WHERE Config = 101.

2)  The DB replies back:
Invalid object name 'MSysConf'..
OVER AND OVER AGAIN.....

Trace Results using MS TCP/IP:
1)  Using MS TCPIP -- ODBC-client sends a request to the DB:
SELECT nValue FROM MsysConf WHERE Config = 101.

2) The DB responds "Invalid object name 'MSysConf'"

3) The ODBC-client responds "sp_server_info 1"

4) Processing continues as normal.

Does anyone understand this?  Are there settings that can be changed on the
Trumpet Winsock software to fix this?

MS clearly indicates that they ONLY support MS TCP/IP, however, the beauty
of using MS SQL Server at our University is that we are able to simply
using the client's existing network protocol and not a) change and b) pay
for
additional network software.

We could 'fix' this problem by migrating all users on each of the four
campuses to Microsoft WFWG and MS TCP/IP, but, as you might imagine -- that
is not realistic.  Besides that, the end-user community LOVE Trumpet Winsock
and have experienced no problem with the product using other TCP/IP access
methods.

Unresolved, this issue could significantly impair our efforts to improve
access to U-wide information at our University using low-cost solutions.
If anyone is able to assist us with this, it would most certainly be
greatly appreciated.

Thanks.

Amy Stephen (402) 472-7675
SQL Server DBA
University of Nebraska -- Computing Services Network