Solved

Access 2007 Front end to SQL Database W/ Linked tables - getting prompt for SQL login - Why?

Posted on 2008-10-06
10
1,091 Views
Last Modified: 2013-11-29
Current setup:

I have an Access 2003 database utilizing Access security (i.e. it utilizes an Access workgroup file .mdw) .  The database contains many linked tables to a SQL 2000 database via and ODBC datasource.  In the SQL database, every access user is created with the same user name and password as they have in Access.  When a user logs into Access they simply enter their Access user name and password and this carries through to the links to SQL.  The user is not prompted for a second login to connect to SQL.  

New Setup:

I must now run the database under Access 2007.  According to Microsofts documentation, I cannot convert the database to 2007 because it utilizes Access security and a workgroup file.  The database seems to work fine under Access 2007.  However, every time I log into the system, I am now being prompted for a second login for SQL.  I must type the same user name and password a second time and then the system runs fine.  

Can somebody tell me why I am now getting this second SQL login.  Is there a way to eliminate this?
0
Comment
Question by:jsprenk55
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 28

Accepted Solution

by:
TextReport earned 300 total points
ID: 22650093
Firstly are you able to login to the Access security in 2007 so you have the username and password working for the access security.

If so then as it looks like the ODBC connection has the same setting in 2007 as the previous versions the problem is probably the TryJetAth setting in the registry not set to 1. Check out

http://office.microsoft.com/en-us/access/HP010321641033.aspx
http://msdn.microsoft.com/en-us/library/bb177636.aspx for more details.

Cheers, Andrew
0
 

Author Comment

by:jsprenk55
ID: 22650348
Thank you for your response.  I checked the TryJetAth setting and it was already set to 1.  Any other ideas would be appreciated.  
0
 
LVL 28

Expert Comment

by:TextReport
ID: 22650475
What is the Access user name, you can check this using CurrentUser(). Is this the one you are expecting to use on SQL Server?
Cheers, Andrew
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:jsprenk55
ID: 22650628
Thank you again for continued help. Yes I verified with CurrentUser() and the correct user is being captured in Access 2007. I am fearful that this ability to pass-through the login to SQL may have been dicontinued with Access 2007 based on a blog that I saw at another web site.

http://www.eggheadcafe.com/software/aspnet/32895552/duplicate-logon-to-access.aspx
0
 

Author Comment

by:jsprenk55
ID: 22652024
Can anybody confirm if this functionality has been stripped from Access 2007?
0
 

Author Comment

by:jsprenk55
ID: 22652319
Thank you Andrew.  You put me on the right track.  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.
 
0
 
LVL 28

Expert Comment

by:TextReport
ID: 22652519
Hi, I beleive the work I put on this steering jsprenk55 to the TryJetAuth setting of 1 as stated in post 22650093 means that this has been resolved by me. Access 2007 has a different registry key to the previous versions but I provided the correct answer.

Cheers, Andrew
0
 

Author Comment

by:jsprenk55
ID: 22652767
Andrew,
It was my intent to assign you the points.  If this is not the case then please direct me to what I need to do.  I explicitly awarded you the points to you when I closed the thread.  There may be an issue with the web site.  
Please advise.
 
0
 
LVL 28

Expert Comment

by:TextReport
ID: 22652963
Not sure what happened, I got notice that your were closing the question, I think rather than accepting an answer. A moderator may be able to explain better.
Cheers, Andrew
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question