I am using MS Access 2007 to generate reports/queries on data stored on a networked machine running MS Sql Server. There are two accounts (among others) registered on the sql server that users can use to connect to the database. One allows all network administrators to connect using a Trusted Connection with NT Authentication, the other uses the SQL Authentication with a user name and password.
Physically in front of the sql server, performing queries on a particular table under both accounts yield the same results; however, when I try to link this particular table into MS Access, I lose the [cost] field from the table using SQL Authentication, while it has no problems when I link using the Trusted Connection with NT Authentication. Any help or insight to how to fix this problem would be greatly appreciated.
As an aside, I've thought setting up another account that lets all users connect using a Trusted Connection with NT Authentication, unfortunately there are a lot of individual privileges set up for the current user, and without an easy way to export and import those privileges into a new user in Sql Server, it's just not worth it, especially when it's probably just some easy option that I'm missing somewhere.
Access 2007: Linking tables via ODBC will lose some fields unless linking as trusted