Solved

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

Posted on 2008-10-06
10
1,083 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
  • 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
 

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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

759 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now