Strange behavior with ODBC

Question

Post reply

Strange behavior with ODBC

cphite

SSCarpal Tunnel

Points: 4272
More actions
June 11, 2013 at 3:54 pm

#278881

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.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47068 More actions · Answer 1

Taking a punt - have you got "Persist Security Info=False" or no entry for "Persist Security Info" at all?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);