Sql server 2005 login from a non-domain user to domain sql
Posted on 2009-07-01
I have a frustrating problem and have scoured the web for solutions. All solutions i have done to no avail.
Here is the scenario:
I have SQL 2005 running on a 2003 R2 server on a domain.
I have several domain users using an Access 2007 front end to the server with no problem. The users have logins associated with 3 databases on the server using windows authentication.
The server is set to mixed mode authentication.
I have set up a non-domain login on the server and can set up file dsn's to all three databases that test successful on a non-domain user machine. They use the non-domain user i set on the server. These have the same dsn names as those on the domain machines.
When i try to run the same Access app on the non-domain machine i get:
"SQL Server Error: 18452"
"[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user ''. The user is not associated with a trusted SQL Server connection"
I then cancel all the errors to leave the Access 2007 app with no data.
If i try to open one of the tables i get the same error which i click OK on.
I then get the "SQL Server Login" window with "Use Trusted Connection" checked. I can uncheck that and enter the non-domain user username and password and the connection works.
Can anyone help with why the app wont connect first time please