Access Link table behavior different with NT vs SQL authentication

[Aaron]

I have inherited an application that consists of an MS-SQL 7 back end and Access 97 front end with linked ODBC tables. I want to secure the application using NT accounts instead of generic MS-SQL accounts. I have tested this by creating the MS-SQL accounts, granting their permissions, and then changing ODBC on the client to use NT authentication. The ODBC test succeeds

When I run the front end application it prompts me for authentication even though it should just use Windows security. So I check the box to use trusted authentication and it returns some rows of data. Then as it returns more rows of data it occassionally prompts again to authenticate. Check the 'trusted' box and it goes back to retrieving rows. Rinse, repeat, rinse repeat.

This is obviously not acceptible to have to keep checking this box. I would think since I set in ODBC to just use NT that it would remember this setting and keep going. I've searched around for answers but haven't uncovered anything so if you have any ideas please share. With hardcoding the SQL account and password into the ODBC configuration there is no problem. I don't want to keep this mode however because our security approach is to get rid of these generic MS-SQL accounts

Versions
Client ODBC 2000.81.9042.0
MS-SQL 7.00.106
Access 97 w/ Office 97 Service Pack

 

[TC]

I feel you will have more luck with this question in a newsgroup devoted to
SQL*Server and/or ODBC. It is nothing to do with Access/Jet security, as far
as I can see. IOW, no-one here will necessarily know the answer.

Cheers,
TC

I have inherited an application that consists of an MS-SQL 7 back end and

Access 97 front end with linked ODBC tables. I want to secure the
application using NT accounts instead of generic MS-SQL accounts. I have
tested this by creating the MS-SQL accounts, granting their permissions, and
then changing ODBC on the client to use NT authentication. The ODBC test
succeeds.

When I run the front end application it prompts me for authentication even

though it should just use Windows security. So I check the box to use
trusted authentication and it returns some rows of data. Then as it returns
more rows of data it occassionally prompts again to authenticate. Check the
'trusted' box and it goes back to retrieving rows. Rinse, repeat, rinse
repeat.

This is obviously not acceptible to have to keep checking this box. I

would think since I set in ODBC to just use NT that it would remember this
setting and keep going. I've searched around for answers but haven't
uncovered anything so if you have any ideas please share. With hardcoding
the SQL account and password into the ODBC configuration there is no
problem. I don't want to keep this mode however because our security
approach is to get rid of these generic MS-SQL accounts.