SQL server Error: 18456 when viewing SQL table in Access

kevin1983
kevin1983 used Ask the Experts™
on
Hi,

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:

Connection failed:
SQLState: ‘28000’
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2014

Commented:
First thing I'd try is recreating the linked tables, and building a new DSN when doing so. Make sure to select the correct options for your particular environment (just read each prompt screen very carefully and take the time to research each option if you're not sure what they do).

Your server must be setup to use a mixed authentication method, if you're going to use both Windows Authentication and Trusted Users (i.e. Windows) mode.

Author

Commented:
ok, The server appears to be setup for Windows authentication mode

Author

Commented:
OK tried creating a new DSN, and on the screen that says create a new data source to SQL server id like to use the option that says "With Windows NT authentication using the network login id" I click next to this but same issue for the users. maybe something needs changing on the SQL server?

Author

Commented:
The users were not setup correctly on the SQL server, by ensuring all relevant active directory users had the correct permissions to read the relevant SQL database and setup the default database they are able to access  we wished them to access seemed to fix the issue.

Now users can view linked SQL tables without any error/warning messages.
Commented:
Question PAQ'd and stored in the solution database.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial