SQL Server Performance

SQL Server Connectivity Issue.

Discussion in 'ALL SQL SERVER QUESTIONS' started by Tapas Kumar, Jul 3, 2013.

  1. Tapas Kumar New Member

    I am able to connect the server but not to the SSMS. getting the below error:

    "
    The client was unable to establish a connection because of an error during connection initialization process before login. Possible causes include the following: the client tried to connect to an unsupported version of SQL Server; the server was too busy to accept new connections; or there was a resource limitation (insufficient memory or maximum allowed connections) on the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) (Microsoft SQL Server, Error: 10054)"

    To fix it :
    I have done some changes in system registry..

    from the path... \System\CurrentControlSet\Control\Lsa\Kerberos\Parameters
    -- Added the following registry value:
    Name: MaxTokenSize
    Data type: REG_DWORD
    Radix: Decimal
    Value: 48000
    --Rebooted server

    and it resolves the issue for me..

    BUT it is happening weekly basis..
    and every time server restart resolves the issue.

    CAN ANYBODY HELP TO FIX THE ISSUE PERMANENTLY..


    Details :
    SQL Server 2005 -- SP4 -- Standard Edition (64-bit)
    MS Windows Server 2003 R2 -- SP2-- Standard x64 Edition
    RAM: 16 GB

    Thanks in advance...
  2. davidfarr Member

    I am not understanding quite what you mean by that.
    SSMS is "SQL Server Management Studio". Correct ?
    SSMS is an executable client application. One does not therefore "connect" to SSMS, you either execute SSMS or you don't. Once executed and open, SSMS then connects to a server.
    So, perhaps you can clarify what you mean by "cannot connect to SSMS".
    
    Are you changing the MaxTokenSize on the server or on the client workstation ? Are you rebooting the server to correct the problem or rebooting the client workstation ?
    MaxTokenSize is very seldom a problem unless you work in a very large company with a large Active Directory.
    How large is your company and how many people in your company/office are connecting to this SQL server ?
    Does the problem affect all users, all at the same time (i.e; suddenly nobody can connect), or specific users at random moments, or random users at random moments ?

    If you find yourself needing to change the registry value back to 48000 each week, then there is obviously some other process elsewhere that is changing this registry value, and this is then what you need to investigate by observing the exact day and time that connection problems start happening, and what the new registry value is (if it's no longer 48000). It might be a domain controller that is enforcing an active directory policy by changing the registry value to something other than 48000.
    Also note that 48000 is not the limit. You could use a value as high as 65535, although this is very seldom necessary.
    
    Also note that a reboot of the server affects and resets many different things. The registry MaxTokenSize might actually not have anything to do with the problem. A server reboot on it's own might be 'solving' the problem by dropping other user's connections.
    Check the result of this query;
    exec sp_configure 'user connections' --(run_value should be either 0 or a fairly high number)
    ...and observe Activity Monitor see how many connections are concurrently being made to the server. It could just be a simple case of more connections than the server allows.

    Note that the MaxTokenSize registry value can only affect logins that use Windows Authentication. If your Windows login to SQL server is failing, then attempt a connection using a SQL server login. If that works (or doesn't work), it will give you further insight into the problem either way.

Share This Page