Connection Problems with 10.2 to SQL 2008r2

Question

Original User: Buddhatown

I am experiencing a strange connection issue going from 10.2 (and 10.2.1) clients (ArcCatalog) to SQL 2008r2. Windows authenticated users can make that initial connection to retrieve the database list from the server no problem.  However, existing SQL based logins cannot - 'BAD USER login' error every time - no db list gets returned.  Tried all the way up to SA - still nothing.  However, and this is where it gets even stranger - NEW logins can make this connection.  For example, if I were to create a brand new, fresh user called 'TestUser' on the SQL box, I am able to make that initial connection - but nothing with an existing user.  Anyone else seen this?

2014-02-13_11-33-25.jpg

new  1.txt.zip

Anonymous User · Answer

Original User: Buddhatown

Sorry, one more thing...

Creating a connection from the same machine via ODBC to the SQL box works with the SQL native driver:
Microsoft SQL Server Native Client Version 10.50.1600

Running connectivity tests...

Attempting connection
Connection established
Verifying option settings
Disconnecting from server

TESTS COMPLETED SUCCESSFULLY!

and the standard ODBC driver:

Microsoft SQL Server ODBC Driver Version 06.01.7601

Running connectivity tests...

Attempting connection
Connection established
Verifying option settings
Disconnecting from server

TESTS COMPLETED SUCCESSFULLY!

EDIT:  After attempting again, I checked the local machines direct connect logs, and get this:

Failed to connect. Denali error: 18456, [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user 'sa'.
2008 error: 18456, [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'sa'.
2005 error: 0, IM002:[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
CAN'T OPEN INSTANCE: sde:sqlserver.
Spatial Engine Connection Failed (-9).
Cannot Get Access to Instance sde:sqlserver

Tried all variations of instance... machine name, ip, sde:sqlserver:machinename   nothing works

JasonHarris2 · Answer

SQL reports the connection attempt as a failed login:

Date  2/13/2014 10:32:37 PM
Log  SQL Server (Current - 2/13/2014 10:29:00 PM)

Source  Logon

Message
Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: 10.10.205.xyz]

JasonHarris2 · Answer

Is this a restored database?

If yes, then you need to resynch the old logins (However that still doesn't explain why "sa" is unable to connect)
HowTo:  Re-synch SQL Server logins or users after restoring a database from backup
http://support.esri.com/es/knowledgebase/techarticles/detail/29672

Thanks for the suggestion...All users have already been synched up.  However, I cant even get that far - no database list is even returned.  I should mention that the same user can connect with no problem via a 10.1 client.  Very strange.