Access2003 and conection to MS SQL

  • Thread starter Thread starter sp
  • Start date Start date
S

sp

Access2003 and conection to MS SQL

I configured Access 2003 database with linked tables to MS SQL that uses
SQL authorization. I did it via ODBC. I would like that every user have
to enter his login after startimg MS Access database and password and
that user should be used to linking tables.

How to do it?
 
sp said:
Access2003 and conection to MS SQL

I configured Access 2003 database with linked tables to MS SQL that uses
SQL authorization. I did it via ODBC. I would like that every user have to
enter his login after startimg MS Access database and password and that
user should be used to linking tables.

How to do it?
Click to expand...

If you set up the DSN to use SQL Server authentication and not Trusted
Connections and DON'T save your password when you initially create the links
then that should be the standard behavior.
 
If you set up the DSN to use SQL Server authentication and not Trusted
Connections and DON'T save your password when you initially create the links
then that should be the standard behavior.
Click to expand...


Unforunatelly it doesn't work, the password is remembered... I checked
it many times, but always password is remembered. During the tests I
reset my system to be sure that!
 
Hi kofa,

Thanks for using MSDN Managed Newsgroup Support.

Your SQL database administrator can choose to disable the access to store
the SQL database login IDs and passwords locally, 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.


Please try to follow this KB article to setup the table:

How to store SQL database login IDs and passwords locally
http://support.microsoft.com/?id=209502

Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
I created MSysConf table in database that I connect from MS ACCESS, the
creation script is:

CREATE TABLE [dbo].[MSysConf] (
[Config] [smallint] NOT NULL ,
[chValue] [varchar] (255) COLLATE Polish_CI_AS NULL ,
[nValue] [int] NULL ,
[Comment] [varchar] (255) COLLATE Polish_CI_AS NULL
) ON [PRIMARY]
GO


Data:

SELECT Config, chValue, nValue FROM MSysConf


Config chValue nValue
101 NULL 0

bu.. it still doesn't work Access never asks about SQL login and
Password, I entered SQL login and password only once...

Maybe MSysConf table should be created in master database?
 
Hi Kofa,

Would you please check the DSN configuration for your SQL Server coonection?

I assume you are using the SQL Server Provider.

When in the step you specify the authenticity of the login, please do not
check the "Connect to SQL Server to obtain default settings for the
addtional configuration options" check box.

Since linked table using the DSN to connect to the SQL Server, if you check
this option, you will use the login id here you provide to connect to the
SQL Server.

Please have a check and let me know the result.

Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Wei Lu napisa³(a):
Hi Kofa,

Would you please check the DSN configuration for your SQL Server coonection?

I assume you are using the SQL Server Provider.

When in the step you specify the authenticity of the login, please do not
check the "Connect to SQL Server to obtain default settings for the
addtional configuration options" check box.
Click to expand...


I am useing driver version 200.86.1830.00 file SQLSRV32.DLL but i can
not uncheck "Connect to SQL Server to obtain default settings for the
addtional configuration options" check box it is GREY and checked!!!

What to do?
 
Hi Kofa,

Would you please try to create a System DSN or a User DSN and have a try?

Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Wei Lu napisa³(a):
Hi Kofa,

Would you please try to create a System DSN or a User DSN and have a try?
Click to expand...

OK! It is working (do not remeber password) when I use System DSN or a
User DSN, but it isn't working when I use File DSN - but solution is
good for me! Thank you!
 
Hi Kofa,

Thanks for the update.

Glad to hear the issue was resolved.

If you have any questions or concerns, please feel free to let us know.

Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------
Date: Thu, 27 Apr 2006 09:44:18 +0200
From: sp <[email protected]>
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
X-Accept-Language: pl, en-us, en
MIME-Version: 1.0
Subject: Re: Access2003 and conection to MS SQL
References: <#[email protected]>
Click to expand...
<[email protected]>
<ObJWhs#[email protected]>
<[email protected]>
<#[email protected]>
<[email protected]>
 
dont use MDB for anything; especially for SQL Server stuff.

throw away all MDB applications you've got and fire anyone at your
company that uses MDB.

ADP is the best thing since sliced bread.

goodluck
 
Back
Top