Clicky

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.

asked 10/17/2010 09:20

kevin1983's gravatar image

kevin1983 ♦♦


5 Answers:

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.

link

answered

LSMConsulting's gravatar image

LSMConsulting

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

link

answered 2010-10-18 at 05:34:24

kevin1983's gravatar image

kevin1983

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?

link

answered 2010-10-18 at 06:23:41

kevin1983's gravatar image

kevin1983

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.

link

answered 2010-10-18 at 06:28:33

kevin1983's gravatar image

kevin1983

Question PAQ'd and stored in the solution database.

link

answered 2010-11-11 at 03:30:12

ee_auto's gravatar image

ee_auto

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

×14
×132
×1

Asked: 10/17/2010 09:20

Seen: 165 times

Last updated: 12/12/2011 05:16