Solved

Microsoft query using ODBC asking for login credentials

Posted on 2008-10-09
3
729 Views
Last Modified: 2013-11-17
I am using Excel and retrieving external data from a SQL 2005 db via Microsoft qery using an ODBC data source.  The ODBC connection is a system DSN set to use SQL authentication.  I created the workbook and query as a domain admin.  I checked the "save password" checkbox under the Query definition section of the external data range properties.  Obviously, this all works fine as domain admin.

I get differing results as 2 test users.  One user it works fine for: no authentication challenge.  The other user it says "logon failed for user X", then promts for a password.  The SQL Server Login window that comes up has "Use Trusted Connection" checked and the Login ID and Password boxes are greyed out, but the Login ID is the correct one that I setup for the ODBC connection.

Each of these users is part of a different AD security group (i.e. user1 belongs to group1 and user2 belongs to group2).  Initially, group1 had read and write access to the database and group2 only had read.  I changed group2 to have the same rights as group1, but I still get prompted when opening the file as user2.  (I would like this to work with only read access)

Not sure what to look at next.  Any help?
Thanks,
Tim
0
Comment
Question by:timnorvel
[X]
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
  • 2
3 Comments
 

Author Comment

by:timnorvel
ID: 22681300
ok, this is obviously a rights issue.  I added the non-working user2 to group1 and it worked.  But, as I stated, the rights on the database for group1 and group2 are the same.  I also checked the rights on Excel file itself, and it is the same for both of them.  Where else can I check?  Where else do user rights come into play?

thanks
0
 
LVL 10

Expert Comment

by:pkhari
ID: 22713303
Hi

Some thoughts to try out

Guess you should disable the Trusted connection model, and go by passing the login name and password. Following links might give you a heads-up on this:

http://www.mrexcel.com/forum/showthread.php?t=343938

Hope this helps.

Cheers
P.K.
0
 

Accepted Solution

by:
timnorvel earned 0 total points
ID: 22723306
God, I am so stupid sometimes.  On the SQL server i was looking under the DB at the security section and saw that both groups had the same rights there.  But if I looked at the securtiy section up a level... for the sql server, not the specific db, the group 1 was not there.

It turns out that if you delete a user/group from that "upper level" security section it does not update the individual db's that user/group had rights to (and there's a popup window telling you as much, which I'm sure I quickly clicked through).

Anyway, I added it back in and Bob's your uncle.  Thanks.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

632 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