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?