Solved

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

Posted on 2008-10-06
10
1,086 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

776 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