I'm running an Access 2000 Database on Windows 2000. The Access Database links to SQL Server 2000 tables. The error occurs when the users login with their windows login (my sql server is mixed authentication) and open the database to open the tables or forms. The connection will fail with the following:
SQL Server Error 18456
[Microsoft][ODBC SQLServer Driver][SQL Server]Login failed for domain/'user"
I used in the Data Source set up and tested the connection and I have as WindowsNt authentication using the network login ID from (Access) All these users are in added to sql server as users for these database using windows authentication.
The users belong to the group in sql server "users" (sql server computer managent), when i add the "administrator" group to their login then they can login without anyproblems but for obvious security reasons i have to take them out of the administrator's group.
things i already checked:
[*]sql server is mixed authentication[*]odbc conection to sql server database is windows authentication (from access i used the link table option to sql server and created a conection to sql server) [*]user has read and write access to the sql server database and select to the tables. [*]user is part of the user group in sql server [*]the user is under the domain users group in active directory. [*]no deny rights in sql server for this user. [*]My sql driver version is 2000.85.1117 and when i test it says odbc driver version 03.85.1117.
[*]my sql server 2000 sp is mdac version 2.80.1022 and the user's desktop is version mdac 2.8 sp1 on windows XPS2. she is a member of the user's group with her windows login domain\user , i added her to the database wiht db_reader and db_writer rights and i also add her as only user (without the domain) . my sql server is with mixed authentication. in active directory she is part of the built in user's group.....
so i logged in as administrator to his computer and deleted the odbc dsn and created a new one (under file dsns) , made sure it Nt authentication and set the default database tested the conection and it works (run as administrator). i relinked the tables in the database with this new dsn and it works fine. i logged off and logged in with the users credentials. when i try to use that table then i get a message saying 'ODBC-conection to DATA MB conection failed'.