Bypass SQL Login

Posted on 2012-04-04
Last Modified: 2012-04-05
I have an Access database, all the tables are links to a SQL 2000 database. There are permissions set up for this SQL database. As long as the users are set up in the SQL database they can open the reports or forms in Access without having to login to SQL.
My problem arises from a new table I created. I created a table in Access, I then exported it to my SQL database and created a link in Access to this new SQL table. I used this new table in some of the existing Queries. Now whenever I open anything in Access that is using this new table, the SQL login window appears. I do not want the SQL login window to appear every time a report is run. There is no option to remember the password on the SQL login window, the "OPTION" button is greyed out.

I think this is a permission issue in SQL, not an Access issue, but I am not sure.

Why is this one table requiring the user to login to SQL every time it is run? What can I do to stop the SQL login window from appearing every time the query is run?
Question by:CJSilver
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
  • 3
  • 3
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37808874
Did you save the password when you created the linked table?

Expert Comment

by:Patrick Tallarico
ID: 37808952
If you re-link the table, you should have a save password option in the window where you choose the table(s) from the listing from the datasource.  If you do this, Access will save the password, but it should warn you that it will store it in an unencrypted form.

Author Comment

ID: 37812267
I do not see an option to save the password. This is Access 2007. When I go to the linked table manager, I select the table and click okay, and it tells me all tables have been successfully refreshed, it does not ask for a password. There is no place I can tell it to remember the password.
Linked Table Manager In fact, I can open the table in Access and it does not ask for a password, only when I run the query does it ask for the password.  But in the SQL password window there is no place for me to tell it to remember the password.
SQL Login
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users


Expert Comment

by:Patrick Tallarico
ID: 37812412
Try re adding the table from scratch. By re adding it from scratch, you should have a save password option.

Author Comment

ID: 37812631
I tried, it does not give me the option to save the password. It gives the exact same login window as the one I showed in the last post.

I am going to call this problem table, TABLE_1, just to make it clearer.

This is getting more confusing. As I mentioned earlier, I can open TABLE_1 in Access and it does NOT ask for a password, it is only when I run a query that it asks for the password.
This query is made up of many different tables, ALL of the tables are from the same SQL database. If I take out TABLE_1 from this query, the  query no longer asks for a password. If I put TABLE_1 back, it asks for a password. What makes this more confusing is that if I create a new query that only contains TABLE_1, I can run this query and it does NOT ask for a password. It only asks for the password when the query with multiple tables is run, but as I mentioned above, it only asks for the password if TABLE_1 is in the query.

I am not sure if this is an Access issue or a SQL issue.
This SQL database is the database for my manufacturing and accounting software, it has hundreds of tables. If I add any of these tables, It asks me to login when I add the table, I do not have to save the password, and from that point on, I never have to log in to it again. I assume it is because the users are set up with permissions in the SQL database so as long as they have permission they do not have to log in every time a report is run.

But this table is different, it is not one that existed when the users were set up in SQL, it is a table that I created and exported from Access to SQL. Do I need to do something in SQL so this table behaves like the tables that were already in the SQL database?

Accepted Solution

Patrick Tallarico earned 500 total points
ID: 37812715
I apologize, i don't think i was clear when i said to re add the table.  I meant that you should re link the table from the datasource.  Remove the table from your access project and then re add the table through the import wizard, making sure to link to the table rather than a straight import. This is the process where you should have the option to save the password.  The check box should appear to the right of the table listing from which you can select the table s.

Author Closing Comment

ID: 37813713
I understood what you meant, I had been looking for the save password check box in the window where the password is typed, I did not notice the "save password" check box in the link table window.

Thank you, you have solved my problem!

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

728 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