When using Microsoft access 2003 to view any linked SQL tables we get a Microsoft SQL server Login error message for a regular network user which says:
SQL server Error: 18456
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user ‘DOMAIN\Username’.
And then clicking ok and “SQL server login” box pops up with the “Use trusted connection” check box ticked and the name of the SQL server listed in the drop down box. Clicking ok to this dialog box brings ups the same error message.
However domain admin’s can view linked SQL table’s fine without being prompted to enter any credentials. The SQL tables have been linked via ODBC and we would like users to be able to view the tables using Windows authentication.
I’m guessing the issue is related to windows authentication security on the SQL server but not sure how to resolve. The Server is running Windows server 2008 and SQL server 2005. Any help on how to resolve would be appreciated.