Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1116
  • Last Modified:

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

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
jsprenk55
Asked:
jsprenk55
  • 5
  • 4
1 Solution
 
TextReportCommented:
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
 
jsprenk55Author Commented:
Thank you for your response.  I checked the TryJetAth setting and it was already set to 1.  Any other ideas would be appreciated.  
0
 
TextReportCommented:
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
jsprenk55Author Commented:
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
 
jsprenk55Author Commented:
Can anybody confirm if this functionality has been stripped from Access 2007?
0
 
jsprenk55Author Commented:
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
 
TextReportCommented:
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
 
jsprenk55Author Commented:
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
 
TextReportCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now