Monday, August 26, 2013

Login failed for user 'Admin' with SQL 18456 State 5

A client receives error when running MS Access application and querying tables linked via ODBC. The error is:
Connection failed:
SQLState: '01000'
SQL Server Error: 53
SQLState: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied 

What shows up in the SQL log is:
Login failed for user 'Admin'. [CLIENT: xx.xx.xx.xxx]
Error: 18456, Severity: 14, State: 5.

Possible solution:
By default, Access Jet engine tries to use its own username to connect to SQL Server BEFORE the predefined SQL connection. "Admin" is usually defined as the default Access username. There is a registry entry that switches that default behavior off:

Registry value: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\ODBC\TryJetAuth
Change from 1 to 0.