Solved

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

Posted on 2008-10-06
10
1,089 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

734 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