locked
collation did not match the database's collation during connection reset RRS feed

  • Question

  • Hi Team,

    An user unable to access the DB where as he has Read/Write permission to db.

    SQL errorlog throwing below message

    LogDate       ProcessInfo   Text

    2017-03-22 18:08:57.100    Logon  Error: 18456, Severity: 14, State: 50.

    2017-03-22 18:08:57.100    Logon  Login failed for user 'XXXXXX'. Reason: Current collation did not match the database's collation during connection reset. [CLIENT: XXXXXXXX]

    2017-03-22 18:08:57.100    spid126       Error: 18056, Severity: 20, State: 50.

    2017-03-22 18:08:57.100    spid126       The client was unable to reuse a session with SPID 126, which had been reset for connection pooling. The failure ID is 50. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.

    Please suggest what needs to check here.

    Wednesday, March 22, 2017 10:34 PM

All replies

  • Hi Chinmay,

     

    The error code 'Error: 18456, Severity: 14, State: 50' means the default collation for the login is incompatible with the collation of their default database.

     

    In your scenario, I think this problem is caused by that the client connects master upon reconnection instead of the default database of login after previous disconnection. You can try to recreate this login.

     

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, March 23, 2017 3:24 AM
  • Interesting error message. :-) Never seen it before. Does this happen repeatedly, or was it just once?

    Thursday, March 23, 2017 10:26 PM
  • Indeed .This is the first time I am receiving this error. After granting DBO permission to a newly created login id this error message found in SQL log. And user also unable to access the db.

    Friday, March 24, 2017 5:45 PM
  • What is the collation of the server? Of the database? And what is the default language for the user? (You see this in sys.server_principals).

    Friday, March 24, 2017 9:58 PM
  • Collation of server :- SQL_Latin1_General_CP1_CI_AS

    Collation of DB :-      SQL_Latin1_General_CP1_CI_AS

    Default language for user :- English

    Thursday, March 30, 2017 1:43 AM
  • Hi Teige,

    I have recreated the login but no luck.Still getting same error message.

    Thursday, March 30, 2017 1:45 AM
  • And the user's default database is master? What is the collation of the default database if different?

    I will have to confess that I have very little clue. The whole concept of a "current collation" is unknown to me - and I sort of specialises in collations.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, March 30, 2017 9:44 PM
  • 2017-03-22 18:08:57.100    Logon  Login failed for user 'XXXXXX'. Reason: Current collation did not match the database's collation during connection reset. [CLIENT: XXXXXXXX]

    2017-03-22 18:08:57.100    spid126       Error: 18056, Severity: 20, State: 50.

    2017-03-22 18:08:57.100    spid126       The client was unable to reuse a session with SPID 126, which had been reset for connection pooling. The failure ID is 50. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.

    I haven't seen this error before. It might be related to the interaction of the client API and SQL Server. Do you know what API and driver the client is using (ODBC, OLE DB, SqlClient, et. al.)?


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Friday, March 31, 2017 12:42 PM
  • Dan gave me some ideas. What are the regional settings of the client?

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, March 31, 2017 1:02 PM
  • Hi Erland, Can you help me how to check the regional settings ? Is it like collation setting in SQL server that needs to be checked in client machine ?
    Monday, April 3, 2017 9:01 AM
  • Hi Dan, User trying to access DB through SSMS which is installed in laptop.
    Monday, April 3, 2017 9:03 AM
  • What version of SSMS?

    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Monday, April 3, 2017 10:56 AM
  • Hi Erland, Can you help me how to check the regional settings ? Is it like collation setting in SQL server that needs to be checked in client machine ?

    It's in the Control Panel. The exact label depends on the Windows version, but it is always something with Region - provided the OS language is English, that is. The icon is a globe with a clock in the lower left.

    Regional settings in Windows controls a number of things: date formats, number formats, some standard message boxes etc. It also affects sorting in Windows. But I am not sure that it affects the code page. So let's look into more places. In the same applet, there is a tab "Administrative" which tends to have two parts and the lower is for "system locale" or "non-Unicode" programs.

    There is one more place to check. What are the values of these two registry keys:
    HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Control\Nls\CodePage\ACP
    HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Control\Nls\CodePage\OEMCP

    Monday, April 3, 2017 8:02 PM