We have a Microsoft Access 2010 database (ACCDB).
This ACCDB file is just a front end to a Microsoft SQL Server 2008 database.
Authentication to the database is accomplished by Windows integrated authentication.
I have created a System DSN on the workstation. In the ODBC connection window, I select APP1 for the server name and am able to pick the database (for our purposes we will call it test) from the drop down list and when I am done choosing options I test the connection (the test works).
I have gone into SQL and confirmed that the user has permissions to login to the database with their windows username.
When that user (or other user) tries to login they get the error ODBC--connection to 'systemDSN' failed.
I have opened the ACCDB file and opened the Linked Table Manager. It shows that all dbos are pointing the the same DSN name I entered on the computer.
Is there something very simple I am missing? Any ideas?
I have checked the Application and Security logs on the workstation and the SQL server and didn't see anything for the login attempts.
Update 1:
If we add the system dsn in the 32-bit version of ODBC we get a different error. The error we get is Invalid use of Null but each field has something in it.
If we change the system dsn in the 64-bit version of ODBC to a user dsn we also get the Invalid use of Null error.
It turns out the that person who designed the database used three layers of logins.
There is the Windows integrated login which is mapped to a SQL login. After that there is another login which is found in one of the tables in SQL.