Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. Error message when you log on to an instance of Microsoft SQL Server 2005 from SQL Server Management Studio: "Timeout expired. This timeout period elapsed prior to completion of the operation or the server is not responding"

Error message when you log on to an instance of Microsoft SQL Server 2005 from SQL Server Management Studio: "Timeout expired. This timeout period elapsed prior to completion of the operation or the server is not responding"

Symptoms

When you log on to an instance of Microsoft SQL Server 2005 from SQL Server Management Studio, you receive the following error message:
Cannot connect to ComputerName/InstanceName

Timeout expired. This timeout period elapsed prior to completion of the operation or the server is not responding. (Microsoft SQL Server)
If you log on to the instance of SQL Server 2005 by using the Sqlcmd utility (Sqlcmd.exe) at a command prompt, you receive the following error message:
HResult 0x102, Level 16, State 1
ProtocolName Provider: Timeout error [258].
Sqlcmd: Error: Microsoft SQL Native Client : Unable to complete login process due to delay in login response.
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
Note ProtocolName represents the protocol that the SQL Server database engine currently uses to connect to the instance of SQL Server 2005.

Additionally, CPU utilization is 100 percent during the logon process.

This problem occurs if the following conditions are true:
  • The SQL Server login that you use to log on to the instance of SQL Server 2005 is not a member of the sysadmin fixed server role.
  • Lots of SQL Server logins exist in the instance of SQL Server 2005.

↑ Back to the top

Cause

This problem occurs because the permission cache entries for the Server object are initialized during the connection process. During this process, the SQL Server login is not a member of the sysadmin fixed server role.

You experience 100 percent CPU utilization occurs when the permission cache for the Server object is initialized.

Note Other problems may also cause the error message that is mentioned in the "Symptoms" section.

↑ Back to the top

Workaround

To work around this problem, use one of the following methods.

Method 1

Reduce the number of SQL Server logins that are in the instance of SQL Server 2005.

If many users must have access to the instance of SQL Server 2005 from the Web, create a user table in the instance to manage these users in the instance. You do not have to create SQL Server logins for each user in the instance.

You can also configure the SQL Server logins to use Windows authentication to log on to the instance. To do this, follow these steps:
  1. Create a Windows group.
  2. Create a SQL Server login that is mapped to the Windows group.
  3. Add the users to the Windows group.

Method 2

Populate the permission cache entries before you log on to the instance. To do this, log on to the instance by using a SQL Server login that is not a member of the sysadmin fixed server role. When the permission cache entries are populated, you experience the problem that is described in the "Symptoms" section. However, if you log on the instance again, the logon process will be fast.

↑ Back to the top

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

↑ Back to the top

More Information

When you use a member of the sysadmin fixed server role to log on to the instance, the permission cache entries for the Server object are not initialized. However, you must be careful when you grant users the sysadmin fixed server role for security reasons.

For example, you have two SQL Server logins. One SQL Server logins is LoginA. The other SQL Server login is LoginB. Neither LoginA nor LoginB is a member of the sysadmin fixed server role. When you use LoginA to log on to the instance, the permission cache entries for the Server object are initialized. If many SQL Server logins exist in the instance of SQL Server 2005, the initialization process takes a long time to complete, and CPU utilization is 100 percent. After the logon process for LoginA is completed, any later logon processes will be fast. For example, if you try to use LoginB to log on to the instance of SQL Server 2005, the logon process will be fast even though you are using LoginB to log on to the instance for the first time.

When you log on to an instance of SQL Server 2005, the following behavior may occur:
  • The Server object is invalidated.
  • The permission cache entries for the Server object must be initialized.
This behavior occurs after you perform one of the following operations:
  • You restart the SQL Server service.
  • You run one the following statements in the instance of SQL Server 2005:
    • CREATE LOGIN
    • DROP LOGIN
    • DBCC FREESYSTEM CACHE ('ALL')
    • DROP SCHEMA
  • You run one of the following statements in the Master database in the instance:
    • CREATE USER
    • DROP USER
    • DROP CERTIFICATE
    • DROP ASYMETRIC KEY
    • DROP SYMETRIC KEY
    • DROP APPLICATION ROLE

↑ Back to the top

Keywords: kbsql2005error, kbexpertiseadvanced, kbtshoot, kbprb, kb

↑ Back to the top

Article Info
Article ID : 934751
Revision : 3
Created on : 4/20/2018
Published on : 4/20/2018
Exists online : False
Views : 88