We have a TM1 server that accounting uses for their numerology magic... anyway, it connects to our SQL server via ODBC. We have six connections - we use Microsoft Great Plains, and each division of our company has it's own database.
Anyway, all six of these were setup using SQL authentication, and with the "sa" account.
Five of them work flawlessly; the sixth fails to connect, and in the SQL log we see the following message:
Login failed for user ''. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only.
Error: 18456, Severity: 14, State: 58.
I am puzzled by this... not sure first of all why it's saying "Server is configured for Windows authentication only" because obviously that is not the case; the other five ODBC entries are pointing at the same server using the same account. Also, the fact that it's passing a blank username instead of "sa" is strange.
I reentered the password multiple times; even tried deleting and recreating the ODBC entry. Same results. Finally, I was able to get it working by using Windows authentication instead of SQL authentication.
So basically I am wondering if anyone has any ideas as to why it would be different for this connection and not the others. All six were previously pointing to a SQL 2005 instance, all with SQL authentication, and were working just fine. Now they are pointing to a new server running SQL 2012.