Duplicate logon to Access and SQL Server

[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.

 

[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.

 

[Alex Dybenko]

Hi,
if you are using linked tables or pass-through queries - then all connection
settings in connection strings

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[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 <--

 

[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.

 

[Rick Brandt]

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.

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.

 

[david]

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)

 

[david]

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)

 

[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

 

[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.