My domain account has access to our MS-SQL server where the database resides, so I never experienced this issue.
When I gave the Access .mdb file to one of our regular users, he was unable to use the file without logging in. I created an account on the database before setting the Access file up and entered the username and password I setup, but it hasn't saved it. Instead it attempts to use their domain account as logged into their Workstation.
Is it possible to save this? Several users will be using the file so I'd rather not give them SQL access rights.