Duplicate logon to Access and SQL Server

M

Marina

We have an Access 2003 front-end with user group security connecting to a SQL
2005 back-end.

As far as I know, I've set up all user accounts (the server use SQL
Authentication) to be the same (both on Access and SQL), yet some users have
to logon only to Access, but others are requested to logon both to Access and
SQL.

Does anyone know where you configure whether Access 'passes on' logon
details to SQL? Or how does this work? What am I mising?

Thanks very much.
 
G

Guest

It's a very old feature, and I'd always assumed that if I
tried it, it wouldn't work anymore, like many other old
Access features. Are all the users on the same version
of Windows? Are they using the same version of Access?
All have the same version of the SQL Server ODBC driver
installed? All connected to the same SQL Server? Does
the server also have integrated log in enabled?

I think that there was a registry entry that was supposed
to control if SQL Server login was attempted using the Access
login. Has anyone been fooling with that? And the
configuration table?

There is an ODBC group that would be a better place to
ask, but I don't know that anyone thee is using SQL
Server login either.

(david)
 
R

Ryan

If you are using windows authentication then it is not the log on to access
that matters, it is the user name and password into windows that needs to
match SQL Server.
 
S

Stefan Hoffmann

hi,
As far as I know, I've set up all user accounts (the server use SQL
Authentication) to be the same (both on Access and SQL), yet some users have
to logon only to Access, but others are requested to logon both to Access and
SQL.
Afaik it is not possible to use the Access security system to logon to a
SQL Server using SQL Server Authentication and passing this credentials.

If you say some of your users have only to logon once, then they logon
to the Access security system. The SQL Server is using Windows
Authentication for these uses.


mfG
--> stefan <--
 
M

Marina

Thanks guys, I think I've found the problem. The users experiencing problems
seem to have recently upgraded to Access 2007, but the others are still using
2003. The two versions seem to be handling logons to the SQL server from an
Access front-end slightly differently.

My pass-through queries are still working correctly, but Access 2007 users
need to logon to the database separately. In 2003, you could logon once as
long as the username and password in Access was the same as for the SQL
Server.
 
D

david

Rick Brandt said:
You are mistaken. SQL Server can use its own login credentials or the
credentials from Windows can be passed automatically to SQL Server. There
is no way that the Access credentials can be automatically utilized by SQL
Server whether they are identical or not. Something else is going on.


You are mistaken. Access credential pass-through has been a feature
for many years. It just doesn't get used very often. If you can't find it
in the current Access help file, have a look in one of the older versions.

(david)
 
D

david

Stefan Hoffmann said:
hi,

Afaik it is not possible to use the Access security system to logon to a
SQL Server using SQL Server Authentication and passing this credentials.

If you say some of your users have only to logon once, then they logon to
the Access security system. The SQL Server is using Windows Authentication
for these uses.


mfG
--> stefan <--


Unfortunately, she posted these messages to multiple groups, so the full
discussion is not here. However, Access credential pass-through is (or
was) a standard part of Access. It comes as no surprise to find that it
has been disabled in Vista.

BTW, since the Access credential login is attempted BEFORE the Windows
credential login, people sometimes have the opposite problem, that users are
logging into their SQL Server as "Admin" instead of using the correct
Windows
Login.

(david)
 
R

Rick Brandt

david said:
You are mistaken. Access credential pass-through has been a feature
for many years. It just doesn't get used very often. If you can't
find it in the current Access help file, have a look in one of the
older versions.

I was unable to find anything about "credential pass-through" or
"credentials pass-through" in the help files for 97, 2000, 2002, or 2003. I
also searched the MS Knowledge Base and MSDN and still nothing. Is this an
ADP thing perhaps? Can you provide the actual name of the help topic?

Thanks;
 
R

Rick Brandt

david said:
"Configuring the Microsoft Jet Database Engine for ODBC Access"

Cool. The article says it applies only to Access 2003 but the registry
setting is for Jet 4.0. Do you know if it works in the older versions that
use Jet 4?

Hard to believe they don't make this available in the interface instead of
only as a registry tweak.

I'm not sure why one would ever use it for SQL Server rather than just using
integrated security, but it could be handy for other databases. The article
doesn't say if this applies to all server databases, but it makes no
statement that it is for SQL Server only either.

Just now noticed that this is ON by default? That makes the OP's problem
more curious then as it would mean that they changed the default back to OFF
for Access 2007. Perhaps it was considered a security risk?
 
M

Marina

The database is sitting on a server belonging to someone else and he chose
SQL Server Authentication rather than Windows Authentication. Seems like a
strange decision to me (but I'm still quite grateful for the space on his
cool server).

Using a trusted connection to connect to the server definitely does not work.

The Access credential pass-through feature has got to be the explanation
then, thanks, guys. Since it no longer works with Access 2007, I think the
best would be to disable Access security once everybody has upgraded to 2007
and to then only use SQL Server Authentication - assuming I don't get a
chance to try and convince the DBA to change to Windows Authentication.

Cheers :)

P.S. Did I post to multiple groups? What other groups and how? I surely
didn't do so intentionally?
 
G

Guest

P.S. Did I post to multiple groups? What other groups and how? I
surely didn't do so intentionally?

No, that was my mistake. My problem is that the MS servers don't
always give me all the messages, but I'm old enough to remember
when it all worked correctly, so I thoughtlessly blamed you.

I'm sorry.

(david)
 
G

Guest

The setting is in Jet 3: from the look of it, it must date back
to Jet 2, since it must pre-date Windows Authentication,
and I don't think Jet 1 did ODBC.

Useful features often get broken, not turned off, and instead we
get broken new features, but I put this in a different category.

I think few people will miss this. Would you put your SQL Server
passwords in a MDW?

(david)
 
G

Guest

Because of the way it works, it will apply for any ODBC
database. It always attempts the first connection using the
Jet login first. (As I mentioned, this has caused problems for
people with an "Admin" user) Another question is if it would
work for an IISAM.login. If: it always attempts an MDB
login using the Jet login, and always attempts an ODBC
login using the Jet login, does it always attempt an IISAM
login using the Jet login?

And another question is about connection time-outs.
You know how if your ODBC connection times-out, it
won't re-connect? I wounder if that would be fixed if you
disabled the login attempt using Jet credentials?

(david)
 
M

Marina

I'm not sure about older versions of Access (older than 2003), but it
definitely works in 2003 and it definitely doesn't work in 2007.

I've checked the registry setting - TryJetAuth is enabled, but the feature
isn't working..

(All my users are on Windows XP, it's only their versions of MS Office that
differs.)
 
J

John Sprenkle

Marin,

I am currently attempting to run an Access 2003 database under Access 2007 and I am encountering the same issue where the user is receiving a duplicate login for SQL. Did you find a resolution to this? Is there a way around this? Any input would be greatly appreciated
 
J

John Sprenkle

To correct this problem, you need to navigate to the following registry setting:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\ODBC\TryJetAuth


Set the TryJetAuth registry item to 1.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top