topic Re: Connection Problems with 10.2 to SQL 2008r2 in Data Management Questions

Connection Problems with 10.2 to SQL 2008r2

Anonymous User — Thu, 13 Feb 2014 19:31:24 GMT

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?

Re: Connection Problems with 10.2 to SQL 2008r2

Anonymous User — Thu, 13 Feb 2014 20:02:49 GMT

Original User: asrujit

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

Re: Connection Problems with 10.2 to SQL 2008r2

JasonHarris2 — Fri, 14 Feb 2014 01:22:20 GMT

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.

Re: Connection Problems with 10.2 to SQL 2008r2

JasonHarris2 — Fri, 14 Feb 2014 01:35:19 GMT

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.xx]

Re: Connection Problems with 10.2 to SQL 2008r2

Anonymous User — Fri, 14 Feb 2014 01:39:49 GMT

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