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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.


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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
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.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

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