Look at the SQL Log for additional information on the error. It appears to be a failed login.
Here is an msdn article about it.
You might try running a trace on the SQL box while you attempt to create the ODBC connection. Does the person creating the ODBC connection have rights to the SQL server?